separer adresse cp et ville dans excel Résolu/Fermé

Question

bonjour

j'ai récupéré un fichier excel ou dans la meme colonne se trouve l'adresse le code postal et la ville je souhaiterais séparer cette colonne en 3 (une pour ladresse lautre pour le code postal et une pour la ville) mais je ne sais pas quelle est la formule pour que excel separe ce qui est a droite du code postal et a gauche du code postal de facon a separer les elements en 3 colonne.

il existe un convertisseur dans excel qui delimite aux espaces par exemple mais c vraiment pas pratique donc est ce que vous connaissez la formule magique?

merci pour votre aide

Loki · Accepted Answer

Bonjour tout le monde, j'ai eu exactement le meme probleme que vous pour separer les adresses de type : 

12 rue tolbiac 75005 PARIS

 et pour les prochains voila la solution :

Dans excel cliquez sur outils puis dans Macros lancez le Visual Basic editor.

là, allez dans VBA Project
dans le dossier module et enfin dans Module1

La dans la grande fenetre faites un joli copier coller de la macro que voila (comprise entre les 2 lignes) :

----------------------------------------------------------------------------------------------------------------

Function mode_calcul() As String
    If (Cells(1, 43).Value = 0 Or IsNull(Cells(1, 43).Value)) Then
        If (Cells(1, 46).Value = 0 Or IsNull(Cells(1, 46).Value)) Then
            If (Cells(1, 60).Value = 0 Or IsNull(Cells(1, 46).Value)) Then
                mode_calcul = ""
            Else
                mode_calcul = "-SLR"
            End If
        Else
            mode_calcul = "-NET"
        End If
    Else
        mode_calcul = "-BRT"
    End If
    
    
End Function

Function nb_lignes() As Integer
    Dim celluleCourante As Variant
    Set celluleCourante = Range("A1")
    nb_lignes = 1
    Do While Not IsEmpty(celluleCourante)
        Set celluleCourante = celluleCourante.Offset(1, 0)
        nb_lignes = nb_lignes + 1
    Loop
    If nb_lignes = 1 Then nb_lignes = 2
End Function


Sub Adresses()
    Dim dimension As Integer
    Dim Cpt As Integer
    Dim Cpt2 As Integer
    Dim Adresse As String
    Dim Ville As String
    Dim CP As String
    Dim finCP As Integer
    Dim chaine As String
    Dim Position As Integer
    
    dimension = nb_lignes()
    For Cpt = 1 To dimension
        chaine = Cells(Cpt, 1)
        Position = 0
        For Cpt2 = 1 To Len(chaine) - 5
            If IsNumeric(Mid(chaine, Cpt2, 6)) And Cpt2 > Position + 2 Then
                Position = Cpt2
            End If
        Next Cpt2
        If Position > 0 Then
            For finCP = Position To Len(chaine)
                If Trim(Mid(chaine, Position, finCP - Position)) <> "" Then
                    If Not IsNumeric(Mid(chaine, Position, finCP - Position)) Or Len(Mid(chaine, Position, finCP - Position)) > 7 Then Exit For
                End If
            Next finCP
            If finCP > Len(chaine) Then finCP = Len(chaine) + 2
            CP = Trim(Mid(chaine, Position, finCP - Position - 1))
            finCP = finCP - 1
            Adresse = Trim(Left(chaine, Position - 1))
            If finCP > Len(chaine) Then finCP = Len(chaine) + 1
            Ville = Trim(Right(chaine, Len(chaine) - finCP + 1))
            Cells(Cpt, 1) = Adresse
            Cells(Cpt, 2) = CP
            Cells(Cpt, 3) = Ville
        End If
    Next Cpt
    Range("B1", "B" & dimension).Replace What:=" ", Replacement:=""
End Sub

-----------------------------------------------------------------------------------------



Une fois cela copié : 
mettez toutes vos adresses dans la Colonne A (la premiere de votre fichier) et retournez dans outils, Macros, Macros et cliquez sur la jolie petite macro appelée Adresses.
Et hop c magique vos adresses sont séparés de vos codes posteaux et de votre ville :) :) :) 
C beau non

enfin derniere petite precision si la colonne A ne vous plait pas modifiez les trois lignes en fin de macro 
            Cells(Cpt, 1) = Adresse
            Cells(Cpt, 2) = CP
            Cells(Cpt, 3) = Ville

avec les numeros de colonne que vous désirez sachant que votre adresse concaténée doit se trouvée dans Adresse
que le numero et la rue s'y trouverons tjs et que CP et ville seront comme leur nom l'indiquent les colonnes Code postal et ville.
Bon travail a vous tous messieurs dames et j'espere que vous aurez gagner du temps grace à ca :)

_goni · Answer

Je peux te proposer d'aller sur des site comme http://www.excelabo.net/xl/caracteres.php , http://bvrve.club.fr/Astuces_Michel/excel.html#Manip ou encore https://silkyroad.developpez.com/VBA/ManipulerChainesCaracteres/ . 
Maintenant, si je ne réponds pas à ce genre de post c'est pcq j'en sais pas plus que toi mais je tape trois mot dans google (manipuler chaine caractère) et je trouve une tone de choses très intéressantes.

melissa4 · Answer

merci mais je suis déjà allée vois ces sites mais il ne donne pas la solution à mon problème car moi je cherche à enlever 5 chiffres qui se suivent (le code postal) au milieu d'une ligne de caractères texte (adresse à droite et ville à gauche)

il doit exister une formule qui reconnait les chiffres au milieu de lettre ou qui puisse me dire a partir de quel caractere (quel nombre) commence les chiffres et a partir de quel caractere ils finissent (du style =DROITE() ou =GAUCHE()

j'ai trouvé une formule:
{=1*STXT(A1;EQUIV(0;ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:"&NBCAR(A1)));1)*1)*1);0);NBCAR(A1)-SOMME((ESTERREUR(STXT(A1;LIGNE(INDIRECT("1:&NBCAR(A1)));1)*1)*1)))}
mais ça ne marche pas

blux · Answer

Salut,

il n'y a pas de fonction toute faite pour 'exploser' une chaine de caractères, n'oublie pas qu'excel, à la bse, est un tableur et manipule des nombres...

Il existe la fonction ESTNUM pour savoir si une valeur est de type numérique et la fonction CHERCHE, pour trouver un caractère particulier, mais pas de fonction cherchant une classe de caractères...

La fonction que tu as collée ne marche pas, dis-tu, mais qu'est-ce qui ne marche pas ?

Comment est ton adresse, y'a-t'il un numéro de rue ? (parce que ça peut compliquer...)

melissa4 · Answer

j'ai un fichier excel avec une colonne de ce type

1 Rue Lecouteux 41600 LAMOTTE BEUVRON
4 Rue Corse 65000 TARBES
Parc Synergie Val de Loire 45130 MEUNG SUR LOIRE

le code postal n'est jamais au meme endroit dans la cellule (dans l'adresse 1 : le 1er chiffre du code postal est en 17eme position en comptant les espaces alors que pour l'adresse 2 il est en 13eme position) donc il faut commencer par calculer le nombre total de caratere dans la cellule (formule =NBCAR(A1)) (celle la je la connais)
puis il faut calculer le nombre de caractere avant et apres le code postal (formule du style =1*STXT(A1;P;5) ou avec la formule cherche et trouve) enfin je n'en sais rien mais je sais que c'est possible

HELP...

blux · Answer

C'est à faire une seule fois ?
Ou à chaque nouvelle donnée entrée ?

Parce qu'on peut éclater la cellule avec 'espace' comme séparateur et ensuite regrouper les champs ainsi découpés...

Sinon, faut passer par du VBA...

blux · Answer

En VBA, on écrit un programme, donc on fait ce qu'on veut...

Sinon, je te propose un moyen simple :

Tu recopies ces deux formules dans les colonnes après ta donnée, et tu les dupliques deux à deux (colonne B vers colonne D, F... et colonne C vers E,G...).=GAUCHE(A1;TROUVE(" ";A1))
=DROITE(A1;NBCAR(A1)-TROUVE(" ";A1))Au bout d'un moment, tu t'apercevras qu'en colonne H, si tes données sont structurées de la même façon, il te reste ton code postal.

Ne te reste ensuite qu'à concaténer les autres colonnes...

Un conseil cependant : à moins que tu n'en aies vraiment pas besoin, il est plus intéressant de conserver les données de l'adresse éclatées... (si tu veux faire un publipostage, par exemple, cela te permet de positionner les champs où tu veux et de gérer ton format d'adresse)

jipicy · Answer

Salut,

En enregistrant ton fichier au format "CSV", puis en le "parsant" avec un éditeur de flux comme Sed pour Windows, tu pourrais facilement avoir (en ligne de commande) :

- Le fichier original :1 Rue Lecouteux 41600 LAMOTTE BEUVRON
4 Rue Corse 65000 TARBES
Parc Synergie Val de Loire 45130 MEUNG SUR LOIRE- La ligne de commande :sed 's/$.*$ $[0-9]\{5\}$ $.*$/\1;\2;\3/' adress.txt- Le fichier en sortie :1 Rue Lecouteux;41600;LAMOTTE BEUVRON
4 Rue Corse;65000;TARBES
Parc Synergie Val de Loire;45130;MEUNG SUR LOIRE;-))

melissa4 · Answer

merci c'est cool mais il faut que je télécharge le logiciel sed c'est ça? (oh la honte je suis vraimant une débutante)
je vais essayer de me débrouiller avec toutes ces infos

xkristi · Answer

J'ai contourné le problème en enregistrant ce fichier en .prn
ensuite je suis aller le renommer en enlevant l'extension.
et quand je l'ouvre dans Excel , je tombe sur l'assistant importation de texte 
je choisis type de fichier délimité , suivant et je coche le délimiteur espace , j'obtiens ainsi autant de colonnes qu'il y de mot
ensuite je concatène les colonnes  que je veux

C'est un truc presque de débutant , mais je ne vois pas autrement

blux · Answer

C'est ce que j'ai proposé en <7>...

lermite222 · Answer

Bonjour,
Pour d'éventuel suivant, une macro un peu simplifiée.
Sub SeparAddresse()
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 = 3 'Colonne où mettre l'adresse - ici C
    CcodeP = 4 'Colonne où mettre le CP - ici D
    Cville = 5 'Colonne où mettre la ville - ici E
    DebLig = 6 'Ligne où commence le split.
    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
A+

Butagaz38 · Answer

Merci à vous.
Vous me faites économiser plusieurs jours de travail !

Dans la dernière macro ça coince un chouilla si il y'a une boite postale ou qu'il n'y a pas d'adresse (juste le nom de la ville) mais je ne vais pas non plus faire la fine bouche, reprendre 10 adresses sur 900 c'est pas la mort !

Un grand merci encore !

Butagaz

lermite222 · Answer

Re,
Voir cette astuce
A+

Separer adresse cp et ville dans excel

14 réponses

Discussions similaires