Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

[nemounet11]

Bonjour à tous.
Je suis sapeur pompier professionnel, responsable d'un service cartographie sous SIG (GeoConcept).
J'ai crée sur ma commune 4800 linéaire qui représentent le réseau routier.
Pour chacun d'eux, je remplis un champs avec le nom de la rue.
J'ai ensuite exporté l'ensemble du réseau routier.
Je l'ai importé sur excel et j'ai déjà crée une chaîne de formule (colonnes cachées C à AD) pour avoir le nom abrégé de la rue.
Ceci afin de pouvoir géré l'affichage de celle-ci sur le plan selon l'échelle.
J'ai finit tout ce travail mais désormais, je dois créer l'index du plan.
Pour ceci, j'ai besoin de votre aide.
J'ai donc mes noms de rue sous la forme : Allée Ambrosia; Rue d'Amstersdam; Rue Charas.
Et je souhaiterais faire mon index sous la forme : Ambrosia (Allée); Amsterdam (Rue d'); Charas (Rue).
Ceci afin de permettre une recherche plus rapide pour départs opérationnels.
Merci d'essayer de m'apporter une solution qui m'évitera ainsi de devoir refaire toute la saisie sous excel à la main, rue par rue.
Et encore bravo pour tout ce que les utilisateurs du forum m'apportent à chacune de mes recherches.

 

[JCGL]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous,
Bienvenue sur XLD,

Utilise Données / Convertir avec le séparateur Espace

Et une petite formule : =AI2&" "&AJ2&" "&AK2&" "&AL2&" "&AM2&"("&AH2&")"

A + à tous

 

[nemounet11]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Merci JCGL.
C'est vraiment pas mal mais vu la réponse rapide que vous m'avez apporté, vous serait-il possible d'approfondir votre solution.
En effet, le résultat que j'attends n'est pas tout à fait celui là.
Il est super mais j'aimerais que AVENUE DE PROVENCE devienne PROVENCE (AVENUE DE) et non pas DE PROVENCE (AVENUE).
Croyez-vous pouvoir m'apporter une autre solution.
Et encore MERCI à vous.
Cordialement

 

[Modeste geedee]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonsour® nemounet11, JCGL

pourquoi pas une petite fonction personnalisée :

Function Renverse(Target As Range) As String[INDENT]Renverse = StrReverse(Target)[/INDENT]
End Function

puis en AG6
la formule :

=DROITE(C6;TROUVE(CAR(32);Renverse(C6))-1)& " (" &GAUCHE(C6;TROUVE(DROITE(C6;TROUVE(CAR(32);Renverse(C6))-1);C6)-1)&")"

il restera bien quelques anomalies telles que :
D'ARMISSAN (ROUTE)
1945 (PLACE DU 6 JUIN )

mais comme ils disent à la TV
cà c'était avant... qu'il fallait y penser selon le precepte M**** In, M**** Out

on pourra toujours reprndre les saisies mais à 10 sec par entrée (4800 entrées)
il y en pour environ 12heures de boulot pour ajouter un "_" avant la partie clé souhaitée
y'a pas le feu au lac ...


on pourra même avantageusement réutiliser la fonction perso pour regénérer la saisie...
GAUCHE(C6;TROUVE(DROITE(C6;TROUVE(CAR(32);Renverse(C6))-1);C6)-1) &"_"& DROITE(C6;TROUVE(CAR(32);Renverse(C6))-1)

 

[ROGER2327]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous

Un essai...​

ROGER2327
#6014

Dimanche 15 Gidouille 139 (Sainte Giborgne, vénérable - fête Suprême Tierce)
11 Messidor An CCXX, 0,8792h - coriandre
2012-W26-5T02:06:36Z

 

[nemounet11]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Merci pour vos réponse JCGL, modeste geedee et ROGER2327.
Cela va beaucoup m'aider et surtout me faire gagner énormément de temps.
Par contre, ROGER2327, j'ai téléchargé le fichier que vous avez modifié, cela fonctionne très bien mais comment dois-je faire pour que cela fonctionne sur une liste de 4800 noms de rue, voire même sur une liste de rue départementale (environ 80000 noms de rue).
Je suppose qu'il faut modifier le Virtual Basic mais je n'y connais pas grand chose.
Pourriez-vous donc me refaire le fichier avec le Virtual Basic qui fonctionne jusqu'à 80000 noms de rue.
Et juste en quelques mots m'expliquer comment cela fonctionne car je suis assez curieux.
Ensuite, comment dois-je faire si je veux rajouter des types de voie dans l'onglet paramètres pour qu'ils soient pris en compte par le Virtual Basic (exemple : passage, passerelle, écluse, etc.....)

Merci d'avance pour vos réponses.

Cordialement

 

[ROGER2327]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Re...

(...) ROGER2327, j'ai téléchargé le fichier que vous avez modifié, cela fonctionne très bien mais comment dois-je faire pour que cela fonctionne sur une liste de 4800 noms de rue, voire même sur une liste de rue départementale (environ 80000 noms de rue). (...)

Il va de soi que le code s'adapte de lui-même aux longueur des listes à traiter. Si vous ajoutez quelques items dans la colonne B de la feuille Calcul, il vous suffit d'exécuter la procédure tata pour mettre à jour les colonnes AI et AJ.

Vous pouvez de même étendre les données de la feuille Paramètres : l'exécution de la procédure toto mettra cette feuille à jour.

Les seules modifications du code à faire sont celles concernant l'emplacement des données et des résultats : si vos données se trouve ailleurs qu'en colonne B, ou commence ailleurs qu'en B5, une modification du code est nécessaire.

Par ailleurs, la modifications de la feuille Paramètres requiert quelques précautions. Notamment, l'ordre des items des colonnes B et C est important.

Pour l'instant, le code est rudimentaire. Comme il semble vous intéresser, je vais le réviser et je reviendrai avec une version plus sérieuse, documentée pour que vous puissiez l'adapter facilement.​

À plus tard,



ROGER2327
#6015

Dimanche 15 Gidouille 139 (Sainte Giborgne, vénérable - fête Suprême Tierce)
11 Messidor An CCXX, 3,6411h - coriandre
2012-W26-5T08:44:19Z

 

[Modeste geedee]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonsour®
bravo Roger... ça sent le vécu .

Merci pour vos réponse JCGL, modeste geedee et ROGER2327.
Cela va beaucoup m'aider et surtout me faire gagner énormément de temps.
Par contre, ROGER2327, j'ai téléchargé le fichier que vous avez modifié, cela fonctionne très bien mais comment dois-je faire pour que cela fonctionne sur une liste de 4800 noms de rue, voire même sur une liste de rue départementale (environ 80000 noms de rue).

simplement importe ou recopie ta liste en colonne B
la macro calcule elle-même la plage à traiter

With Range("B5")
        a = Range(.Cells, Cells(Rows.Count, .Column).End(xlUp)).Value
    End With

Ensuite, comment dois-je faire si je veux rajouter des types de voie dans l'onglet paramètres pour qu'ils soient pris en compte par le Virtual Basic (exemple : passage, passerelle, écluse, etc.....)

dans la feuille parametres :
simplement ajouter en Colonne A les types que tu souhaite
il peut etre utile aussi de re-trier la colonne A

puis exéecuter la macro "toto" pour générer les variations

 

[JBOBO]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous,

Une formule à tester eventuellement :

=DROITE(B6;NBCAR(B6)-SIERREUR(SIERREUR(SIERREUR(TROUVE(" DE ";B6;1)+3;TROUVE(" DU ";B6;1)+3);TROUVE(" D'";B6;1)+2);TROUVE(" ";B6;1)))&" "&"("&GAUCHE(B6;NBCAR(B6)-NBCAR(DROITE(B6;NBCAR(B6)-SIERREUR(SIERREUR(SIERREUR(TROUVE(" DE ";B6;1)+3;TROUVE(" DU ";B6;1)+3);TROUVE(" D'";B6;1)+2);TROUVE(" ";B6;1)))))&")"

 

[david84]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous,
un 1er essai pour voir avec une fonction personnalisée utilisant une expression rationnelle :

Function Glossaire(c As String) As String
Dim oRegExp As Object, matches As Object
Set oRegExp = CreateObject("vbscript.regexp")
With oRegExp
    .Global = True
    .Pattern = ".*((?:AVENUE|BOULEVARD|COURS|PLACE|PROMENADE|RUE|ROUTE|CARREFOUR|CHEMIN|ESPLANADE|IMPASSE|QUAI|RUELLE|ROND-POINT|VOIE|Z.A.C.|Z.I.)\s+(?:AUX |D'|DE (?:LA |L')?|DES )?)(.*)"
    If .test(c) = True Then Glossaire = .Replace(c, "$2 ($1)")
End With
Set oRegExp = Nothing: Set matches = Nothing
End Function

A+
Pour l'utiliser :

=Glossaire(A1)

(A1 étant la cellule comportant la chaîne à traiter).
A+

 

[ROGER2327]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous

Il commence à y avoir pas mal de pistes : ça devient intéressant...

Merci à Modeste geedee qui a commencé à commenter ma proposition.

Voici une version plus achevée.
L'ensemble des procédures et fonctions est dans le module ROGER1.​

Sub tata()
Dim i&, j&, tmp$, a(), b()
P1: With Feuil2.Range("A1")
        b = .Parent.Range(.Offset(0, 2).Cells, .Parent.Cells(.Parent.Rows.Count, .Offset(0, 2).Column).End(xlUp).Offset(1)).Value
    End With
    b(UBound(b), 1) = " "
P2: With Feuil1.Range("B5")
        a = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)).Value
    End With
    ReDim Preserve a(1 To UBound(a), 1 To 2)
    For i = 2 To UBound(a)
        tmp = UCase(WorksheetFunction.Trim(a(i, 1)))
        For j = 1 To UBound(b)
            If InStr(1, tmp, UCase(b(j, 1))) Then
                a(i, 2) = Right$(tmp, Len(tmp) - Len(b(j, 1))) & " (" & UCase(Trim(b(j, 1))) & ")"
                Exit For
            End If
        Next
    Next
P3: With Feuil1.Range("AI5")
        .Resize(UBound(a), 2).Value = a
    End With
End Sub

Sub toto()
Dim i&, j&, k&, tmp$, a(), b()
P1: With Feuil2.Range("A1")
        a = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)).Value
        With .Offset(0, 1)
            b = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp).Offset(1)).Value
        End With
        b(UBound(b), 1) = " "
        With Application: .ScreenUpdating = 0: .EnableEvents = 0: .Calculation = -4135: End With
        With .Offset(0, 2)
            .Parent.Columns(.Column).Resize(.Parent.Rows.Count - .Row + 1, 1).Offset(.Row - 1).ClearContents
            For i = 1 To UBound(a)
                tmp = a(i, 1)
                For j = 1 To UBound(b)
                    .Offset(k).Value = tmp & b(j, 1)
                    k = k + 1
                Next
            Next
        End With
        With Application: .Calculation = -4105: .EnableEvents = 1: .ScreenUpdating = 1: End With
    End With
End Sub

Fonctionnement :
L'onglet Paramètres (feuille Feuil2 dans le classeur joint) doit être complété dans les colonnes A et B. En colonne A, les références aux types de voies, en colonne B les compléments possibles à ces références. Attention aux espaces dans cette dernière colonne ! L'ordre des données ne semble pas avoir beaucoup d'importance.
On peut augmenter à volonté les données dans chacune des colonnes. (On évitera évidemment les doublons et les lignes vides !)
Lorsque les paramètres sont saisis, l'exécution de la procédure toto donne toutes les combinaisons possibles des références et de leurs complément dans la colonne C. Cette colonne sera utilisée pour le traitement des données de l'onglet Calcul.

Pour traiter les données de l'onglet Calcul (feuille Feuil1 dans le classeur joint), il suffit d'exécuter la procédure tata.

Réglage du code :
En l'état, la procédure tata traite la plageB5:Bnnn où nnn est le numéro de la dernière ligne non vide de la colonne B, et renvoie un tableau à deux colonnes (données originales, données traitées) à partir de la cellule AI5 de la même feuille.
On peut modifier ces deux références en modifiant les lignes étiquetées P2 et P3 dans le code tata.

Par exemple, supposons qu'on veuille récupérer les données traitées dans un autre onglet nommé Résultat, à partir de la cellule D8. Il faudra alors remplacer​

P3: With Feuil1.Range("AI5")

par​

P3: With Sheets("Résultat").Range("D8")

De même, si les paramètres ne sont pas saisis à partir de la cellule A1, il faudra modifier en conséquence les lignes étiquetées P1 des procédures tata et toto.

Ce sont pour l'instant les seules modifications prévues. J'espère que ce n'est pas trop compliqué...


Ceci dit, on peut se libérer de la contrainte du traitement préalable des paramètres et du remplissage de la colonne C de l'onglet Paramètres. En exécutant tutu avec le code suivant​

Sub tutu()
Dim i&, j&, tmp$, a(), b()
P1: b = paramètres(Feuil2.Range("A1"))
P2: With Feuil1.Range("B5")
        a = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)).Value
    End With
    ReDim Preserve a(1 To UBound(a), 1 To 2)
    For i = 2 To UBound(a)
        tmp = UCase(WorksheetFunction.Trim(a(i, 1)))
        For j = 1 To UBound(b)
            If InStr(1, tmp, UCase(b(j))) Then
                a(i, 2) = Right$(tmp, Len(tmp) - Len(b(j))) & " (" & UCase(Trim(b(j))) & ")"
                Exit For
            End If
        Next
    Next
P3: With Feuil1.Range("AI5")
        .Resize(UBound(a), 2).Value = a
    End With
End Sub

Private Function paramètres(RData As Range)
Dim i&, j&, k&, tmp$, a(), b(), c()
    With RData
        a = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp)).Value
        With .Offset(0, 1)
            b = .Parent.Range(.Cells, .Parent.Cells(.Parent.Rows.Count, .Column).End(xlUp).Offset(1)).Value
        End With
        b(UBound(b), 1) = " "
        ReDim c(1 To UBound(a) * UBound(b))
        With .Offset(0, 2)
            For i = 1 To UBound(a)
                tmp = a(i, 1)
                For j = 1 To UBound(b)
                    k = k + 1
                    c(k) = tmp & b(j, 1)
                Next
            Next
        End With
    End With
    paramètres = c
End Function

Réglage selon le même principe que précédemment.​

Terminé.
Bon courage.



ROGER2327
#6016

Dimanche 15 Gidouille 139 (Sainte Giborgne, vénérable - fête Suprême Tierce)
11 Messidor An CCXX, 6,6551h - coriandre
2012-W26-5T15:58:20Z

 

[david84]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonsoir,
Merci Roger pour ce fichier.
Ci-joint la fonction modifiée en fonction des nouveautés proposées.
Actuellement, les chaînes en majuscules et en minuscule sont ramenées dans la casse d'origine mais on peut bien entendu modifier cela.

Function Glossaire(c As String) As String
Dim oRegExp As New RegExp
c = Trim(c)
With oRegExp
    .Global = True
    .IgnoreCase = True
    .Pattern = "\s{2,}"
    If .Test(c) = True Then c = .Replace(c, " ")
    .Pattern = ".*((?:AVENUE|BOULEVARD|COURS|PASSAGE|PLACE|PROMENADE|RUE|ROUTE|CARREFOUR|CHEMIN|ESPLANADE|IMPASSE|QUAI|RUELLE|ROND-POINT|VOIE|Z.A.C.|Z.I.)\s+(?:AU |AUX |À (?:L')?|D'|DU |DE (?:LA |L')?|DES )?)(.*)"
    If .Test(c) = True Then Glossaire = Replace(.Replace(c, "$2 ($1)"), " )", ")")
End With
Set oRegExp = Nothing
End Function

A+

 

[ROGER2327]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour à tous

Nouvelles versions corrigées de quelques erreurs dans le classeur joint. Extension de la liste des paramètres.
Le principe reste le même, le mode d'emploi est inchangé.

J'ai intégré la nouvelle version de la fonction de david84 (que je salue), mais, ne maîtrisant pas les expressions régulières, je ne sais pas y intégrer les nouveaux paramètres (autoroute, pont, ...) : david84, si vous lisez ces lignes...

À nemounet11 : que pensez-vous de ces différentes méthodes ? Avez-vous trouvé votre bonheur ?​

ROGER2327
#6018

Lundi 16 Gidouille 139 (Saint Inventaire, poète - fête Suprême Quarte)
12 Messidor An CCXX, 0,4842h - artichaut
2012-W26-6T01:09:44Z

 

[david84]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonjour,

J'ai intégré la nouvelle version de la fonction de david84 (que je salue), mais, ne maîtrisant pas les expressions régulières, je ne sais pas y intégrer les nouveaux paramètres (autoroute, pont, ...) : david84, si vous lisez ces lignes...

Je vous lis avec intérêt Roger (si vous désirez des informations sur les expressions régulières, je suis à votre disposition bien entendu) !
Ci-joint une version modifiée :

Function Glossaire(c As String) As String 'Version 3
Dim oRegExp As New RegExp
c = Trim(c)
With oRegExp
    .Global = True
    .IgnoreCase = True
    .Pattern = "\s{2,}"
    If .Test(c) = True Then c = .Replace(c, " ")
    .Pattern = ".*\b((?:AVENUE|BOULEVARD|COURS|PASSAGE|PLACE|PROMENADE|RUE|(?:AUTO)?ROUTE|CARREFOUR|CHEMIN|ESPLANADE|IMPASSE|PONT|QUAI|RUELLE|ROND(?:-|\s)POINT|VOIE|Z.A.C.|Z.I.)\s+(?:AU |AUX |À (?:L')?|D'|DU |DE (?:LA |L')?|DES )?)(.*)"
    If .Test(c) = True Then Glossaire = Replace(.Replace(c, "$2 ($1)"), " )", ")")
End With
Set oRegExp = Nothing
End Function

À nemounet11 : que pensez-vous de ces différentes méthodes ? Avez-vous trouvé votre bonheur ?

Oui au fait, où est nemounet11 ?
A+

 

[Modeste geedee]

Re : Gestion automatique du nom des rues écrit sous la forme : Cauquène (allée de).

Bonsour®

Bonjour,

Je vous lis avec intérêt Roger (si vous désirez des informations sur les expressions régulières, je suis à votre disposition bien entendu) !
A+

https://www.excel-downloads.com/threads/vba-expressions-regulieres-patterns-pour-regexp.152183/