Separating address components

[mikeb7500]

I have cells with the following addresses, and I'd like to split/separate data into individual columns.

The cells currently look like this: (3 different cells)

3352 RED ASH CIR OVIEDO, GA 23457
1655 COPPERLEAF CV ORLANDO, OH 88348
2345 DEEP OAK ST JACKSONVILLE, MT 38293

I need them to be separated into their own columns like this:

Address(Number and Street Name) City State ZIP


I have tried using the RIGHT, LEFT, LEN, FIND functions (with no luck), but my problem is the addresses have a different number of spaces in the street addresses, as well as the Cities have a different number of characters. I'm missing something somewhere. I used Power Query to get it this far, but need some help please. I have 14,000 of these that must be done for mail merge capability. Thanks!

 

[ndsutherland]

This macro should get you close. remember to test this on a copy of your data, not the original. Let me know if you need help applying this.

Sub StreetCityStateZip()
Dim nws As Worksheet, rng As Range, r As Range
Dim Street As String, City As String, State As String, zip As Long, rvalue() As String


Set rng = Application.InputBox("Select the range of addresses", , , , , , , 8)
Set nws = Sheets.Add
For Each r In rng
    rvalue = Split(r.Value, " ")
    zip = CLng(rvalue(UBound(rvalue)))
    State = rvalue(UBound(rvalue) - 1)
    City = Left(rvalue(UBound(rvalue) - 2), Len(rvalue(UBound(rvalue) - 2)) - 1)
    Street = Left(r.Value, Len(r.Value) - Len(zip) - Len(State) - Len(City) - 5)
    nws.Range(r.Address) = Street
    nws.Range(r.Address).Offset(, 1) = City
    nws.Range(r.Address).Offset(, 2) = State
    nws.Range(r.Address).Offset(, 3) = zip
Next r
nws.Columns.AutoFit
End Sub

 

[mikeb7500]

Thanks ND...will be in touch.

 

[mikeb7500]

Will need your help applying...I placed the spreadsheet in Dropbox.

https://www.dropbox.com/s/d872lc4ksm2tsqe/TestSubdivisionsSplitColumns081315.xlsx?dl=0

 

[ndsutherland]

How is this?

https://www.dropbox.com/s/chubelwci7a9kvs/TestSubdivisionsSplitColumns081315.xlsm?dl=0

 

[mikeb7500]

It's excellent...on first glance it's EXACTLY what I need!

A couple questions for you ND. If I want to change a City or Zip Code, do I just change it in the original Address Column, and will it change in it's individual column automatically? If not, how do I do that?

Also, since this is just a "test" spreadsheet, when I compile the real one...I would imagine I complete the Power Query, and then COPY and PASTE your code into the new Spreadsheet, and it will all work? Thanks, I appreciate ALL your help!