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 easy way to do it besides re-keying?? Of course this person needs it yesterday... Help please!

Thanks so much!

 

[Zathras]

You didn't say exactly how the city/state/zip are formatted. If the format is something like this:

A1: 'CITYSTATEZIP 
A2: 'Chicago, IL 60601

Then you could use some formulas like these:

B1: 'CITY
C1: 'STATE
D1: 'ZIP
B2: =TRIM(LEFT(A2,LEN(A2)-10))
C2: =TRIM(MID(A2,LEN(A2)-7,2))
D2: =RIGHT(A2,5)

Of course, if the zip is zip+four, or the states are spelled out or something else is wierd, you may need a different approach.

If you need more help, please post some real examples of what the data look like.

 

[schat]

amandarose80
as zathras says, a bit more info on how the cell contents are laid out would help. apart from what he has suggested, if there are spaces or some other consistently used delimiter between each city/state/zip you could try using the 'text to columns' feature on the data menu.
hth
schat

 

[DaleWatson123321]

If there's consistency to your data, such as the following example... City, NM 123456 ...then the following formulas will work. The formulas are based your city/state/zip column being A, and the first item in A2.

Enter this formula in B2:
=LEFT(A2,FIND(",",A2,1)-1)

Enter this formula in C2:
=MID(A2,FIND(",",A2,1)+2,2)

Enter this formula in D2:
=RIGHT(A2,FIND(",",A2,1)+1)

If your data lends itself to working with these formulas, then simply copy the formulas down for each row.

I hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[amandarose80]

I don't know what is going on now. Whenever I enter a formula it acts like it is taking it but when I press enter it has the formula as text in the cell. I've tried simple formulas to see if it is the formula I'm using and it doesn't take those either.

 

[xlbo]

Tools>Options>View
Untick "Formulas" Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[DaleWatson123321]

If Tools - Options - Formulas was NOT checked off, then it's likely that the cell(s) where you've entered the formulas are formatted as "Text".

To change the cells, in an entire column, or columns, click the letter(s) at the top of the column(s), then right-click and choose "Format Cells". Then choose an appropriate format... "General" will work.

Hope this helps. Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[DaleWatson123321]

I should have added... that any existing formulas will NOT automatically change after you change the formatting.

You'll have to use <Edit> <Enter> on each formula to update the formulas.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[amandarose80]

Thank you all so much!! You have saved me time and my client $$!!

You guys are all great!!