Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Splitting City State Zip
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 |
#2
|
|||
|
|||
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) In article , "Pete Provencher" wrote: 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 |
#3
|
|||
|
|||
That worked1 Thanks a lot.
Pete Provencher "JE McGimpsey" wrote in message ... 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) In article , "Pete Provencher" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
splitting number strings in excel | Excel Worksheet Functions | |||
Zip - State - County | Excel Discussion (Misc queries) | |||
splitting and rejoining text in cells | Excel Worksheet Functions | |||
What Functions should be used | Excel Worksheet Functions | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |