XL 2016 35 Variantes d'écriture dans un numéro de téléphone portable à harmoniser en 1 seul

[Philippe Château]

Bonjour à toutes et tous,

J'ai récupéré un fichier d'une quarantaine de milliers de lignes, avec 35 variantes d'écriture du numéro de téléphone portable (et OUI pas de cadre pour obliger les gens à formaliser leur numéro de portable) ! et mon but serait pour le croiser avec un autre fichier de le rendre à 9 chiffres "699999999" (et à le rendre en format téléphone "06 99 99 99 99" pour qu'il soit lisible)

La fonction SUBSTITUE rend service mais pas totalement, lorsque l'on a un début qui commence par 0 - 33 - 330, on risque de supprimer les infos dans un numéro complet, d'où en colonne adjacente de la fonction GAUCHE + REMPLACER

N'y aurait-il pas plus simple ? de l'avis d'expert(e)s ?

J'ai été obligé de changer quelques numéros par A, question de confidentialité, car la cellule qui commence par + croit voir une valeur numérique à calculer si on change par la valeur 9 comme pour les autres cellules

Merci à toutes et tous

 

[Dranreb]

Bonjour.
Le plus simple c'est de l'enregistrer sous forme de nombre dans des cellules munies d'un format Spécial: Numéro de téléphone.

 

[R@chid]

Bonjour,
en B2 :

=--DROITE(SUBSTITUE(A2;" ";);9)

@ tirer vers le bas


Format spécial : Numéro de téléphone

Cordialement

 

[ERIC S]

Bonjour

A tester:

=SI(ESTNUM(A2);A2-ENT(A2/1000000000)*1000000000;CNUM(DROITE(SUBSTITUE(SUBSTITUE(A2;" ";"");".";"");9)))

 

[Philippe Château]

Bonjour à vous deux

Merci de votre super réactivité, les deux fonctionnent sauf une pour R@chid (d'autant que la formule est hyper condensée, ouahh c'est époustouflant...)

+33 (0)7.99.99.99.99 (je l'ai indiquée dans mon fichier sous la forme)

+33 (0)6.AA.AA.AA.AA

et pour vous deux la cellule

+33 0(6) 99 99 99 99

Si il n'y a pas moyen, ce n'est pas grave, une grosse partie est bien résolue, et vous en remercie.

 

[patricktoulon]

bonjour
ça fait pas mal de chose a supprimer
les plus courantes

=DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;" ";"");",";"");"-";"");"_";"");"+";"");".";"");")";"");9)

te reste plus qu'a appliquer le format que tu souhaite en ajoutant un "0" dans le format pour faire 10 chiffres

 

[Philippe Château]

Merci "patricktoulon", je vais essayer ta formule, et vous reviens demain.
Bonne soirée

 

[Philippe Château]

En fait juste rajouter un CNUM, puis faire les cellules en format téléphone
Je continue mes tests
Merci

 

[patricktoulon]

re

=SIERREUR(CNUM(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;" ";"");",";"");"-";"");"_";"");"+";"");".";"");")";"");9));"no number")

et mettre la colonne au format téléphone
les cellules qui n'en seront pas seront pointées "nonumber"

 

[Philippe Château]

Ah OUI pas mal comme condition pour cerner de suite les "martiens"

 

[job75]

Bonsoir à tous,

@patricktoulon perso au lieu de SUBSTITUE(A2;" ";"") j'écris simplement :

SUBSTITUE(A2;" ";)

A+

 

[patricktoulon]

bonsoir @job75
oui les réductions par omission d'argument ça me séduit
mais je préfère donner la version intégrale pour une meilleure compréhension
sinon oui onpeut réduire a ça

=SIERREUR(CNUM(DROITE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(SUBSTITUE(A2;" ";);",";);"-";);"_";);"+";);".";);")";);9));"no number")

 

[mapomme]

Bonsoir,

Avec une fonction personnalisée dont le code est dans module1. On renvoie #REF! si ce n'est pas un numéro de téléphone. Recopier la cellule B1 vers le bas.

Function TailleTel9(ByVal x)
Dim i&, n, c
   For i = Len(x) To 1 Step -1
      c = Mid(x, i, 1)
      If c >= "0" And c <= "9" Then n = c & n
   Next i
   TailleTel9 = CLng(Right(n, 9))
   If Len(TailleTel9) <> 9 Then TailleTel9 = CVErr(xlErrRef)
End Function

 

[Philippe Château]

Bonjour "mapomme"
Merci pour le VB, mais malheureusement je ne peux insérer de VB dans mes fichiers, dommage.
A+ et bonne journée