Autres comment extraire cp et ville d'une adresse dans excel 2007?

Sub test()
MsgBox Cp("12 rue de la paix 75000 Paris")
End Sub
Function Cp(Ad As String) As String
Dim t, i As Integer
t = Split(Ad)
For i = 0 To UBound(t)
    If IsNumeric(t(i)) And Len(t(i)) = 5 Then
        Cp = t(i)
        Exit Function
    End If
Next
End Function

Sub test()
MsgBox CC_Ville("12 rue de la paix 75000 Paris")
End Sub

Function CC_Ville(c)
Application.Volatile
  Set obj = CreateObject("vbscript.regexp")
  obj.Pattern = "\d{5}\s([A-Z,a-z,])+"
  Set a = obj.Execute(c)
  If a.Count > 0 Then CC_Ville = a(0) Else CC_Ville = ""
End Function

Sub test()

MsgBox Cp(Columns("C:C").Select)

End Sub

Function Cp(Ad As String) As String

Dim t, i As Integer

t = Split(Ad)

For i = 0 To UBound(t)

    If IsNumeric(t(i)) And Len(t(i)) = 5 Then

        Cp = t(i)

        Exit Function

    Selection.Cut Destination:=Columns("C:C")

    Columns("D:D").Select

    End If

Next

End Function

Function CDE_P_VIlle(txt As String)
    With CreateObject("Vbscript.regexp")
        .Pattern = "\d{5}\s\D+$"
        Set a = .Execute(CStr(txt))
        If a.Count > 0 Then CDE_P_VIlle = a(0) Else CDE_P_VIlle = ""
    End With
End Function

Sub test2()
    Set plage = Feuil1.Range("c1:c" & Cells(Rows.Count, "C").End(xlUp).Row)
    For Each cel In plage.Cells
        cpville = CDE_P_VIlle(cel.Value)
        cel.Offset(, 1) = cpville
        cel.Value = Split(cel.Value, cpville)(0)
    Next
End Sub

Type Adrs
    Adresse As String
    Cp As String
    ville As String
End Type

Sub test2()
Dim ad As Adrs, cel As Range
    Set plage = Feuil2.Range("c1:c" & Cells(Rows.Count, "C").End(xlUp).Row)
 
    For Each cel In plage.Cells
    ad = Cp(cel.Value)
    cel = ad.Adresse
    cel.Offset(0, 1) = ad.Cp & " " & ad.ville
    Next
End Sub
Function Cp(ad As String) As Adrs
Dim t, i As Integer
Dim ville As Boolean
t = Split(ad)
For i = UBound(t) To 0 Step -1
    If IsNumeric(t(i)) And Len(t(i)) = 5 Then
       Cp.Cp = t(i)
       ville = True
     Else
     If ville = False Then
        Cp.ville = t(i) & " " & Cp.ville
     Else
         Cp.Adresse = t(i) & " " & Cp.Adresse
     End If
    End If
Next
End Function