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

Function Glossaire2(c As String) As String
Dim oRegExp As RegExp
Set oRegExp = New RegExp
c = Trim(c)
With oRegExp
    .Global = True
    .IgnoreCase = True
    
    .Pattern = "(^|.*\b)((?:(?:ALL(?:É|E)E|ARCADE|AVENUE|BAIE|BALCON|BOULEVARD|BUTTE|CARREFOUR|CENTRE(?: DE FORMATION)?|" _
    & "CHAUSS(?:É|E)E|CHEMIN(?: COMMUNAL| RURAL| PRIV(?:É|E))?|CIT(?:É|E)|(?:EN)?CLOS|COUR|DOM(?:.|AINE)|(?:É|E)CLUSE|" _
    & "ESPACE|ESPLANADE|GALERIE|GRILLE|HAMEAU|IMPASSE|JARDIN|LIEU(?:-|\s)DIT|LOT(?:.|ISSEMENT)|MAISON|MONT(?:É|E)E|PARVIS|" _
    & "PASS(?:AGE|ERELLE)|P(?:É|E)RISTYLE|PLACE(?:TTE)?|PONT|PROMENADE|QUAI|ROND(?:(?:-|\s)POINT)?|PARC|PLAN|PORT(?:E|IQUE)?|" _
    & "R(?:É|E)SIDENCE|(?:AUTO)?ROUTE|RUE(?:LLE)?|SENT(?:E|IER)|SQUARE|TERRASSE|VILLA|VOIE|Z.?A.?C?.?|Z.?I.?|Z.?U.?P?.?)(?:S|X)?)" _
    & "\s+(?:AUX? |(?:À|A) (?:L')?|D'|DU |LA |LES? |DES? (?:LA |L')?)?)(.*)"
    
    If .Test(c) = True Then c = Replace(Replace(.Replace(c, "$3 ($1 $2)"), " ( ", " ("), " )", ")")
    
    .Pattern = "\s{2,}"
    If .Test(c) = True Then c = .Replace(c, " ")

End With
Glossaire2 = Trim(c)
Set oRegExp = Nothing
End Function

Function Nemounet11(c As String) As String
Dim oRegExp As RegExp
Set oRegExp = New RegExp
c = Trim(c)
With oRegExp
    .Global = True
    .IgnoreCase = True
    
    .Pattern = "(^|.*\b)((?:(?:ALL(?:É|E)E|ARCADE|AVENUE|BAIE|BALCON|BOULEVARD|BUTTE|CARREFOUR|CENTRE(?: DE FORMATION)?|" _
    & "CHAUSS(?:É|E)E|CHEMIN(?: COMMUNAL| RURAL| PRIV(?:É|E))?|CIT(?:É|E)|(?:EN)?CLOS|COUR|DOM(?:.|AINE)|(?:É|E)CLUSE|" _
    & "ESPACE|ESPLANADE|GALERIE|GRILLE|HAMEAU|IMPASSE|JARDIN|LIEU(?:-|\s)DIT|LOT(?:.|ISSEMENT)|MAS|MAISON|MONT(?:É|E)E|PARVIS|" _
    & "PASS(?:AGE|ERELLE)|P(?:É|E)RISTYLE|PLACE(?:TTE)?|PONT|PROMENADE|QUAI|ROND(?:(?:-|\s)POINT)?|PARC|PLAN|PORT(?:E|IQUE)?|" _
    & "R(?:É|E)SIDENCE|(?:AUTO)?ROUTE|RUE(?:LLE)?|SENT(?:E|IER)|SQUARE|TERRASSE|VILLA|VOIE|Z.?A.?C?.?|Z.?I.?|Z.?U.?P?.?)(?:S|X)?)" _
    & "\s+(?:AUX? |(?:À|A) (?:L')?|D'|DU |LA |LES? |DES? (?:LA |L')?)?)(.*)"
    
    If .Test(c) = True Then c = Replace(Replace(.Replace(c, "$3 ($1 $2)"), " ( ", " ("), " )", ")")
    
    .Pattern = "\s{2,}"
    If .Test(c) = True Then c = .Replace(c, " ")
    
    .Pattern = "^(les?|la)\s([ A-Z0-9]+)" 'traitement de "la corniche" ou "la corniche 11"
    If .Test(c) = True Then c = .Replace(c, "$2 ($1)")
    
End With
Nemounet11 = Trim(c)
Set oRegExp = Nothing
End Function

Option Explicit
'Fonction de David84 pour Excel Downloads le 12/07/2012
'http://www.excel-downloads.com/forum/187569-gestion-automatique-du-nom-des-rues-ecrit-sous-la-forme-cauquene-allee-de.html
'Cocher la référence Microsoft VBScript Regular Expressions 5.5 dans Outils=>Références
'ou utiliser un Late Binding pour déclarer l'objet :
'Dim oRegExp as Object à la place de Dim oRegExp As RegExp
'Set oRegExp=createobject("vbscript.regexp") à la place de Set oRegExp = New RegExp

Function Nom_de_voie(c As String) As String
Dim oRegExp As RegExp
Set oRegExp = New RegExp
c = Trim(c)
With oRegExp
    .Global = True 'précise si la recherche porte sur la première occurence ou sur toutes
    .IgnoreCase = True 'précise si la recherche est sensible ou non à la casse (majuscules/minuscules)
    
    .Pattern = "\s{2,}" 'épurage des espaces supérieures à 1 caractère
    If .Test(c) = True Then c = .Replace(c, " ")
    
    'Traitement de la voie
    .Pattern = "(^|.*\b)((?:ALL(?:É|E)ES?|ARCADES?|AVENUES?|BAIES?|BALCONS?|BOULEVARDS?|BUTTES?|CARREFOURS?|" _
    & "CENTRES?(?: DE FORMATIONS?)?|CHAUSS(?:É|E)ES?|CHEMINS?(?: COMMUNA(?:L|UX)| RURA(?:L|UX)| PRIV(?:É|E)S?)?|" _
    & "CIT(?:É|E)S?|(?:EN)?CLOS|COURS?|DOM(?:.|AINES?)|(?:É|E)CLUSES?|ESPACES?|ESPLANADES?|GALERIES?|GRILLES?|" _
    & "HAMEAUX?|IMPASSES?|JARDINS?|LIEUX?(?:-|\s)DITS?|LOT(?:.|ISSEMENTS?)|MA(?:S|ISONS?)|MONT(?:É|E)ES?|PARVIS|" _
    & "PASS(?:AGE|ERELLE)S?|P(?:É|E)RISTYLES?|PLACE(?:TTE)?S?|PONTS?|PROMENADES?|QUAIS?|RONDS?(?:(?:-|\s)POINTS?)?|" _
    & "PARCS?|PLANS?|PORT(?:E|IQUE)?S?|R(?:É|E)SIDENCES?|(?:AUTO)?ROUTES?|RUE(?:LLE)?S?|SENT(?:E|IER)S?|SQUARES?|" _
    & "TERRASSES?|VILLAS?|VOIES?|Z.?A.?C?.?|Z.?I.?|Z.?U.?P?.?)" _
    & "\s(?:AUX? |(?:À|A) (?:L')?|D'|DU |LA |LES? |DES? (?:LA |L')?)?)(.*)"
    
    If .Test(c) = True Then Nom_de_voie = Replace(.Replace(c, "$3 ($1$2)"), " )", ")")
End With
Set oRegExp = Nothing
End Function