Parse ST-ZIP Cell (15,427 times...)

[jawdawson]

Thank you all for earlier help. I am now parsing a column of 15,427
records containing combined State and Zip Code data, IE:

A
1 NY 10023

And I need to parse that data into two separate columns.

I was using the Macro funtion earlier for some successful endeavors,
but the tiny Macro Toolbar "Stop Recording / Reference" has
disappeared.

1) Can anyone help me with the trick to that bloody toolbar, and
2) Is a Macro the best way to accomplish such a parsing...

(With one empty new column to the right, I was going to go into cell
edit mode, Cut the Zip, delete the three spaces to the R of the State,
Paste the Zip in the new Column to the right, and decend one Row... or
something to that effect).

Thank you so much, folks.

 

[Selvarathinam]

Put the following formula in the column B & C, they will provide u the
state in the column B & zip in the column without any spaces
B C
=TRIM(LEFT(A1,2)) & =TRIM(RIGHT(A1,5))
NY 10023

Hope the above solution will help u.

 

[Gord Dibben]

How about using Data>Text to Columns>Delimited by space?


Gord Dibben MS Excel MVP

 

[jawdawson]

Both suggestions helpful - thank you. Text to Columns was magic. Thank
you both.

 

[David McRitchie]

If you use Data, Text to columns
be sure to specify within the Text to columns
that the second column is text. Formatting the
column as text beforehand is not good enough.

Many east coast zip codes have a leading zero.

 

[Gord Dibben]

Good point David.

Thanks, Gord

If you use Data, Text to columns
be sure to specify within the Text to columns
that the second column is text. Formatting the
column as text beforehand is not good enough.

Many east coast zip codes have a leading zero.

Gord Dibben MS Excel MVP