Posez votre question Signaler

Separer adresse cp et ville dans excel [Résolu]

melissa4 11Messages postés mardi 16 janvier 2007Date d'inscription 16 juillet 2007Dernière intervention - Dernière réponse le 24 juin 2013 à 04:47
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
Lire la suite 
Réponse
+9
moins plus
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+
lermite222 8729Messages postés dimanche 8 avril 2007Date d'inscription ContributeurStatut 27 janvier 2014Dernière intervention - 25 sept. 2010 à 10:33
J'ai testé tes 3 adresses dans les 4 configurations possibles et je n'ai aucune erreur ?
Tu a bien copier exactement ce que tu a sur ton classeur ?
Répondre
DEN- 25 sept. 2010 à 15:02
Alors je suis "un âne", je ne comprends pas pourquoi cela ne fonctionne pas.
J'ai recopié ta macro et je l'ai adaptée à mon fichier, j'ai toujours le même bug, la boîte postale ne s'inscrit pas correctement dans la colonne prévue.
La macro fonctionne très bien sans les boîtes postales
il est ou le poste que tu as rajouté?
Répondre
lermite222 8729Messages postés dimanche 8 avril 2007Date d'inscription ContributeurStatut 27 janvier 2014Dernière intervention - 25 sept. 2010 à 16:31
Pas évident sur CCM, y a des postes qui sont pas visible sauf quand tu clic sur voir tout les postes.
pour rappel.. http://www.commentcamarche.net/faq/29629-vba-excel-separer-une-adresse-postale-dans-plusieurs-colonnes
Répondre
G34- 10 mai 2012 à 13:26
Merci beaucoup, tu viens de me faire économiser un temps fou.
Merci pour ton aide!!
Répondre
Paul suisse- 24 juin 2013 à 04:47
Salut l'ermite,

Je suis programmeur aussi sur VBA et j'ai modifier ta macro pour l'utiliser dans la séparation avec les codes postaux en suisse.

Je tenais juste à te remercier et te féliciter car ton code est propre et très bien expliqué. ça m'a éviter de perdre du temps à le comprendre.

Good job et merci

Paul
Répondre
Ajouter un commentaire
Réponse
+5
moins plus
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 :)
Patou- 23 oct. 2008 à 23:28
Bonsoir Loki,

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.
Répondre
Lillou- 5 nov. 2008 à 10:44
Moi perso ma colonne de départ ne sera jamais au même endroit et je bloque sur comment changer la fin de la macro. J'ai mit une inputbox pour demander le numéro de la colonne où se trouve les adresses et à la fin j'ai mis

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?") )
Répondre
montréal- 23 mars 2010 à 13:00
Bonjour Loki,

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,
Répondre
Greg- 23 mai 2013 à 11:40
Bonjour,

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 !
Répondre
Ajouter un commentaire
Réponse
+3
moins plus
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)
melissa4 11Messages postés mardi 16 janvier 2007Date d'inscription 16 juillet 2007Dernière intervention - 25 janv. 2007 à 11:23
ok merci beaucoup pour ton aide

c'est vraiment sympa
Répondre
M.Crofte- 21 févr. 2011 à 15:58
solution très efficace sans prise de tête. Merci
Répondre
Ajouter un commentaire
Réponse
+1
moins plus
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...)
Ajouter un commentaire
Réponse
+1
moins plus
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
gerard- 7 févr. 2007 à 16:03
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
Répondre
Ajouter un commentaire
Réponse
+1
moins plus
Re,
Voir cette astuce
A+
Ajouter un commentaire
Réponse
+0
moins plus
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 http://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.
Ajouter un commentaire
Réponse
+0
moins plus
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
Ajouter un commentaire
Réponse
+0
moins plus
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...
Ajouter un commentaire
Réponse
+0
moins plus
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...
melissa4 11Messages postés mardi 16 janvier 2007Date d'inscription 16 juillet 2007Dernière intervention - 25 janv. 2007 à 11:00
c'est à faire pour environ 3000 lignes d'adresses (donc le séparateur j'ai déjà essayé mais j'ai perdu trop de cheveux donc je cherche une autre méthode où il n'y a pas autant de retraitement à faire)

tu peux m'en dire plus sur le vba?
Répondre
Ajouter un commentaire
Réponse
+0
moins plus
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
;-))
Ajouter un commentaire
Réponse
+0
moins plus
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
blux 23286Messages postés dimanche 26 août 2001Date d'inscription ModérateurStatut 23 avril 2015Dernière intervention - 25 janv. 2007 à 12:21
Dans le message de jicipy, il y a un lien, reste juste à le suivre ! :-)
Répondre
gerard- 7 févr. 2007 à 11:28
salut melissa

je voudrais savoir si tu as résolu ton pb, parce que j'ai exactement la meme chose a faire....

merci d'avance

gérard
Répondre
Ajouter un commentaire
Réponse
+0
moins plus
C'est ce que j'ai proposé en <7>...
xkristi 4272Messages postés lundi 18 décembre 2006Date d'inscription 3 janvier 2014Dernière intervention - 7 févr. 2007 à 16:04
Blux , j'ai vu
mais apparemment , elle n'avait pas assimilé ton propos !
mais vrai : tu as raison ! mes excuses : passe donc devant (sourire)
Répondre
Ajouter un commentaire
Réponse
+0
moins plus
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
Ajouter un commentaire
Ce document intitulé «  separer adresse cp et ville dans excel  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.

Vous n'êtes pas encore membre ?

inscrivez-vous, c'est gratuit et ça prend moins d'une minute !

Les membres obtiennent plus de réponses que les utilisateurs anonymes.

Le fait d'être membre vous permet d'avoir un suivi détaillé de vos demandes.

Le fait d'être membre vous permet d'avoir des options supplémentaires.