City State Zip

[Guest]

When I use Text to Columns this separates fine until it gets to Oklahoma
City, Fort Smith, San Diego, Little Rock, etc. Then I get a separate column
for City, Smith, Diego, Rock. Raw data had City State and Zip all separated
by Spaces, no commas or tabs. The database is too long to do all of these
manually. Please Help Soon??!

Need this:
Little Rock AR 72207

to be:
|Little Rock|AR|72207|

Not:
|Little|Rock|AR|72207

 

[Bob I]

Throw it all in one column and then parse it. The city can be had by
subtracting the State and Zip or Len-9 and you can take the right 5 for
the Zip. As for the State use the starting point of LEN-8 and take 2 chars.

 

[Guest]

Sorry, Bob I - I don't speak Greek...Can you translate please? I don't know
parses and len-8s and 9s. Thanks

 

[Bob I]

Ok, put the string Little Rock AR 72207 in A1
now in B1 put
=LEFT(A1,LEN(A1)-9)
in C1 goes
=MID(A1,LEN(A1)-7,2)
and D1 has
=RIGHT(A1,5)