J'ai un document Excel avec des cellules qui sont des adresses, mais qui n'ont pas toujours la même syntaxe.
Je souhaiterai isoler les codes postaux (5 chiffres) sur une autre cellule.
Exemples:
4 rue Charles Le Goffic Lorient 56100 56 Morbihan France
2 Avenue du 8 Mai 1945, résidence le Saint Louis,Bat B, Appart B15 Nogent-sur-Oise 60180 60 Oise France
46 bis route de Pibrac Cornebarrieu 31700 31 Haute-Garonne
Cf. en pièce jointe, avec une formule matricielle à valider avec Ctrl+maj+entrer.
Attention, cela ne fonctionne que s'il ni y a pas d'espace dans le code postal (il faut écrire 56100 et pas 56 100), et pas d'autres séries de 5 chiffres ou plus dans l'adresse.
Function CodePostal(chaine)
p = 1
CodePostal = ""
Do While p <= Len(chaine) - 4 And CodePostal = ""
If Mid(chaine, p, 5) Like "#####" Then CodePostal = Mid(chaine, p, 5) Else p = p + 1
Loop
End Function
si adresse dans cellule a2, dans la cellule du code postal
Enrichi (BBcode):
=CodePostal(A2)
sur ce site, je vous conseille aussi de consulter les solutions de patricktoulon qui a réalisé un excellent travail sur les extractions d'une adresse.
cordialement
galougalou
Une autre formule (si je m'abuse de Tibo (un grand formuliste de l'XLD d'antan) =STXT(A1;EQUIV(1;(ESTNUM(STXT(SUBSTITUE(A1;" ";"µ");LIGNE(INDIRECT("1:"&NBCAR(A1)));5)*1))*1;0);5)
Toujours à valider matriciellement
PS: je n'ai pas ouvert les PJ du fil, donc désolé si cette formule a déjà été proposée.
EDITION: Ah, bah, en fait c'est la même formule que chris (pour le début en tout cas)
[Confinement -jour1 - m2]
Une fonction VBA personnalisée avec les expressions régulières (regex)
VB:
Function xCP(R As Range) As String
Dim result$, allMatches As Object
With CreateObject("vbscript.regexp")
.Pattern = "\d{5}": Set allMatches = .Execute(R.Value2)
End With
If allMatches.Count > 0 Then
result = allMatches.Item(0)
End If
xCP = result
End Function
Usage:
=xCP(A1)
PS: Test OK avec les exemples du 1er message
[/Confinement - jour 1 - m2]
Une autre formule (si je m'abuse de Tibo (un grand formuliste de l'XLD d'antan) =STXT(A1;EQUIV(1;(ESTNUM(STXT(SUBSTITUE(A1;" ";"µ");LIGNE(INDIRECT("1:"&NBCAR(A1)));5)*1))*1;0);5)
Toujours à valider matriciellement
Bien vu le EQUIV(1;1er test;0) pour repérer la position du groupe de 5 caractères convenant, c;à-d. numériques, en ayant remplacer les " " par des µ, pour que 4 chiffres précédés ou suivis d'un espace ne soient pas compris par Excel comme un nombre.
C'est plus court que mon double test MAX((1er test)*ESTERREUR(TROUVE(" ";STXT(....))))
@ plus
PS : Et non, je viens de voir que la formule proposée par Staple/Tibo donne 1945, (y compris la virgule) au lieu de 60180 sur la seconde ligne, ce qui veut dire que 1945, * 1 donne 1945
Pour "éliminer" les virgules, on peut faire avec