VBA Formula for Splitting Property Address to Multiple Columns

Hello,

I have a list where the property address is in column B as follows:

# Property Address
1. 123 N Maple Dr, Los Angeles, CA 90000
2. 129 W Main St Unit 2, Los Angeles, CA 90000
3. 111 Main Ave, STE 1, Los Angeles, CA 90000

I would like to select all the records in that column and split the date to separate columns as follows:

Street #	Street Address	City	ST	Zip
123	N Maple St	Los Angeles	CA	90000
129	W Main St Unit 2	Los Angeles	CA	90000
111	Main Ave, STE 1	Los Angeles	CA	90000

Note that record 2 & 3, Unit 2 is not separated by a comma whereas record 3 is.

Then I would like the list sorted by City, Zip, Street Address, Street #.

Thank you for your help.

Are the numbers (1, 2 and 3) part of the address string?

No, "#"1,2, 3 is in column A & "Property Address" records 1,2,3 are in column B. 1,2, 3 are not part of the property address column. Thank you for asking.

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...

Code:

---

'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)

Thank you for your help!