inserer virgule

Function INSVIRGULE(txt$)
Dim numero&, nv$
With CreateObject("VBScript.RegExp")
    .Pattern = "(\d+)?(rue|avenue|boulevard)?"
    numero = .Execute(txt)(0)
    nv = numero & ", "
    INSVIRGULE = nv & Application.Proper(Mid(txt, Len(nv), Len(txt)))
End With
End Function

Sub Inserer_Virgule_COLA()
Dim c As Range, numero&, nv$
With CreateObject("VBScript.RegExp")
    .Global = True
    .Pattern = "(\d+)((-|/|)\d+)?(rue|avenue|boulevard)?"
        For Each c In Range("A1", [A56536].End(xlUp))
            numero = .Execute(c.Text)(0)
            nv = numero & ", "
            [COLOR=Red]c.value = _
            nv & Application.Proper(Mid(c.Text, Len(nv),  Len(c.Text)))[/COLOR]
            [COLOR=Blue]'c.Offset(, 1) = _
            'nv & Application.Proper(Mid(c.Text, Len(nv), Len(c.Text)))[/COLOR]
[COLOR=Green]' ligne en bleue à décommenter si on veut mettre les données
'en colonne B[/COLOR], [COLOR=Green](commenter alors la ligne en rouge )[/COLOR]
        Next c
End With
End Sub

Function GENIALO(ByVal chaine2 As String)
chaine = UCase(Trim(chaine2))
Dim Reg As Object: Set Reg = CreateObject("Vbscript.RegExp")
'virgule
Reg.Pattern = "^[0-9]+[A-Z]*( BIS| TER)*"
Set Matches = Reg.Execute(chaine)
For Each Match In Matches
    numero = Match.Value
Next Match
chaine2 = Right(chaine, Len(chaine) - Len(numero))
'majuscule
chaine2 = StrConv(chaine2, vbProperCase)
If numero <> "" Then
    chaine = numero & "," & chaine2
Else
    chaine = chaine2
End If
GENIALO = chaine
End Function