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.

---
Selvarathinam.

[Gord Dibben]

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


Gord Dibben MS Excel MVP

On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
<[email protected]> 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.

[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" <gorddibbATshawDOTca> wrote ...
> How about using Data>Text to Columns>Delimited by space?
>
>
> Gord Dibben MS Excel MVP
>
> On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
> <[email protected]> wrote:
> >1 NY 10023
> >
> >And I need to parse that data into two separate columns.

[Gord Dibben]

Good point David.

Thanks, Gord

On Fri, 20 Jan 2006 11:38:46 -0500, "David McRitchie"
<[email protected]> 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 Data>Text to Columns>Delimited by space?
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Thu, 19 Jan 2006 17:07:30 -0600, jawdawson
>> <[email protected]> wrote:
>> >1 NY 10023
>> >
>> >And I need to parse that data into two separate columns.
>

Gord Dibben MS Excel MVP