Excel 2007 : Separate Combined Numbers and Text

**bw1** · 01-06-2010, 05:47 PM

I am not the best at Excel formula's or macro's but really need some help with this issue. I have a spreadsheet where state abbreviations and postal codes have been combined with no spaces. They are located in cell "J". I need to figure out how to move the zip codes from cell J into K on their own so that only states are in cell J and only zip codes are in K. The number of characters isn't the same - some have 5 digit zip codes, some have 9 digit zip codes.

Sample data:
CA92123
CA939212402

Result needed
J = CA
K = 92123

Any help would be greatly appreciated.

**NBVC** · 01-06-2010, 06:02 PM

If the states are always, 2 chars...

then in another column, say column L2.... =Left(J2,2)

in in M2, =Substitute(J2,L2,"")

both copied down

Then you can copy these 2 columns, go to J1 and Edit|Paste Special >> Values over top...

Then delete the remaining formula column.

**Paul** · 01-06-2010, 06:03 PM

Hi BW1, and welcome to the forum. If the format of the cells is always the two-digit state abbreviation followed by either a 5 or 9-digit zip code, then use the following formulas:

State: =LEFT(J1,2)
Zip: =TRIM(MID(J1,3,255))

Hope that helps!