Parsing data in one cell into three parts

[MeTed]

Hello,

I have a column in Excel that contains an address field formatted like this:

Redondo Beach, CA 90278

How do I parse that out to split it into the 3 respective data parts? I
need separate columns for City, State, and Zip.

TIA,
Mark

[Ken Wright]

You can either do it the text to Columns feature and then recombine the
first two columns, or, you can do the following:-

Assuming data in A1:A100, exactly as stated with a comma separating the City
and State, all States as a 2 letter abbreviation, and all zips as 5 digit
codes.

In B1 put =LEFT(A1,FIND(",",A1)-1)
In C1 put =MID(A1,FIND(",",A1)+2,2)
In D1 put =MID(A1,FIND(",",A1)+5,5)

and then copy down.

Regards
Ken...................

"MeTed" <me...@meted.com> wrote in message
news:IisD9.48$hi...@nwrddc02.gnilink.net...

[MeTed]

Works! Thanks, Ken.

"Ken Wright" <ken.w...@NOSPAMntlworld.com> wrote in message
news:e8wns$jkCHA.2432@tkmsftngp10...

[Ken Wright]

My pleasure - Should have said, don't forget to copy all and then paste
special as values when done.

Regards
Ken.....................

"MeTed" <me...@meted.com> wrote in message

news:4EsD9.42$mL...@nwrddc01.gnilink.net...

[J.E. McGimpsey]

Or you could use Text to Columns twice - splitting the first time
delimited on a comma, and then split the second column on a space.

In article <e8wns$jkCHA.2432@tkmsftngp10>, Ken Wright

[Ken Wright]

Now that's neat - hadn't thought of using it twice - Cheers

Regards
Ken...................

"J.E. McGimpsey" <jemcg...@mvps.org> wrote in message
news:221120020954190054%jemcg...@mvps.org...