Rechercher dans ce blog

Nombre total de pages vues (en milliers)

EXTRAIRE D'UNE CELLULE l'ADRESSES, le CODE POSTAL et la VILLE

Voici la méthode pour extraire d'une cellule l'adresse (N° et rue), le code postal et le nom de la ville :

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)

Positionnez vous dans la barre de formule puis validez avec les touches  maj+ctrl+entrée (*) :

 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)

 Positionnez vous dans la barre de formule puis validez avec les touches  maj+ctrl+entrée (*) :
 
 
 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) 

Positionnez vous dans la barre de formule puis validez avec les touches  maj+ctrl+entrée  (*) :
 
 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


22 commentaires :

  1. Bonjour,
    Tout 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.

    RépondreSupprimer
  2. Re-bonjour,
    Pour 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...).

    RépondreSupprimer
  3. Bonjour Arnaud,

    Pour 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


    RépondreSupprimer
  4. Bonjour,
    Super 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

    RépondreSupprimer
  5. Emile le petit génie :) Merci beaucoup pour ce super Tuto :)

    RépondreSupprimer
  6. Bonjour, Je dois extraire la ville dans cette ligne comment puis-je faire? Zinga Zanga, BEZIERS le 17/05/2019

    RépondreSupprimer
  7. bonjour j'ai tenté d'utiliser votre formule mais je n'y arrive pas..
    Pourriez 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)

    RépondreSupprimer
  8. Bonjour,
    Je 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 ^^.

    RépondreSupprimer
  9. 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
    MERCI

    RépondreSupprimer
  10. 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épondreSupprimer
    Réponses
    1. Pas de quoi.... le mérite vous revient du fait que vous avez fait la démarche de la mettre en application.

      Supprimer
  11. Bonjour, je n'arrive pas à faire fonctionner votre formule. Voici l'adresse :
    Ciments Calcia - 1, Rue du Fief D'argent - 79600 AIRVAULT qui en cellule A4

    RépondreSupprimer
    Réponses
    1. Bonjour
      Exemple 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)

      Supprimer
  12. Bonjour et merci pour ce tuto.
    Y 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 ?

    RépondreSupprimer
    Réponses
    1. 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

      Supprimer
  13. Bonjour,

    Je 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

    RépondreSupprimer
    Réponses
    1. Bonjour
      Avez-vous valider la formule avec les touches maj+ctrl+entrée ? car de mon côté cela fonctionne.

      Supprimer
  14. Bonjour,
    Tout 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.

    RépondreSupprimer
  15. 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épondreSupprimer
  16. Bonjour,
    Merci 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

    RépondreSupprimer
  17. Bonjour,
    Merci 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

    RépondreSupprimer

Pour vous aider à publier votre commentaire, voici la marche à suivre :
1) Ecrivez votre texte dans le formulaire de saisie ci-dessus
2) Si vous avez un compte, vous pouvez vous identifier dans la liste déroulante Commentaire
Sinon, vous pouvez saisir votre nom ou pseudo par Nom/URL
3) Vous pouvez, en cliquant sur le lien S'abonner par e-mail, être assuré d'être avisé en cas d'une réponse
4) Cliquer sur Publier enfin.

Le message sera publié après modération.
Merci