Separating Street Numbers from Street Addresses1 and Street addresses2 is a frustrating procedure. Even the USPS can't fully automate and has to use Humans in the process.
Even Street Numbers can be a pain since they too can have 2 parts (123 B W. Main St #13, ...)
For Street Address2, City, State, and Zip. (To handle Street Adddress2's you will need to create a list of possible Prefixes ("Unit"; ", Unit"; "Ste"; ", Ste"; "Suite"; ", Suite"; etc. Then, cycle thru the Addresses and Replace the Matching Strings in the Cells with "|Unit"; "|Ste"; "|Suite"; "|etc") After the Replacement...
'Uses Option Base 0
X = Split(AddressCell, ",")
If CBool(InStr(AddressCell, "|")) Then StreetAddress2 = Split(X(Ubound(X)-2), "|")(1)
City = X(Ubound(X)-1)
State = Split(X(Ubound(X), " ")(0)
Zip = Split(X(Ubound(X), " ")(1)
Good luck with Street number and Street Address1. Unless there is a comma between the start of Street number and the end of Street Address1, They will both be in X(0)