Microsoft 365 [Résolu] Separer Nom, Ligne d'adresse, Cp et Ville en VBA

[McMidou84]

Bonjour à tous,

Je sollicite votre aide pour trouver une macro capable de séparer par colonne une adresse du type :

M TRUQUE NATHANEL SYLVESTRE RUPERT 0012 IMPASSE DU SENS UNIQUE 27040 FAINFOND

Pour l'instant j'essaye avec cette logique (qui est une formule qui ne fonctionne pas encore ) :

Sub Extraire_ADRESSES_CODESPOSTAUX_VILLES()
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, 500)
Set c = c(2, 1)
Loop

End Sub

Private Sub CommandButton1_Click()
iR = Cells(65535, 1).End(xlUp).Row
For i = 1 To iR
Codep = ADCOD(Cells(i, 1))
iPos = InStr(Cells(i, 1), Codep)
Cells(i, 2) = Left(Cells(i, 1), iPos - 1)
Cells(i, 3) = Codep
Cells(i, 4) = Mid(Cells(i, 1), iPos + 6)
Next
End Sub

Private Function ADCOD(c)
Application.Volatile
Set obj = CreateObject("vbscript.regexp")
obj.Pattern = "\d{5}"
Set a = obj.Execute(c)
If a.Count > 0 Then ADCOD = a(0) Else codepostal = ""
End Function

Pouvez vous, s'il vous plait, me dire comment faire en sorte qu'elle fonctionne?

En vous remerciant par avance,

Bien cordialement

Midou

 

[Staple1600]

Re

Re-inventons la roue

adresse.data.gouv.fr

adresse.data.gouv.fr

PS:
Macistes, le mode de REGEX n'est pas pour vous (si vous passez par un CreateObject)

 

[GALOUGALOU]

bonsoir staple 1600, bonsoir le fil
le lien que tu nous confies nous permet d’accéder au fichier base adresse nationale, qui sert à établir des points de géocodage à destination de fabricant de carte, de gestion GPS, d'organisation des secours, d'édition de code hexaclé dans le cadre de l'installation de la fibre.
en pièce jointe toutes les informations a intégré à un fichier adresse.
je ne pense pas que se soit d'un grand secours dans le but d'établir un mailing
mais peut-être que je me trompe. je ne demande qu'a être éclairé.
cordialement
galougalou

 

[Staple1600]

Re

Alors avec un lien plus précis

Bases Adresses Locales | adresse.data.gouv.fr

Bases de données Adresse de périmètre local, éditées sous la responsabilité des collectivités locales.

adresse.data.gouv.fr

Et un exemple

Aiguilhe (43002) - Base Adresse Nationale | adresse.data.gouv.fr

Consultez les 67 voies et 688 adresses de la commune de Aiguilhe

adresse.data.gouv.fr

 

[GALOUGALOU]

je vais faire mon bête (j'adore ça, il ne faut pas m'en vouloir)
commune de aiguilhe, a la même adresse il peut y avoir plusieurs destinataires, (surtout dans le cas d'un immeuble) et le clic sur la commune nous donne les point de géolocalisation, pas les noms patronymique. (je triche j'ai eu l'occasion de remplir ce fichier)
@+

 

[Staple1600]

Re

Personne n'est bête ici
(voir la charte du forum)
Je voulais juste informer qu'il existe ce site avec des adresses normalisées
(avec téléchargement possible et gratuit)
Ce qui évite les erreurs de saisie de fichiers adresse.

 

[patricktoulon]

re
Bonjour le fil
bon ben je me suis un peu amusé moi aussi

'*************************************************
'décanté une addresse au format:
'[civilité], [nom et ou prenom],[adresse],[codepostal],[ville])
' patricktoulon exceldownload
'version 1.0
'Date version:28/02/2020
' s'utilise en formule ou en VBA
'**************************************************

Option Explicit
Public Function GlobalRegex1(cel As String, Argu As String) As String
   Dim Matches, ccp&, temp$
   Application.Volatile
    With CreateObject("vbscript.regexp")
        .Global = True: .IgnoreCase = True
        Select Case Argu
            '--------------------------------------------------------------------------------------
        Case "civilité"
            .Pattern = "^(mr\s(et|ou)\smme\s|mr\s|melle\s|m.\s|m\s|mme\s|dr\s|docteur\s)"    ' doit commencer par
            Set Matches = .Execute(cel)
            If Matches.Count > 0 Then GlobalRegex1 = Trim(Matches(0))
            '--------------------------------------------------------------------------------------
        Case "adresse"
            ccp = GlobalRegex1(cel, "codepostal")
            .Pattern = "(\d{5})"    'tout jusqu'a une chaine de 5 chiffre
            Set Matches = .Execute(cel)
            If Matches.Count > 0 Then temp = Mid(cel, 1, InStrRev(cel, Matches(Matches.Count - 1)) - 1)
            Debug.Print temp
            'on relance le regex pour ne recupérer la chaine qu'a partir d'un numero ou du nom commun (rue,chemin,etc....)
            'avec un pattern 2 options (xxxx abcdef...) ou ((rue/chemin/etc....) abcdefg....)
            Set Matches = Nothing
            .Pattern = "((\d{1,6})|rue|les|route|chemin|avenue|allée|allee|boulevard|impasse|place|lieu|résidence|residence|hameau|ferme|bar)"
            Set Matches = .Execute(CStr(temp))
            GlobalRegex1 = Mid(temp, InStr(1, temp, Matches(0)))
            '--------------------------------------------------------------------------------------
        Case "codepostal"
            .Pattern = " \d{5} "
            Set Matches = .Execute(cel)
            If Matches.Count > 0 Then GlobalRegex1 = Trim(Matches(Matches.Count - 1))

            '--------------------------------------------------------------------------------------
        Case "ville"
            .Pattern = " \d{5} .*$"
            Set Matches = .Execute(cel)
            If Matches.Count >= 1 Then GlobalRegex1 = Right(Matches(0), Len(Matches(0)) - 7)

            '--------------------------------------------------------------------------------------
        Case "nom"
            'peut se faire eventuellement avec SUBSTITUE dans les cells
            Dim z(1 To 4), I&: temp = cel
            z(1) = GlobalRegex1(cel, "civilité")
            z(2) = GlobalRegex1(cel, "codepostal")
            z(3) = GlobalRegex1(cel, "adresse")
            z(4) = GlobalRegex1(cel, "ville")
            For I = 1 To 4: temp = Replace(temp, z(I), ""): Next
            GlobalRegex1 = Trim(temp)
        End Select

    End With
End Function

ps: un petit rajout

.Pattern = "((\d{1,6})|rue|chemin|avenue|allée|allee|boulevard|impasse|place|route|lieu)"

 

[jmfmarques]

Il y a probablement une bonne raison pour laquelle cette adresse, au demeurant existante, :
BAR LE 421 37 Rue Fresque, 30000 Nîmes
sera traduite ainsi :

BAR LE 421 37 Rue Fresque, 30000 Nîmes

BAR LE

421 37 Rue Fresque,

30000

Nîmes

Et ce n'est là qu'un exemple parmi une foultitude de cas où ton code, Patricktoulon, génèrera des adresses inexploitables. Sans compter, en plus, toutes les abréviations de voies et toutes les autres dénominations possibles de voies et/ou localisations.

 

[patricktoulon]

re
Bonjour jmfmarques
oui je sais mais comme je l'ai dis

'[civilité], [nom et ou prenom],[adresse],[codepostal],[ville])

ça s'adresse surtout a un format d'adresse particulier mr/.. abcdef adresse cp ville
rajouter éventuellement les subpattern manquants soit en dur soit en motifs dans les deux groupes pour adresse

ps j'en profite pour ajouter

.Pattern = "((\d{1,6})|rue|chemin|avenue|allée|allee|boulevard|impasse|place|route|lieu|résidence|residence|hameau)"

fonctionne avec

mr toto résidence les floralie 654 chemin du vertge 54230 totoville	mr	toto	résidence les floralie 654 chemin du vertge	54230	totoville
mr toto 654 chemin du vertge résidence les floralie 54230 totoville	mr	toto	654 chemin du vertge résidence les floralie	54230	totoville

[/CODE]

 

[jmfmarques]

ça s'adresse surtout a un format d'adresse particulier mr/.. abcdef adresse cp ville
rajouter éventuellement les subpattern manquants soit en dur soit en motifs dans les deux groupes pour adresse

ps j'en profite pour ajouter

Et tu comptes en rajouter, combien, combien de fois, etc ... ?
A chaque fois que je te montrerai un cas que tu n'as pas prévu ? Il risque fort d'y en avoir des milliers ...
Tiens, en voilà un tout bête (j'ai modifié le nom et la ville de cette adresse pour des raisons de discrétion):

M. Jim NASTIQUE Ferme Les Anges km 24 route des 4 Chemins 60000 VILLE

--->>

M.

Jim NASTIQUE Ferme Les Anges km

24 route des 4 Chemins

60000

VILLE

Tu voudras bien comprendre que je ne vais pas passer mon temps à continuer à te sortir du fagot des milliers de cas.
Je sais depuis très longtemps que c'est sans issue.

 

[patricktoulon]

re
je sais très bien que tous les cas ne pourront être traités
comme je le redis éventuellement rajouter les motifs SURTOUT!!! pour les désignation tel que résidence hameau ferme bar etc.....
si le format tel que j'ai cité plus haut et que les subpattern y sont y a pas d'erreurs

 

[patricktoulon]

re
pour info

on pourrait même créer une constante générale "boulangerie|patisserie|etc...."
je peut même ajouter un argument dans la fonction en optional ,un item qui n'y seraient pas pour les occasions spéciales

 

[Staple1600]

Bonsoir le fil

[mon grain de sel - FBQJMOPTDT]
En relisant le message#1, une question m'étreint le cortex préfrontal.
Pourquoi le demandeur veut séparer son string ?
Sans doute pour un publipostage, non ?
Si oui, alors on a besoin que de trois colonnes (voire 4)
NOM|ADRESSE|CP_VILLE
avec 4
CIV|NOM|ADRESSE|CP_VILLE
et là, il y tout ce qui faut dans les archives du forum (ou ailleurs) pour que la séparation se fasse en bons termes
Un exemple parmi beaucoup

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

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

www.excel-downloads.com

[/mon grain de sel - FBQJMOPTDT]

 

[patricktoulon]

re
bonsoir Staple1600 parce que c'est loin de fonctionner

Function ExtractionRue(Cellule As Range) As String
Application.Volatile
With CreateObject("vbscript.regexp")
.Global = True
.Pattern = "^.* \d{5} "
Set Matches = .Execute(Cellule.Text)
If Matches.Count >= 1 Then ExtractionRue = Left(Matches(0), Len(Matches(0)) - 7)
End With
End Function

je vais pas plus loin dans les essais du lien c'est pas la peine
et qu'est ce que ça pourrait faire d'autre? .Pattern = "^.* \d{5} " qui veut dire tout jusqu'a la serie de 5 chiffres qui deja en soit ne fonctionnerait pas avec
rue du tournevis vs 20000 83200 toulon

bien que la c'est facilement corrigeable en remplacant
Left(Matches(0), Len(Matches(0)) - 7
par
Left(Matches(Matches.count-1), Len(Matches(matches.counts-1)) - 7

ben pour du publipostage tu repassera

je parle même pas des autre versions sans regex qui sont basées sur un numéro de rue/chemin/etc
alors quand il n'y en a pas ben walouh !!!

 

[Staple1600]

Re, Bonsoir patricktoulon

=>patricktoulon
Ce qu'il fallait retenir de mon grain de sel, c'était d'abord ceci

Sans doute pour un publipostage, non ?
Si oui, alors on a besoin que de trois colonnes (voire 4)
NOM|ADRESSE|CP_VILLE
avec 4
CIV|NOM|ADRESSE|CP_VILLE

Ensuite peu importe la suite

NB: Pas besoin de séparer le CP de la VILLE pour un publipostage
(en tout cas, c'est ce je fais quand je publieposte )
<NOM>
<ADRESSE>
<CP_VILLE>
*: les trois lignes ci-dessus sont censées représenter mes champs de fusion sur un document Word

 

[patricktoulon]

re
j'ai très bien compris ton point de vue mais avec ce qui est proposé dans le lien c'est pas possible
on a juste la ville et le cp de valable à 100%
ce qui n'est pas très compliqué avec le matches.count-1 de "(\d{5})*$" en coupant pas le 1er espace
mais pour le teste c'est mort