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

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.


--
jawdawson
------------------------------------------------------------------------
jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648
View this thread: http://www.excelforum.com/showthread...hreadid=503121

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

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.

---
Selvarathinam.

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

How about using DataText to ColumnsDelimited by space?


Gord Dibben MS Excel MVP

On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
wrote:


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.

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

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


--
jawdawson
------------------------------------------------------------------------
jawdawson's Profile: http://www.excelforum.com/member.php...o&userid=30648
View this thread: http://www.excelforum.com/showthread...hreadid=503121

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

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" <gorddibbATshawDOTca wrote ...
How about using DataText to ColumnsDelimited by space?


Gord Dibben MS Excel MVP

On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
wrote:
1 NY 10023

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

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

Good point David.

Thanks, Gord

On Fri, 20 Jan 2006 11:38:46 -0500, "David McRitchie"
wrote:

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" <gorddibbATshawDOTca wrote ...
How about using DataText to ColumnsDelimited by space?


Gord Dibben MS Excel MVP

On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
wrote:
1 NY 10023

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


Gord Dibben MS Excel MVP