City State Zip
 

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

City State Zip
 

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.

Texas Nuckols wrote:

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

City State Zip
 

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" wrote:

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.

Texas Nuckols wrote:

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

City State Zip
 

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)



Texas Nuckols wrote:

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" wrote:


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.

Texas Nuckols wrote:


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