Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

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

McMidou84

XLDnaute Nouveau
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
 

GALOUGALOU

XLDnaute Accro
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
 

Pièces jointes

  • fichier ban.JPG
    32.9 KB · Affichages: 19

GALOUGALOU

XLDnaute Accro
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

XLDnaute Barbatruc
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

XLDnaute Barbatruc
re
Bonjour le fil
bon ben je me suis un peu amusé moi aussi
VB:
'*************************************************
'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
Code:
.Pattern = "((\d{1,6})|rue|chemin|avenue|allée|allee|boulevard|impasse|place|route|lieu)"

 

Pièces jointes

  • Patrick décante adresse with regex - Copie.xlsm
    23.7 KB · Affichages: 13
Dernière édition:

jmfmarques

XLDnaute Accro
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îmesBAR LE421 37 Rue Fresque, 30000Nî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

XLDnaute Barbatruc
re
Bonjour jmfmarques
oui je sais mais comme je l'ai dis
VB:
'[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
VB:
.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 totovillemrtotorésidence les floralie 654 chemin du vertge54230totoville
mr toto 654 chemin du vertge résidence les floralie 54230 totovillemrtoto654 chemin du vertge résidence les floralie54230totoville
[/CODE]
 
Dernière édition:

jmfmarques

XLDnaute Accro
ç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 km24 route des 4 Chemins 60000VILLE

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

XLDnaute Barbatruc
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

 

Staple1600

XLDnaute Barbatruc
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
[/mon grain de sel - FBQJMOPTDT]
 

patricktoulon

XLDnaute Barbatruc
re
bonsoir Staple1600 parce que c'est loin de fonctionner
VB:
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

XLDnaute Barbatruc
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
 
Dernière édition:

patricktoulon

XLDnaute Barbatruc
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
 

Discussions similaires

Réponses
7
Affichages
592
Réponses
12
Affichages
453
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…