Splitting City State Zip

  • Thread starter Pete Provencher
  • Start date
P

Pete Provencher

Excel 2000:

I'm trying to split the city state zip field into individual fields. I was
able to get the zip and state by using the folllowing formulas:

State: =LEFT(RIGHT(M2,10),3)

Zip: =RIGHT(M2,5)

But, I can't seem to get the city. The problem being no comma betwee the
city and St and cities with two name:

Loris SC 29573
Myrtle Beach SC 29577
Little River SC 29588

Any help will be appreciated.


Pete Provencher
 
J

JE McGimpsey

How about

=TRIM(LEFT(TRIM(A1),LEN(TRIM(A1))-9))

If your addresses are all well-formed (e.g., no extra spaces), that
reduces to

=LEFT(A1,LEN(A1)-9)
 
P

Pete Provencher

That worked1 Thanks a lot.

Pete Provencher
JE McGimpsey said:
How about

=TRIM(LEFT(TRIM(A1),LEN(TRIM(A1))-9))

If your addresses are all well-formed (e.g., no extra spaces), that
reduces to

=LEFT(A1,LEN(A1)-9)
Click to expand...
 

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

Similar Threads

separate an address street/city,state,zip within a cell? 6
Auto Populating city, state and county based on the zip code. 1
Changing info in one field to 3 fields 2
Many (City) to Many (Zip Code) help 8
Parse City State and Zip code 1
City State Zip 3
Splitting single field into 3 fields 7
Split city, state zip 1

Top