Once Cell(city, state zip) to three cells - an easy/quick way?

[amandarose80]

I have a 1216 line spreadsheet, not created by me, that needs the city, state zip column split up into three cells, for converting reasons. Is there a quick and wasy way to do it besides rekeying?? Of course this person needs it yesterday... Help please!

Thanks so much!

 

[acron]

You can use the Text To Columns option from the Data Menu, provided that you can specify a delimiter between each component (such as a comma or space). Just select th ecolumn with the data (you need to have a sufficient number of blank columns to the right to receive the new data), and use the Data, Text To Columns option.

A.C.

 

[mscallisto]

I agree with ACRON but if that don't work you could try the following:

If A1 is [Anytown,MA12345] and A2 etc follow the same rule
then create the following 3 forlulas.

B1 =MID(A1,1,SEARCH(",",A1,1)-1)
C1 =MID(A1,SEARCH(",",A1,1)+1,2)
D1 =MID(A1,LEN(A1)-4,5)

Drag the formulas down to the last row of data

B1 Will contain Anytown
C1 Will contain MA
D1 Will contain 12345

etc

 

[mscallisto]

Sorry Dale and Zathras I didn't notice that amandarose80 submitted the post twice.

Amandarose80 you need to submit a post only once then read and respond to that post.

You can click on "My Threads" at the top left of the Tek-Tips main screen to view all your posts.

Much Luck!!