City State Zip

T

Texas Nuckols

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
 
B

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.
 
T

Texas Nuckols

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

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top