Separer adresse cp et ville dans excel
Résolu
melissa4
Messages postés
11
Statut
Membre
-
Millemercis -
Millemercis -
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
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
A voir également:
- Excel l'adresse
- Darkino nouvelle adresse - Guide
- Liste déroulante excel - Guide
- Adresse mac - Guide
- Word et excel gratuit - Guide
- Changer adresse dns - Guide
14 réponses
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 :)
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.
A+
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+
Bonjour,
J'ai utilisé votre macro sur un fichier d'adresses, elle fonctionne très bien. C'est formidable ce que vous faites, faire profiter aux autres de votre savoir. J'ai une question cependant, peut-on utiliser la même procédure pour la boîte postale si oui comment faire évoluer cette partie du code quand a rajouter Cbp As string integer
For e = 2 To Len(Txt)
If Mid(Txt, e, 1) <> " " And IsNumeric(Mid(Txt, e, 5)) Then
.Cells(i, Cbp) = Left(Txt, e - 1)
J'ai utilisé votre macro sur un fichier d'adresses, elle fonctionne très bien. C'est formidable ce que vous faites, faire profiter aux autres de votre savoir. J'ai une question cependant, peut-on utiliser la même procédure pour la boîte postale si oui comment faire évoluer cette partie du code quand a rajouter Cbp As string integer
For e = 2 To Len(Txt)
If Mid(Txt, e, 1) <> " " And IsNumeric(Mid(Txt, e, 5)) Then
.Cells(i, Cbp) = Left(Txt, e - 1)
Bonjour,
exemple d'adresse :
12 Rue des Egletières BP 100 75008 PARIS
et je voudrais mettre la boîte postale 100 (BP 100) dans une autre colonne, ce serait la col addresse 2 par exemple
j'ai déjà la col adress, la col code postal, la col ville, ta macro fonctionne très bien
mais quand elle rencontre un n° BP (boîte postale), elle bug et mets automatique le chiffre qui accompagne la BP dans la colonne code postal.
exemple d'adresse :
12 Rue des Egletières BP 100 75008 PARIS
et je voudrais mettre la boîte postale 100 (BP 100) dans une autre colonne, ce serait la col addresse 2 par exemple
j'ai déjà la col adress, la col code postal, la col ville, ta macro fonctionne très bien
mais quand elle rencontre un n° BP (boîte postale), elle bug et mets automatique le chiffre qui accompagne la BP dans la colonne code postal.
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...).
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)
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...)
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...)
Vous n’avez pas trouvé la réponse que vous recherchez ?
Posez votre question
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
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
OK
j'ai finalement trouvé une solution, avec la fonction TROUVE ; mais mon fichier ne comportait des adresses avec codes postaux que de 4 départements différents
et donc j'ai fait un test sur la chaine de caracteres (à quel endroit se trouvait le numéro de dept)....
c'est pas optimal, mais ca a marché et etait tres rapide
j'ai finalement trouvé une solution, avec la fonction TROUVE ; mais mon fichier ne comportait des adresses avec codes postaux que de 4 départements différents
et donc j'ai fait un test sur la chaine de caracteres (à quel endroit se trouvait le numéro de dept)....
c'est pas optimal, mais ca a marché et etait tres rapide
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.
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
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...
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...
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 :
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
je vais essayer de me débrouiller avec toutes ces infos
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
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
Un grand merci car tu viens de me faire ganger au moins une journée de travail pour séparer environ 2000 adresses dans un but de publipostage.
Bonne continuation et bon développement.
Cells(Cpt, (col + 1)) = Adresse
Cells(Cpt, (col + 2)) = CP
Cells(Cpt, (col + 3)) = Ville
Mais ça bug!
(mon inputbos; col = InputBox("Dans quelle numéro de colonne se trouve l'adresse?") )
Un grand merci pour cette belle macro que j'ai lancé. Elle a fonctionné sur une douzaine de cellules puis s'est stoppée.
Juste deux petites questions, si tu as un moment :
- quelle serait la formule a ajouté pour qu'elle ne s'arrête pas sur les champs vides (sans adresse) ?
- quelle serait la formule a ajouté pour que la distinction soit faite entre le CP et la ville lorsque les deux se trouvent sur la même ligne après un retour chariot ?
Bien cordialement,
Merci pour cette macro qui m'a fait gagner du temps.
Juste quelques remarques,
-la première fonction mode_calcul() ne sert à rien pour le problème posé
-La fonction nb_lignes() ne renvoyait pas le bon nombre de lignes dans mon cas (Excel 2007), j'ai du utiliser la propriété Sheets(1).UsedRange.Rows.Count
-La macro a fonctionné sur 3500 adresses sans problème, seule une adresse m'a posé problème: 12 rue du test ET1 62250 ville -> le code postal retenu était "1 62250" que j'ai du corriger à la main.
Sinon bravo à l'auteur et un grand merci !
Marche aussi sous Office 2013, c'est bon à savoir ;)