Separer adresse cp et ville dans excel

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 :)

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+

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)

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...)

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

Re,
Voir cette astuce
A+

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.

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

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...

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...

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

;-))

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

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

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