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

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 326
  • ADRESSES.xls
    18 KB · Affichages: 1 426
  • ADRESSES.xls
    18 KB · Affichages: 1 455

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
 

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

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
Bonsoir,
cf. fichiers. Formules adaptées du site de JB

=> chaînes de caractères
A+

Edit : bonsoir à Cisco:) et Softmama:)
 

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

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

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:

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

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.

@+
 

Discussions similaires

Statistiques des forums

Discussions
313 256
Messages
2 096 596
Membres
106 672
dernier inscrit
MFPaderne