Exemple :
1) Extraire l'adresse :
Inscrivez le code suivant en B2:
=GAUCHE(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)-5)
Résultat :
2) Extraire le code postal
Inscrivez le code suivant en C2:
=STXT(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)-4;5)
Résultat :
3) Extraire le nom de la ville :
Inscrivez le code suivant en D2:
=STXT(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)+2;99)
Résultat :
Autre solution pour les personnes qui maitrise le langage VBA, insérer le code suivant dans un module :
EXTRAIRE LES CODES POSTAUX ET LES VILLES :
Sub Extract_Adresse_CP_VILLE()
Dim Csource As Integer, Cadd As Integer, CcodeP As Integer
Dim Cville As Integer, DebLig As Integer
Dim i As Long, e As Integer, Txt As String
Csource = 1 'colonne ou trouver la source - ici A
Cadd = 2 'Colonne où mettre l'adresse - ici B
CcodeP = 3 'Colonne où mettre le CP - ici C
Cville = 4 'Colonne où mettre la ville - ici D
DebLig = 2 'Ligne où commence le split ou ligne de départ
With Sheets("Feuil1")
For i = DebLig To .Range("A65536").End(xlUp).Row
Txt = .Cells(i, Csource)
If Len(Txt) > 6 Then
For e = 2 To Len(Txt)
If Mid(Txt, e, 1) <> " " And IsNumeric(Mid(Txt, e, 5)) Then
.Cells(i, Cadd) = Left(Txt, e - 1)
.Cells(i, CcodeP) = Mid(Txt, e, 5)
.Cells(i, Cville) = Mid(Txt, e + 6)
Exit For
End If
Next e
End If
Next i
End With
End Sub
EXTRAIRE les ADRESSE, les CODES POSTAUX et les VILLES :
Sub Extraire_ADRESSES_CODESPOSTAUX_VILLES()
Dim c As Range, t As Integer
Set c = Range("A2")
Do While c <> ""
For t = InStr(InStr(c, " "), c, " ") To Len(c)
Select Case Mid(c, t, 1)
Case "0" To "9"
Exit For
End Select
Next t
c(1, 2) = Mid(c, 1, t - 2)
c(1, 3) = Mid(c, t, 5) c(1, 3).NumberFormat = "00000"
c(1, 4) = Mid(c, t + 6)
Set c = c(2, 1)
Loop
End Sub
Un petit plus ...
EXTRAIRE une adresse MAIL dans une contenu de texte :
Insérer dans un module le code suivant correspondant à une fonction personnalisée :
Function Extraction_EMAIL(ByVal S As String) As String
Dim X As Long, AtSign As Long
Dim Locale As String, DomainPart As String
Locale = "[A-Za-z0-9.!#$%&'*/=?^_`{|}~+-]"
Domain = "[A-Za-z0-9._-]"
AtSign = InStr(S, "@")
For X = AtSign To 1 Step -1
If Not Mid(" " & S, X, 1) Like Locale Then
S = Mid(S, X)
If Left(S, 1) = "." Then S = Mid(S, 2)
Exit For
End If
Next
AtSign = InStr(S, "@")
For X = AtSign + 1 To Len(S) + 1
If Not Mid(S & " ", X, 1) Like Domain Then
S = Left(S, X - 1)
If Right(S, 1) = "." Then S = Left(S, Len(S) - 1)
Extraction_EMAIL = S
Exit For
End If
Next
End Function
Ajouter la fonction personnalisée Extraction_EMAIL dans la cellule A2 pour la valeur recherchée dans la cellule A1 =Extraction_EMAIL(A1)
A vous de jouer....
Emile @+
(*) Voire l'onglet " Mise en ordre des prénoms et noms " pour la validation maj+ctrl+entrée
Bonjour,
RépondreSupprimerTout d'abord merci pour ce tutoriel, qui m'est d'une grande utilité (j'ai une liste de plus de 15000 adresses dont il me faut extraire le code postal).
Votre formule fonctionne très bien sur la quasi totalité des adresses, mais pour certaines il y a un blocage que je ne parviens pas à comprendre.
Voici un exemple d'adresse pour lequel cela ne fonctionne pas :
"12/20 RUE MACHIN 75932 PARIS CEDEX 19"
Pouvez-vous m'aider ?
Merci d'avance.
Re-bonjour,
RépondreSupprimerPour compléter le propos et peut-être pour vous aider, a priori le problème se produit lorsque l'adresse se termine par un Cedex avec numéro (Cedex 15, Cedex 19, etc...).
Bonjour Arnaud,
RépondreSupprimerPour extraire le CEDEX XX voici la formule :
=STXT(A1;NBCAR(A1)-EQUIV(VRAI;ESTNUM(--(STXT(A1;NBCAR(A1)-LIGNE($1:$255);1)));0)-6;9)
Pour extraire la ville suivi d'un CEDEX XX
=STXT(GAUCHE(A1;CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-1)));CHERCHE("|";SUBSTITUE(A1;" ";"|";NBCAR(A1)-NBCAR(SUBSTITUE(A1;" ";""))-2))+1;99)
Le tout à valider avec Ctr + Maj + entrée
Bonjour,
RépondreSupprimerSuper tutoriel! Merci beaucoup!
Je rencontre le même problème qu'Arnaud avec les adresses comportant un cedex.
Si je reprends le 3ème exemple et que je rajoute Cedex 03 à la fin : "2 avenue de cheval blanc 75000 Paris Cedex 03"
- La formule pour extraire le nom de la rue donne le résultat suivant : "2 avenue de cheval blanc 75000 Paris Ce"
- La formule pour extraire le code postal donne le résultat suivant : "dex 0"
Les 2 formules ci-dessus fonctionnent bien pour extraire le cedex : "Cedex 03" et la ville "Paris".
Est-ce que les 2 premières formules sont modifiables pour extraire les valeurs voulues quand il y a un Cedex?
Merci d'avance
Emile le petit génie :) Merci beaucoup pour ce super Tuto :)
RépondreSupprimerBonjour, Je dois extraire la ville dans cette ligne comment puis-je faire? Zinga Zanga, BEZIERS le 17/05/2019
RépondreSupprimerbonjour j'ai tenté d'utiliser votre formule mais je n'y arrive pas..
RépondreSupprimerPourriez vous m'aider ?
ex : NEWREST RESTAURATION AFPA RIS Liv. 7h15/14 h avenue valmy 91320 RIS ORANGIS
formule : =STXT(F132;NBCAR(F132)-EQUIV(VRAI;ESTNUM(--(STXT(F132;NBCAR(F132)-LIGNE($1:$255);1)));0)-4;5)
Bonjour,
RépondreSupprimerJe tiens à vous remercier pour ce tutoriel, les formules marchent très bien.
Seulement, je souhaiterai savoir comment faire si je veux extraire que le nom de l'adresse:
Exemple pour:17 RUE DES DAMES AUGUSTINES 92200 NEUILLY SUR SEINE
Je veux avoir: DAMES AUGUSTINES
Nb: Je tiens à préciser que je suis nul en VBA
Merci d'avance pour votre aide ^^.
SUPER J'ai cherché toute la journée, j'ai essayé un tas de truc mais il n'y a que ces formules qui fonctionnent
RépondreSupprimerMERCI
Un grand merci, cela fonctionne parfaitement bien pour mon besoin, et je désespérai de devoir faire cela manuellement n'ayant pas trouvé jusqu'ici une aide aussi "souple" !
RépondreSupprimerPas de quoi.... le mérite vous revient du fait que vous avez fait la démarche de la mettre en application.
SupprimerBonjour, je n'arrive pas à faire fonctionner votre formule. Voici l'adresse :
RépondreSupprimerCiments Calcia - 1, Rue du Fief D'argent - 79600 AIRVAULT qui en cellule A4
Bonjour
SupprimerExemple en B4 => =GAUCHE(A4;NBCAR(A4)-EQUIV(VRAI;ESTNUM(--(STXT(A4;NBCAR(A4)-LIGNE($1:$255);1)));0)-5)
en C4 => =STXT(A4;NBCAR(A4)-EQUIV(VRAI;ESTNUM(--(STXT(A4;NBCAR(A4)-LIGNE($1:$255);1)));0)-4;5)
en D4 => =STXT(A4;NBCAR(A4)-EQUIV(VRAI;ESTNUM(--(STXT(A4;NBCAR(A4)-LIGNE($1:$255);1)));0)+2;99)
Bonjour et merci pour ce tuto.
RépondreSupprimerY a t-il un moyen de convertir les données extraites en texte (string ?), afin de pouvoir déplacer les cellules dans le document sans perdre les résultats ?
Je répond oui, mais je n'ai pas la structure de votre dossier donc difficile de vous répondre sur le déplacement de vos données. Vous pouvez me contacter via l'onglet CONTACT et m'envoyer votre fichier (du moins une partie ou un exemple).Cdt
SupprimerBonjour,
RépondreSupprimerJe n'arrive pas à faire fonctionner vos formules en B2
ADRESSE exemple : RESIDENCE LES GEMEAUX 131 AV DU MAL DE LATTRE DE TASSIGNY - 83140 SIX FOURS LES PLAGES
Merci
Cdlt
Bonjour
SupprimerAvez-vous valider la formule avec les touches maj+ctrl+entrée ? car de mon côté cela fonctionne.
Bonjour,
RépondreSupprimerTout d'abord merci pour ce tuto qui m'a fortement aidé.
A mon niveau, dans mes adresses, j'ai, après la ville, le PAYS.
Je souhaiterai pouvoir extraire ce pays en plus des 3 extractions que vous faites.
Exemple actuellement ça me donne : A1 = ADRESSE CP VILLE PAYS/ B1 = ADRESSE / C1 = CP / D1 = VILLE PAYS
Ce que je souhaiterai si possible : A1 = ADRESSE CP VILLE PAYS/ B1 = ADRESSE / C1 = CP / D1 = VILLE / E1 = PAYS (sachant que ce n'est pas toujours FRANCE)
Sauriez vous comment faire svp ?
Merci par avance pour votre aide.
Merci pour ton tuto. C'est géniale mais comment fait on après avoir séparé le code postale et l'adresse, pour effacer sur l'adresse originale le code et la ville
RépondreSupprimerBonjour,
RépondreSupprimerMerci beaucoup pour toutes ces formules que j'utilise avec soin !!!
J'aurais une petite demande supplémentaire. Je souhaite extraire un CP d'une adresse ayant ou pas un cedex, sachant que le resultat du CP doit se finir par 0.
Par exemple en cellule A1 : THALES LYON - 62 QUAI CHARLES DE GAULLE CS20100 69463 LYON Cedex 06
Le CP extrait doit être : 69460 (et non 69463)
Mille mercis pour votre aide.
Cordialement
Bonjour,
RépondreSupprimerMerci pour ce tuto. J'ai essayé de l'adapter pour extraire l'adresse, le numéro de téléphone et les coordonnées géographiques de la ligne ci-après, mais cela ne marche pas.
Pourriez-vous m'aider svp ?
7285NICOLAS128 Avenue Du Gal De Gaulle94170Perreux Sur Marne0143242207 48.84276
Excellent ! Merci !!
RépondreSupprimer