Split cell data

I'm trying to split the city, state and zip code into 3 different columns.

I think I have to start from the right and find the first space to get the zip code. Then find the second space from right for the state and city.

The zip code can vary in length and the city can be one or two words which is why I think the best way is the “space” delimiter and has to start from the right.

The wizard doesn't work because the spaces vary due to cities being one or two words and there are no commas.



Examples:

DELRAY BEACH FL 33445

BRONX NY 10466

NEW YORK NY 10128-5517

To be clear the city, st and zip are all in cell A1.

I want
A2 - City
A3 - State
A4 - Zip

One way.... B3:
=TRIM(SUBSTITUTE(SUBSTITUTE(A3,D3,""),C3,""))

c3:
=TRIM(RIGHT(SUBSTITUTE(TRIM(SUBSTITUTE(A3,D3,""))," ",REPT(" ",50)),50))

D3:
=TRIM(RIGHT(SUBSTITUTE(A3," ",REPT(" ",50)),50))

An alternative is to use Power Query

Please Login or Register  to view this content.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

?? Did you open the file?

Still learning this site. alansidman gets the prize!! Power Query worked perfectly. Thank you. I even customized it some. Glad you pointed out this tool.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

@MoeFoxx,

Here is another set of formulas that should work. With your text in cell A1, put these formulas in the indicated cells...

A2: =LEFT(A1,LEN(A1)-9-5*(MID(A1,LEN(A1)-5,1)<>" "))

A3: =LEFT(RIGHT(A1,8+5*(MID(A1,LEN(A1)-5,1)<>" ")),2)

A4: =RIGHT(A1,5+5*(MID(A1,LEN(A1)-5,1)<>" "))



@Glenn... just noting you placed your formulas across whereas the OP asked for them to be down the column.

Rick. That was added at the most recent (of 4) edits of the original post... after I replied. If you look at the first sence of post 1 it (currently) states "separate columns".

Moefoxx. Please don't edit posts after replies have been recieved. It causes confusion.