I was asked to separate city, state, and zip (contained in one column) to 3 separate columns.

Are you asking for a new method that is different from the one you are stating you already use? (I would do it without the new place holder column and use the MID command to get state out.)

Or is the solution not giving you what you expect out?

Jason

Conceptually, I would FIND for the comma and select the city from 0 character to the position of the comma. Then look for the first number and since there are no numbers in city or state, it will find zip. Then strip the length of the number field into the column desired.

The LEN and MID function can be helpful there, along with FIND or even SEARCH.

An efficient way to do this is to use this Text to Columns Automator Service (Dropbox download).

Installation takes less than a minute. Doubleclick downloaded .workflow package and if necessary give permission at System Preferences > Security & Privacy. Also make sure Automator.app and Numbers.app are listed and checked at System Preferences > Security & Privacy > Privacy > Accessibility.

To use simply select the cells with text you want to split, choose Numbers > Numbers Services > Text to Columns from the menu, answer the prompt about the separator character ( , or space or other character). Then click once in the upper left cell where you want the results to appear (usually the top body cell of the adjacent column to the right, but it could be anywhere) and command-v or Edit > Paste and Match Style to paste the values. They will appear in separate columns.

SG

If the first value is in B2, Lets put City in C, State in D and Zip in E

B2 is just the raw data

C2 =LEFT(A2,FIND(",",A2)-1)

D2 =MID(A2,FIND(",",A2)+2,2)

E2 =RIGHT(A2,5)

Jason