Sub test()
Dim c As Range, t As Integer, vVoies As Variant
vVoies = Array("BD", "RUE", "IMPASSE", "CHEMIN", "AVENUE")
Set c = [A2]
Do While c <> ""
For t = LBound(vVoies) To UBound(vVoies)
Rép c, vVoies(t)
Next t
Set c = c(2, 1)
Loop
End Sub
Sub Rép(cc As Range, Voie As Variant)
If InStr(cc, " " & Voie & " DE LA ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DE LA ") + Len(Voie) + 8) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " de la)"
ElseIf InStr(cc, " " & Voie & " DES ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DES ") + Len(Voie) + 6) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " des)"
ElseIf InStr(cc, " " & Voie & " DU ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DU ") + Len(Voie) + 5) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " du)"
ElseIf InStr(cc, " " & Voie & " DE ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " DE ") + Len(Voie) + 5) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " de)"
ElseIf InStr(cc, " " & Voie & " D ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " D ") + Len(Voie) + 4) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & " d')"
ElseIf InStr(cc, " " & Voie & " ") > 0 Then
cc(1, 2) = Mid(cc, InStr(cc, " " & Voie & " ") + Len(Voie) + 2) & " (" & IIf(Voie = "BD", "Boulevard", Application.Proper(Voie)) & ")"
End If
If cc(1, 2) = "" Then cc(1, 2) = cc
End Sub