Séparer l'adresse, le code postal et la ville ?

A

abouam

XLDnaute Nouveau
Bonjour et BONNE et HEUREUSE ANNÉE à vous tous !

je possède un tabelau qui contient des lignes comme suit :

rue de Siam 07100 Brest

Je souhaite avoir 3 colonnes qui auront comme titre :
Adresse
Code postale
Ville

Donc une extraction de données.
Voici mon fichier exemple
Merci
 

Pièces jointes

  • ADRESSES.xls
    18 KB · Affichages: 1 325
  • ADRESSES.xls
    18 KB · Affichages: 1 426
  • ADRESSES.xls
    18 KB · Affichages: 1 455
Trier par date Trier par votes
S

Softmama

XLDnaute Accro
Bonjour abouam

Tu peux tester cette macro :
Code: 
Sub extrac()
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, 4) = Mid(c, t + 6)
Set c = c(2, 1)
Loop
End Sub
 
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Séparer l'adresse, le code postal et la ville ?

Bonsoir à tous, bonsoir Softmama

Cf une solution en pièce jointe, avec des formules. A vérifier.

@ plus
 

Pièces jointes

  • ADRESSES(1).xls
    20.5 KB · Affichages: 2 923
  • ADRESSES(1).xls
    20.5 KB · Affichages: 3 035
  • ADRESSES(1).xls
    20.5 KB · Affichages: 3 105
Vote positif 0 Vote négatif
A

abouam

XLDnaute Nouveau
Re : Séparer l'adresse, le code postal et la ville ?

Merci à vous tous.
Il y a un souci !
La fonction marche bien mais pour les villes comme "Paris" ou "Lille" mais pas pour "La Rochelle" par exemple.

Y a t-il un moyen de régler celà ?
Merci encore
david84 à dit:
Bonsoir,
cf. fichiers. Formules adaptées du site de JB

=> chaînes de caractères
A+

Edit : bonsoir à Cisco:) et Softmama:)
Cliquez pour agrandir...
 
Vote positif 0 Vote négatif
D

david84

XLDnaute Barbatruc
Re : Séparer l'adresse, le code postal et la ville ?

Re
si je peux me permettre, simplement pour que le code proposé par Softmama affiche le format de nombre adéquat des codes postaux (éviter l'affichage de 7100 au lieu de 07100):
Sub extrac()
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
Cliquez pour agrandir...
De plus, j'ai l'impression qu'un seul Instr suffit : For t = InStr(c, " ") To Len(c) au lieu de
For t = InStr(InStr(c, " "), c, " ") To Len(c)
mais à vérifier...
Sur ce, bonne nuit.
 
Dernière édition:
Vote positif 0 Vote négatif
ROGER2327

ROGER2327

XLDnaute Barbatruc
Re : Séparer l'adresse, le code postal et la ville ?

Bonjour à tous
Une autre fonction personnalisée :
VB: 
Function toto(r, Optional u = 0)
Application.Volatile
Dim i%, j%, adr$, cp$, loca$, x
  x = Split(r)
  For i = 0 To UBound(x)
    If x(i) Like "#####" Then Exit For
  Next
  If i > UBound(x) Then
    adr = r.Value 'facultatif
  Else
    cp = x(i)
    For j = 0 To i - 1: adr = adr & x(j) & " ": Next
    adr = Left$(adr, Len(adr) + (Len(adr) > 1))
    For j = i + 1 To UBound(x): loca = loca & x(j) & " ": Next
    loca = Left$(loca, Len(loca) + (Len(loca) > 1))
  End If
  x = Array(adr, cp, loca)
  If 0 < u And u < 4 Then toto = x(u - 1) Else toto = x
End Function
Peut s'employer de deux manières. Si A2 contient rue de Siam 07100 Brest :
  1. Indexée :
    Code: 
    =toto($A2;1)
    renvoie rue de Siam
    Code: 
    =toto($A2;2)
    renvoie 07100
    Code: 
    =toto($A2;3)
    renvoie Brest.

  2. Matriciellement : sélectionner trois cellules contigües sur la même ligne et saisir
    Code: 
    =toto($A2)
    puis valider matriciellement (Ctrl Maj Entrée).
ROGER2327
#4871

Mardi 17 Décervelage 138 (Saint Mandrin, poète et philosophe, SQ)
25 Nivôse An CCXIX
2011-W02-5T01:10:21Z
 

Pièces jointes

  • ADRESSES_4871.xls
    35 KB · Affichages: 524
Vote positif 0 Vote négatif
BOISGONTIER

BOISGONTIER

XLDnaute Barbatruc
Repose en paix
Re : Séparer l'adresse, le code postal et la ville ?

Bonjour,

Matriciel:

Fonctionne pour des adresses du type:
11, rue de Siam 07100 Brest ou
rue de Siam 07100 Brest

Rue: =GAUCHE(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)-5)
CP: =STXT(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)-4;5)
Ville: =STXT(A2;NBCAR(A2)-EQUIV(VRAI;ESTNUM(--(STXT(A2;NBCAR(A2)-LIGNE($1:$255);1)));0)+2;99)

Fonctionne pour des adresses du type:
rue de Siam 07100 Brest

Rue: =GAUCHE(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)-2)
Code Postal: =STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0);5)
Ville: =STXT(A2;EQUIV(VRAI;ESTNUM(CNUM(STXT(A2;LIGNE($1:$255);1)));0)+6;99)

VBA:

Code: 
Function CodePostal(chaine)
  p = 1
  CodePostal = ""
  Do While p <= Len(chaine) - 4 And CodePostal = ""
    If Mid(chaine, p, 5) Like "#####" Then CodePostal = Mid(chaine, p, 5) Else p = p + 1
  Loop
End Function

Function Rue(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Rue = ""
    If Mid(chaine, p, 5) Like "#####" Then Rue = Left(chaine, p - 2) Else p = p + 1
  Loop
End Function

Function Ville(chaine)
  p = 1
  Do While p <= Len(chaine) - 4 And Ville = ""
    If Mid(chaine, p, 5) Like "#####" Then Ville = Mid(chaine, p + 6) Else p = p + 1
  Loop
End Function

JB
 

Pièces jointes

  • ADRESSES.xls
    48.5 KB · Affichages: 442
  • ADRESSES.xls
    48.5 KB · Affichages: 417
  • ADRESSES.xls
    48.5 KB · Affichages: 454
Dernière édition:
Vote positif 0 Vote négatif
CISCO

CISCO

XLDnaute Barbatruc
Re : Séparer l'adresse, le code postal et la ville ?

Bonjour à tous

Toujours avec la même méthode, mais avec une formule un peu plus courte en colonne B :

En B2
Code: 
STXT(A2;1;MIN(CHERCHE({"0";"1";"2";"3";"4";"5";"6";"7";"8";"9"};A2&"0123456789"))-1)
à tirer vers le bas.

@ plus
 

Pièces jointes

  • ADRESSES(1).xls
    21.5 KB · Affichages: 416
  • ADRESSES(1).xls
    21.5 KB · Affichages: 468
  • ADRESSES(1).xls
    21.5 KB · Affichages: 480
Vote positif 0 Vote négatif
Tibo

Tibo

XLDnaute Barbatruc
Re : Séparer l'adresse, le code postal et la ville ?

Salut Cisco, le fil,

Ce 17 décervelage correspond à la date de ce jour dans le calendrier pataphysique.

Je te laisse faire une recherche sur le Net sur ce calendrier pataphysique.

Tu pourras constater que ce calendrier est du plus grand sérieux.

@+
 
Vote positif 0 Vote négatif

Discussions similaires

V
  • Question
XL 2016 Formule pour récupérer un code postal
Réponses
7
Affichages
592
Staple1600
Staple1600
C
  • Question
Autres Dans Excel, à partir d'un UserForm, copier l'adresse sélectionnée dans enveloppe de publipostage Word et ouvrir la fenêtre "aperçu avant impression"
Réponses
11
Affichages
383
Chatron
C
K
  • Résolu(e)
Microsoft 365 Ouvrir un autre classeur excel (VBA) pour utiliser les données de celui dans le classeur actif
Réponses
5
Affichages
817
Dranreb
Dranreb
Y
  • Résolu(e)
XL 2016 Changement de recherche de critère des listes et adaptation aux usf
2 3 4
Réponses
56
Affichages
3 K
cp4
cp4
minipintade
  • Question
Autres comment extraire cp et ville d'une adresse dans excel 2007?
Réponses
9
Affichages
634
minipintade
minipintade
C
  • Résolu(e)
XL 2019 Saut à la ligne dans cellule
Réponses
8
Affichages
1 K
coloc52
C
dindin
  • Question
XL 2016 Filtrer un combobox par le code postal ou nom de ville
Réponses
2
Affichages
1 K
dindin
dindin
L
  • Question
XL 2013 Macros Calcul des variations de la masse salariale entre le mois M-1 et le mois M
Réponses
5
Affichages
818
Hasco
Hasco
S
  • Question
XL 2019 Tri d'un listbox à partir de variables tableaux (en "dur" dans le code)
Réponses
2
Affichages
757
herve62
herve62
J
  • Question
XL 2013 stock le plus proche d'un code postal
Réponses
5
Affichages
1 K
juju1978
J

Membres actuellement en ligne

23 membres et 281 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
312 102
Messages
2 085 302
Membres
102 857
dernier inscrit
Nony1931