XL 2021 Séparer les adresses (Rue et N°) en 2 cellules avec un macro

Banbara · 7 Août 2024

Bonjour à tous,

Séparer les adresses (Rue et N°) en 2 cellules avec un macro

J'ai un fichier d’adhérents d’une association qui comporte une colonne « Adresse_Rue » dans laquelle se trouve le N° et le nom de la rue. Je voudrais séparer en 2 colonnes, une pour les N°, et une pour le nom de la rue et que cela me permette aussi de pouvoir trier les noms de rue pour avoir toutes les lignes de la même rue les unes à la suite des autres. Mon fichier comporte les adresses sous cette forme :

5 Rue des Eruitys

10 Pont Gigoule

13 bis Rue des sentes

10 C Rue du Littoral

J'ai trouvé sur le forum un fichier qui s’appelle : Exemple-forum.xls qui a l’air de fonctionner de cette manière, qui pourrais peut-être, être adapté à mon cas, mais je ne sais pas faire. Ci-joint la copie de mon fichier au niveau de l’organisation de celui-ci.

Merci par avance de votre aide.

job75 · 12 Août 2024

Bonjour à tous,

Perso j'utiliserai plutôt une liste des voies et cette formule matricielle en C2 :

Code:

=SIERREUR(SUPPRESPACE(GAUCHE(B2;PETITE.VALEUR(SI(ESTNUM(CHERCHE(Voie;B2));CHERCHE(Voie;B2)-1);1)));"")

Sur les versions récentes d'Excel la validation matricielle par Ctrl+Maj+Entrée n'est pas nécessaire.

A+

Phil69970 · 7 Août 2024

Bonjour @Banbara et bienvenu sur XLD

Donne nous le résultat attendu sur ton fichier pour les exemple que tu as mis

Banbara à dit:
5 Rue des Eruitys

10 Pont Gigoule

13 bis Rue des sentes

10 C Rue du Littoral

Et aussi celui ci

41 Coteau de Tournebride

ou bien

130 bis commandant Jean Saisrien

TooFatBoy · 7 Août 2024

Bonjour,

Banbara à dit:
Séparer les adresses (Rue et N°) en 2 cellules avec un macro

Tu mets celui-là entre le numéro et la rue, ça te permettra de séparer les deux.

Loin déjà je suis...

TooFatBoy · 7 Août 2024

Phil69970 à dit:
Donne nous le résultat attendu sur ton fichier pour les exemple que tu as mis

Effectivement, avec les a, b, bis, ter, etc. ça risque de ne pas être simple.

Ceci dit, je crois que cette question a déjà été abordée sur le forum.

Banbara · 7 Août 2024

Voilà le résultat escompté:

Ci-joint le Fichier

TooFatBoy · 7 Août 2024

Si ta source est bien foutue, il suffit de couper à la première espace de code 32.
L'éventuelle espace précédente étant de code 160, si ta source est bien foutue.

Phil69970 · 7 Août 2024

Re

Une piste de réflexion ici

Et plus généralement fait une recherche sur le forum en haut à droite

mapomme · 7 Août 2024

Bonsoir Banbara et bonsoir à tous les autres ,

Une formule qui couvre un large éventail de numérotation mais certainement pas tous.

Pour la numérotation, mettre la formule suivante en C2 puis la copier vers le bas :

VB:

=SI(ESTNUM(1*GAUCHE(B2;1));SUPPRESPACE(SIERREUR(GAUCHE(B2;CHERCHE(" ";B2)*1)&SIERREUR(INDEX($H$1:$H$28;EQUIV(VRAI;ESTNUM(CHERCHE(" "&$H$1:$H$28&" ";B2));0));"");""));"")

Pour la dénomination de la voie, mettre la formule suivante en D2 puis la copier vers le bas :

VB:

=SUPPRESPACE(STXT(B2;NBCAR(C2)+1;999))

Voir le fichier joint.

nota : la formule en colonne C utilise une liste auxiliaire en colonne H des second termes possibles pour les numéros de voie (A, B, C, ..., bis, ter). Bien sûr cette liste n'est pas limitée et peut-être complétée par insertion.

Exemple de résultat :

patricktoulon · 7 Août 2024

bonsoir
comme ca vite fait je dirais en premier qu'il te sera impossimble (àmoins d'avoir une liste de sufixe de numerotation d'addresse(bis,C,etc.....)

ensuite pour palier au plus grand nombre de possibilités
on coupe la chaine par les espaces on prend le premier
et le 2d si moins de 3caractères et différent de "rue"
comme on veut le reste de l'adresse séparément on fait deux fonction siamoises l'une dépendante de l'autre

VB:

Function NumAddress(x As String) As String
    Dim T, N$
    T = Split(x, " ")
    If Val(T(0)) > 0 Then N = T(0)
    If T(1) = "bis" Or Len(T(1)) <= 3 And LCase(T(1)) <> "rue" Then N = N & " " & T(1)
    NumAddress = N
End Function

Function suitAddress(x As String) As String
    suitAddress = Replace(x, NumAddress(x), "")
End Function

les formules sont simples
=numaddress(D2)
=suitaddress(D2)

mapomme · 7 Août 2024

Re,

Il est plus intéressant de passer en tableau structuré au minimum pour la liste des termes auxiliaires.
Le tableau est nommé tsAUX. Il devient plus facile d'ajouter ou retirer des termes car ces opérations sont automatiquement reportées sur les formules qui font appel à ce tableau.

Nouvelle formule en C2 :

VB:

=SI(ESTNUM(1*GAUCHE(B2;1));SUPPRESPACE(SIERREUR(GAUCHE(B2;CHERCHE(" ";B2)*1)&SIERREUR(INDEX(tsAUX[Terme];EQUIV(VRAI;ESTNUM(CHERCHE(" "&tsAUX[Terme]&" ";B2));0));"");""));"")

mapomme · 7 Août 2024

Bonjour @patricktoulon ,

Pour parfaire le vba, il faudrait aussi traiter le cas des adresses sans numéro qui sont assez fréquentes pour les demeures isolées en campagne (pas plus de 21 secondes pour toi à le faire et encore je suis plus que généreux )

patricktoulon · 8 Août 2024

re
et si on veut pousser plus loin
on ajoute les exeption sous forme de not like inversé

Code:

Function NumAddress(x As String) As String
    Dim T, N$, exeption
    exeption = " rue  st "
    T = Split(x, " ")
    If Val(T(0)) > 0 Then N = T(0)
    If IsNumeric(N) Then
    If T(1) = "bis" Or Len(T(1)) <= 3 And Not exeption Like "* " & LCase(T(1)) & " *" Then N = N & " " & T(1)
    End If
    NumAddress = N
End Function

Function suitAddress(x As String) As String
    suitAddress = Replace(x, NumAddress(x), "")
End Function

voila tu peux ajouter autant d'exeption que tu veux il doit y avoir un espace avant et après chaque exeption
si il n'y a pas de numero le resulstat sera vide et suitaddress donnera le complet
22 secondes !

Banbara · 8 Août 2024

Bonjour,
Merci beaucoup à tous d'avoir pris le temps de me répondre. je ne manquerais pas de tout essayer suivant mes possibilités, et donner mes conclusions ici.
Cordialement à tous.
Banbara

Banbara · 8 Août 2024

mapomme à dit:
Bonjour @patricktoulon ,

Pour parfaire le vba, il faudrait aussi traiter le cas des adresses sans numéro qui sont assez fréquentes pour les demeures isolées en campagne (pas plus de 21 secondes pour toi à le faire et encore je suis plus que généreux )

Merci "Mapomme", mais le pire c'est que je n'ai pas tripoté Exel depuis plus de 30 ans, et que je n'y connais rien en VBA.

TooFatBoy · 8 Août 2024

Banbara à dit:
Merci "Mapomme", mais le pire c'est que je n'ai pas tripoté Exel depuis plus de 30 ans, et que je n'y connais rien en VBA.

C'est pour ça que la solution de mapomme utilisant un Tableau Structuré est parfaite : pas besoin de connaître VBA.

XL 2021 Séparer les adresses (Rue et N°) en 2 cellules avec un macro

Banbara

XLDnaute Nouveau

Pièces jointes

job75

Phil69970

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

TooFatBoy

XLDnaute Barbatruc

Banbara

XLDnaute Nouveau

Pièces jointes

TooFatBoy

XLDnaute Barbatruc

Phil69970

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

mapomme

XLDnaute Barbatruc

Pièces jointes

mapomme

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Banbara

XLDnaute Nouveau

Banbara

XLDnaute Nouveau

TooFatBoy

XLDnaute Barbatruc

Discussions similaires