Formule Excel pour decomposer n° phone et format date

Le 02/12/2011 à 12:13

Bonjour le forum

J'ai deux soucis :

le 1er concerne une formule excel:

En effet, je saisi des n° de téléphone dans une même cellule.

Par exemple, dans la cellule B28 je peux saisir un numero du genre 70000000/50000000/72000000 (il s'git de n° de phone comportant chacun 8 chiffres).

Je cherche une formule qui me permettra :

  • lorsque le 1er chiffre de la cellule B28 commence par 7, que les 8 1er chiffre en commencant par le 7 soit extrait (cette formule est a entrer dans la cellule C29),
  • lorsque le 10è chiffre de la cellule B28 commence par 5, que les 8 1er chiffre en commencant par le 5 soit extrait (cette formule est a entrer dans la cellule C30),
  • lorsque le 19è chiffre de la cellule B28 commence par 7, que les 8 1er chiffre en commencant par le 7 soit extrait (cette formule est a entrer dans la cellule C31).
Voici le resultat attendu si je venais à trouver la formule : C29 = 70000000, C30 = 50000000 et en C31 = 72000000.

Le 2è point concerne les formats de date:

Il m'arrive de constater que certains utilisateurs d'un fichier que j'ai conçu saisissent par inadvertance les dates de facon brute (au lieu de saisir par exemple 02/12/2011, certains saisissent 02122011). est-il possible de faire en sorte que celui qui viendrai à saisir 02122011 que le format 02/12/2011 s'applique à la validation (c'est à dire en faisant ENTREE).

D'avance merci.

zombeTrolleur

Messages: 53Inscription: Lun Juil 11, 2011 5:52 am

Le 02/12/2011 à 13:37

Bonjour,

Pour ton 1er soucis :

en C29:

=SIERREUR(GAUCHE($B$28;TROUVE("/";$B$28;1)-1);"")

en C30 :

=SIERREUR(STXT($B$28;TROUVE("/";$B$28;1)+1;TROUVE("/";$B$28;TROUVE("/";$B$28;1)+1)-TROUVE("/";$B$28;1)-1);"")

en C31 :

=SIERREUR(DROITE($B$28;NBCAR($B$28)-CHERCHE("§";SUBSTITUE($B$28;"/";"§";NBCAR($B$28)-NBCAR(SUBSTITUE($B$28;"/";"")))));"")

Pour le 2ème, je ne suis pas un pro des dates et des formats, donc je laisse le soin à quelqu'un qui s'y connait de t'aider.

Cordialement,

Le 02/12/2011 à 14:12

Bonjour,

zombe a écrit :

Bonjour le forum

Le 2è point concerne les formats de date:

Il m'arrive de constater que certains utilisateurs d'un fichier que j'ai conçu saisissent par inadvertance les dates de facon brute (au lieu de saisir par exemple 02/12/2011, certains saisissent 02122011). est-il possible de faire en sorte que celui qui viendrai à saisir 02122011 que le format 02/12/2011 s'applique à la validation (c'est à dire en faisant ENTREE).

J'utiliserai la "validation de données" pour obliger l'utilisateur à entrer les chiffres au format date.

Dans l'onglet Données, outil Validation de données, tu choisis "date " dans le champ autoriser de l'onglet options, puis tu remplis l'onglet alerte d'erreur avec "veuillez entrer les données au format date : jj/mm/aaaa".

Le 02/12/2011 à 14:13

Bonjour

Bonjour Psycho

Comme je l'ai déjà indiqué pas sur d'avoir compris

Tu indiques la position des chiffres

En C29

=SI(STXT(B28;1;1)="7";GAUCHE(B28;8);"")

en C30

=SI(STXT(B28;10;1)="5";STXT(B28;10;8);"")

En C31

=SI(STXT(B28;19;1)="7";DROITE(B28;8);"")

Pour la date avec une macro évènementielle

Code à copier/coller dans la code la feuille

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Count > 1 Then Exit Sub
  If IsNumeric(Target) = True Then
    If (Len(Target) = 7 Or Len(Target) = 8) Then
      If IsDate(Left(Target, 2) & "/" & Mid(Target, 3, 2) & "/" & Right(Target, 2)) Then
        With Target
          .Value = Left(Target, 2) & "/" & Mid(Target, 3, 2) & "/" & Right(Target, 2)
          .NumberFormat = "m/d/yyyy"
        End With
      End If
    End If
  End If
End Sub
Le 02/12/2011 à 14:25

Salut zirak

Tè formidable. c'est ce que je cherchais.

Infiniment merci et que DIEU te bénisse.

Le 04/12/2011 à 11:17

Merci à Psycho et à Banzaï pour leur proposition.

J'ai essayé et ca marche bien.

Concernant le code macro évènementiel de Banzaï sur le format date, j'ai un peu de souci.

En effet, j'ai déjà une macro sur la feuille ou ta macro doit être collée.

Comment faire dans ce cas? Y'a t-il possibilité que fasse de sorte que la macro puisse être collée sur une autre feuille ou module tout en obtenant les même résultats?

Je compte sur toi et tous ceux qui pourront trouver une solution à mon soucis.

Le 04/12/2011 à 11:30

Bonjour

Si ton autre macro est aussi la même procédure évènementielle Worksheet_Change il faut essayer de les combiner

Le plus simple joins un fichier avec ta macro afin de voir ce que l'on peut faire

Le 04/12/2011 à 12:34

Voici le code de la macro.

Dans mon fichier, les cellules concernées par le format date sont :

B18, B22, B23, B38 et B47. Je n'arrive pas à joindre le fichier.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo fin

If Range("B4").Value = "CH PARTICULIER" Then

If Target.Address = "$B$5" And Target.Value <> "" Then

Range("B7").Select

ElseIf Target.Address = "$B$30" And Target.Value <> "" Then

Range("B32").Select

Call Macro10

'Range("E3").Select

Range("E3").Select

Else: GoTo fin

End If

ElseIf Target.Address = "$B$46" And Target.Value <> "" Then

Call Macro10

GoTo fin

Else: GoTo fin

End If

fin:

Exit Sub

End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo fin

If Range("B4").Value = "CH PARTICULIER" Then

If Target.Address = "$B$5" And Target.Value <> "" Then

Range("B7").Select

ElseIf Target.Address = "$B$30" And Target.Value <> "" Then

Range("B32").Select

Call Macro10

'Range("E3").Select

Range("E3").Select

Else: GoTo fin

End If

ElseIf Target.Address = "$B$46" And Target.Value <> "" Then

Call Macro10

GoTo fin

Else: GoTo fin

End If

fin:

Exit Sub

End Sub

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo fin

If Range("B4").Value = "CH PARTICULIER" Then

If Target.Address = "$B$5" And Target.Value <> "" Then

Range("B7").Select

ElseIf Target.Address = "$B$30" And Target.Value <> "" Then

Range("B32").Select

Call Macro10

'Range("E3").Select

Range("E3").Select

Else: GoTo fin

End If

ElseIf Target.Address = "$B$46" And Target.Value <> "" Then

Call Macro10

GoTo fin

Else: GoTo fin

End If

fin:

Exit Sub

End Sub

Le 04/12/2011 à 14:17

Bonjour

Un essai mais sans fichier de test, je ne sais pas si cela fonctionne

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldNumberFormat As String
 On Error GoTo Fin

  If Target.Count > 1 Then Exit Sub
  If Not Intersect(Range("B18,B22,B23,B38,B47"), Target) Is Nothing Then
    OldNumberFormat = Target.NumberFormat     ' Récupère l'ancien format de cellule
    Target.NumberFormat = "@"                 ' Force en standart
    If IsNumeric(Target) = True Then          ' Si c'est un nombre
      If (Len(Target) = 7 Or Len(Target) = 8) Then            ' Longueur 7 ou 8 caractères
        If IsDate(Left(Target, Len(Target) - 6) & "/" & Mid(Target, Len(Target) - 5, 2) & "/" & Right(Target, 4)) Then
          ' Vérifie si une fois transformé on obtient une date
          Application.EnableEvents = False
          With Target
            .Value = Left(Target, Len(Target) - 6) & "/" & Mid(Target, Len(Target) - 5, 2) & "/" & Right(Target, 4)
            .NumberFormat = "m/d/yyyy"
          End With
          Application.EnableEvents = True
        Else
          ' On n'obtient pas de date donc on remet le format de cellule d'origine
          Target.NumberFormat = OldNumberFormat
        End If
      Else
        ' La longueur n'est ni 7 ni 8 donc on remet le format de cellule d'origine
        Target.NumberFormat = OldNumberFormat
      End If
    Else
      ' Ce n'est pas un nombre donc on remet le format de cellule d'origine
      Target.NumberFormat = OldNumberFormat
    End If
  Else
    If Target.Value <> "" And Range("B4").Value = "CH PARTICULIER" Then
      Select Case Target.Address
        Case "$B$5"
          Range("B7").Select
        Case "$B$30"
          Range("B32").Select
          Call Macro10
          Range("E3").Select
        Case "$B$46"
          Call Macro10
      End Select
    End If
  End If
Fin:
End Sub
Le 04/12/2011 à 15:56

Merci pour le code proposé.

Je rencontre toujours des soucis à la saisi de certaines dates.

Ex : en B18 j'ai saisi 01112011

en B22 j'ai saisi 01122011

en B23 j'ai saisi 15122011

en B22 j'ai saisi 25082010

Tu pourras voire les resultats que ca donne dans les cellules correspondantes du fichier joint.

Peux tu revoir ton code qui doit jouer sur le format date sans modifier mon code si possible?

Merci également aux bonnes volontés.

N.B:tu vien également de me proposer ce code sur un autre sujet.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Range("B9,B12"), Target) Is Nothing And Target.Count = 1 Then

Application.EnableEvents = False

ValSaisie = Target

Application.Undo

p = InStr(Target, ValSaisie)

If p > 0 Then

Target = Left(Target, p - 1) & Mid(Target, p + Len(ValSaisie) + 1)

If Right(Target, 1) = ":" Then

Target = Left(Target, Len(Target) - 1)

End If

Else

If Target = "" Then

Target = ValSaisie

Else

Target = Target & ":" & ValSaisie

End If

End If

Application.EnableEvents = True

End If

End Sub

Peut-tu adapter ce code avec le code du fichier joint afin que ce soit un tout.

J'ai pas su dès le depart qu'il fallait poser le problème en même c'est au fur et à mesure que les idées viennent.

Merci pour ta compréhension et que Dieu te bénisse

Le 04/12/2011 à 18:34

Bonjour

A vérifier

Ps: Quand tu mets du code dans un post utilises les balises Code

balise code
15zombe-formatage-dates-v001.zip (34.77 Ko)
Le 04/12/2011 à 22:20

Ok Banzaï j'ai vérifié mais les résultat attendus ne sont pas atteint.

Je n'arrive pas à faire fonctionner tes macros.

Le code que tu réalisé devrait avoir 3 effets:

  • 1) ca devrait pouvoir transformer le format des cellules devant contenir des dates,
  • 2) aussi, j'ai 2 cellules contenant des liste déroulantes, le dernier code que tu donné devrait me permettre de choisir plusieurs élements de la liste deroulante,
  • 3) il y'a également ma macro initiale qui ne marche pas également.
Au regard des objectifs attendus, peut-tu revoir ou ca cloche afin d'apporter des solutions?

Voici à nouveau le fichier avec les essai de date saisie et aussi les listes deroulantes (b11 et b30).

Le 04/12/2011 à 23:53

Bonsoir

zombe a écrit :

Je n'arrive pas à faire fonctionner tes macros.

Erreur de syntaxe (pourtant corrigé dans le fichier précédent) : End If en trop

zombe a écrit :

ca devrait pouvoir transformer le format des cellules devant contenir des dates,

Valable pour les cellules "B18,B22,B23,B38,B47"

zombe a écrit :

aussi, j'ai 2 cellules contenant des liste déroulantes, le dernier code que tu donné devrait me permettre de choisir plusieurs élements de la liste deroulant

cela fonctionnait pour "B9,B12" et dans cette version modifiée pour B11,B30

zombe a écrit :

il y'a également ma macro initiale qui ne marche pas également.

Je ne l'ai pas modifiée (A part (comme indiqué) les deux appels à la macro1 (inconnue) : Une remarque devant la ligne d'appel)

16zombe-formatage-dates-v002.xlsm (78.07 Ko)
Le 05/12/2011 à 23:14

Salut Banzaï

C'est trop super ce que ta pu réaliser pour moi.

Ca marche à merveille. Sui content de toi.

Si tu me mermet, je te soumet le lien ci-dessous afin que tu voie comment tu peut m'aider.

https://forum.excel-pratique.com/post153535.html?hilit=publipostage#p153535

Le 06/12/2011 à 00:55

Bonjour

Désolé mais pour OutLook je passe la main

Pas ma tasse de thé

Remets un message pour faire remonter le sujet

Quelqu'un va bien t'aider

Le 06/12/2011 à 09:14

Merci pour la sincérité Banzaï.

Ce que vous avez deja fait est très considérable.

Je vais suivre vos conseils.

Bye bye

Rechercher des sujets similaires à "formule decomposer phone format date"