XL pour MAC extraction complexe

[danery]

Bonjour à tous,
Je maitrise un peu les formules grace aux tutos.
J'ai trouvé des exemple de comptage à partir premier ou du dernier espace mais pas après x espaces.
Mais sur ci-dessous je bloque.
Comment extraire les adresses (numéro et rue) de différents types de cellules comme :
43 RUE NATIONALE 17250 ST PORCHAIRE
CABINET SERMENT 188 RUE PELLISSIER 74700 SALLANCHES
9 RUE PAUL VAILLANT COUTURIER 18400 ST FLORENT SUR CHER
22 RUE DU CHAMP DE MARS 62300 LENS
CABINET DENTAIRE 16 ALLEE DE LA CLAIRIERE 77820 LE CHATELET EN BRIE
Cordialement

J.Eynard

 

[Phil69970]

Bonjour @danery

Je te propose ce fichier .....

Formule à valider par maj+ctrl+entrée

*Merci de ton retour

@Phil69970

 

[danery]

Bonjour,
Merci, cela fonctionne parfaitement quand l'adresse est en début de cellule mais dans le cas ci dessous, elle apparait en second derrière une série de mots (2 ou 3).
CABINET DENTAIRE 16 ALLEE DE LA CLAIRIERE .
Dois-je faire une seconde extraction pour ce type de cellule ?
Jacques

 

[Phil69970]

Re

Il y a tellement de possibilité qu'il me semble très difficile de le faire par formule
Pour moi une adresse ce n'est pas la raison sociale de l'entreprise (Cabinet dentaire, Bureau Machin, Local Truc, Entrepôt Bidule.....) et on pourrait continuer à l'infini y compris avec des nombres (Atelier du 5 eme étage ou Salon du 3 eme arrondissement etc....) donc j'ai bien peur que tu sois obliger de traiter à la main tout ces cas.
Dans la formule on part de la fin on cherche le code postal et on isole la ville et le code postal

@Phil69970

 

[danery]

Ok et merci.
Existe-t'il une formule qui récupère avant le premier caractère numérique en partant de la droite ?

 

[MP59]

Bonjour à tous,
une approche avec une fonction personnalisée avec une REGEX \d+\D+
voir fichier joint.

 

[danery]

Bonsoir,
Dans votre fichier cela fonctionne parfaitement, mais je ne sais pas l'intégrer au mien, il s'agit d'un fichier *.xlsx
Quelle est la procédure SVP

 

[Phil69970]

Bonjour @danery ,@MP59

Par contre cela ne fonctionne plus avec :

Comme je l'ai écrit au post #4 il y a des cas ou il faudra le faire à la main

y compris avec des nombres (Atelier du 5 eme étage ou Salon du 3 eme arrondissement

@Phil69970

 

[MP59]

Bonjour,
il faut enregistrer votre fichier en Xlsm, ouvir le VBE par Alt F11 et y placer le module qui est dans mon fichier (ou recopier la fonction).

Pour les cas plus complexes de Phil69970, il est tjs possible d'aménager la fonction.
par ex \d+\D+\d{5} et de supprimer le code postal par la suite (de faire un =Gauche ou une macro).

fichier joint avec les 2 fonctions

 

[danery]

Bonjour,
il faut enregistrer votre fichier en Xlsm, ouvir le VBE par Alt F11 et y placer le module qui est dans mon fichier (ou recopier la fonction).

Pour les cas plus complexes de Phil69970, il est tjs possible d'aménager la fonction.
par ex \d+\D+\d{5} et de supprimer le code postal par la suite (de faire un =Gauche ou une macro).

fichier joint avec les 2 fonctions

J'ai fais cela en recopiant la fonction, mais la cellule affiche #VALEUR!
Une idée ?

 

[MP59]

• Dans le ruban du VBE, ajouter une référence à regex sous Outils puis Références
• et en sélectionnant Microsoft VBScript Regular Expression 5.5

 

[danery]

Je n'est pas cela !

 

[MP59]

Alors je ne sais pas.

 

[danery]

Je suis sous mac, j'ai une machine virtuelle windows10, je vais tester excel et vous tiens au courant.
Merci pour tout.

 

[patricktoulon]

Bonjour
c'est un exercice qui a déjà été tenté
il te sera impossible de traiter toutes les forme de chaîne
une adresse peut commencer par un numéro ou pas
ça peut être une rue, un chemin ,un boulevard,une impasse , ou même par le nom d'une résidence
quand aux codes postals il peuvent être à la fin comme avant la ville
et j'en passe et des meilleures
et pour couronner le tout tu es sous MAC ce qui fai que pour gerer un regex c'est possible mais une torture

cela dit si tu y tiens
voici 5 fonctions perso utilisable en vba et formule
le complément d'adresse si il y a doit être en premier ensuite le numéro ensuite l'adresse ensuite le cdepostal ensuite la ville

a mettre dans un module

Function code_Postal(v As String)
    Dim T: T = Split(v, " ")
    For I = UBound(T) To 0 Step -1
        If IsNumeric(T(I)) And Len(T(I)) >= 5 Then code_Postal = Val(T(I))
    Next
End Function
Function ville(v As String)
    Dim T, I&: T = Split(v, " ")
    For I = UBound(T) To 0 Step -1
        If IsNumeric(T(I)) And Len(T(I)) >= 5 Then ville = Split(v, T(I))(1): Exit For
    Next
End Function

Function adresseX(v As String)
    Dim Add$, T, I&: T = Split(v, " ")
    For I = 0 To UBound(T)
        If IsNumeric(T(I)) And Len(T(I)) < 5 Then Add = Split(v, T(I))(1)
        If IsNumeric(T(I)) And Len(T(I)) >= 5 Then Add = Split(Add, T(I))(0)
    Next
    adresseX = Add
End Function

Function Numero(v As String)
    Dim num$, T, I&: T = Split(v, " ")
    For I = 0 To UBound(T)
        If IsNumeric(T(I)) And Len(T(I)) < 5 Then num = T(I): Exit For
    Next
    Numero = num
End Function

Function complementADresse(v As String)
    Dim cmpad, T, I&: T = Split(v, " ")
    For I = 0 To UBound(T)
        If IsNumeric(T(I)) Then cmpad = Split(v, T(I))(0): Exit For
    Next
    complementADresse = cmpad
End Function

exemple pour le numero en formule dans une cellule à étendre à souhait
=numero(A2)
et pareil pour les autre fonctions

mais tu n'aura JAMAIS UNE GARANTIE A 100% DE RESULTAT PROBAN FAIRE ATTENTION!!!!!