Separate full address into street address city state zip country in excel

Question

I want to divide my collection of more than 47K full addresses from various nations into address, city, state, zip code, and country categories. I attempted numerous approaches but was unable to come up with a formula because the structure and pattern of these addresses varies. Note: I am not very familiar with Excel VBA or macros.

Answer 1

This macro contains the functions Split() and IsNumeric(), it's all you need:

Sub test()
Dim A, B As Integer
T = Split("1, 2, X", ",")
If IsNumeric(T(0)) Then A = T(0) Else A = -1
If IsNumeric(T(2)) Then B = T(2) Else B = -1

MsgBox "Result : A=[" & CStr(A) & "], B=[" & CStr(B) & "]"
End Sub