Break up City, State Zip to three columns

[thepartydj]

I currently have in one cell:
City, State zip-exzip

I would like to make that into 3 cells.

What is an easy way to do this?


Thank You!

 

[Trevor G]

Try this, Select your column then use the Text to columns feature, step through the guide but select Delimited then you can specify which separate to use that should then separate the data.

Example shown here before

Excel Workbook
	I
1	New York, USA, Zip
2	New York, USA, Zip
3	New York, USA, Zip
4	New York, USA, Zip
5	New York, USA, Zip
6	New York, USA, Zip
Sheet1

After

Excel Workbook
	I	J	K
1	New York	USA	Zip
2	New York	USA	Zip
3	New York	USA	Zip
4	New York	USA	Zip
5	New York	USA	Zip
6	New York	USA	Zip
Sheet1

 

[thepartydj]

That will only work if there was a comma after the state, but there isn't a comma. I can't use the space delimiter either because some towns have two words. Any other suggestions?

 

[pagrender]

If your data looks like this:

<TABLE style="WIDTH: 185pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=246 border=0><COLGROUP><COL style="WIDTH: 185pt; mso-width-source: userset; mso-width-alt: 8996" width=246><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 185pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=246 height=20>buffalo NY 12345-1234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>eire PA 12345-1234</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>New London NY 12345-4568</TD></TR></TBODY></TABLE>

And if your data is in column A,

Use this in B1:=LEFT($A1,LEN($A1)-14)
Use this in C1: =MID($A1,LEN($A1)-12,2)
Use this in D1: =RIGHT($A1,10)

If your state if a different length than the two digit postal code this will not work.

 

[Ruddles]

How come you're asking the question and we're creating the test data?!

Would you like to post a representative sample of your data?

 

[kpark91]

@Ruddles, because we're so eager -_-

 

[thepartydj]

@Ruddles .... because I think that "city, state zip" is sample code enough. Thanks pagrender for helping me out ... Ruddles, you don't understand the question or can't figure it out you don't need to respond. Thanks again pagrender.

 

[Ruddles]

Sorry, I was about to offer some assistance but I felt a more complete description of the data might have been helpful before I went off in the wrong direction (as I notice one other person did).

I'm glad you've managed to find a solution.