Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have done this before accidentally and cannot recreate the process...

    I have one Excel column that has the address of a location appended together in the following format for example:
    Saratoga Springs, NY 12866

    I need it to be separated into 3 columns: [CITY] [ST] [ZIP]

    I successfully extracted the ZIP off the end using:
    =Right(A1,5)

    I know I need to use a SEARCH function in the middle of the formula to find the "," and get the State out etc..

    Consider that the address is in the A1 cell... Can someone tell me what will work to get City and State OUT!

    Thanks!
    There is always a way.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    To extract the city:

    =LEFT(A1,FIND(",",A1)-1)

    or

    =LEFT(A1,LEN(A1)-10)

    To extract the state:

    =MID(A1,LEN(A1)-7,2)

  3. #3
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Post

    AWESOME!

    Thank you!
    There is always a way.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2001
    Location
    Northern, NJ, New Jersey, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Smile

    As if on purpose I got a new file with one extra challenge...

    This time the address, city, State and Zip are in one field...
    49 El Dorado Lane, Colorado Springs, CO 80919

    All that you gave me got the address off the front and the St and Zip off the back...

    How can I extract the CITY from the middle???
    There is always a way.

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    One way to extra the information between two the two ,s

    Code:
    =TRIM(MID(A1,FIND(",",A1)+1,FIND(",",A1,FIND(",",A1)+1)-FIND(",",A1)-1))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •