Application.DisplayAlerts = False
A = Range("c2").CurrentRegion
Set reg = CreateObject("VBScript.RegExp")
For i = 2 To UBound(A)
temp = A(i, 1) & " " & A(i, 2)
A(i, 1) = getAddress(A(i, 1))
A(i, 2) = getTel(temp)
Next i
With Range("c21")
.Resize(UBound(A), UBound(A, 2)) = A
.Offset(1, 1).Resize(UBound(A)).TextToColumns Space:=True
End With
End Sub
Function getTel(str) As String
Dim matchs As Object, match As Object, temp As String
With reg
.Global = True
.Pattern = "1\d{10}"
Set matchs = .Execute(str)
For Each match In matchs
temp = temp & " " & match.Value
Next
getTel = Mid(temp, 2)
End With
End Function
Function getAddress(str) As String
With reg
.Global = True
.Pattern = "1\d{10}"
getAddress = .Replace(str, "")
End With
End Function