2012-11-15

split "city, state zip" in excel.


here are some formulas to split a cell containing city, state and zip into separate city, state and zip cells in microsoft excel.

format: "city, state zip" e.g. "La Jolla, CA 92092-0100"
  • comma after city name, followed by a space
  • state is two letters, followed by a space
in cell b1, type
=LEFT(A1,(FIND(",",A1))-1)

in cell c1, type
=MID(A1,FIND(",",A1)+2,2)

in cell d1, type
=RIGHT(A1,LEN(A1)-FIND(",",A1)-4)

format: "city, state zip" e.g. "La Jolla, California 92092-0100"
  • comma after city name, followed by space
  • state is specified but of any length and followed by space
in cell b1, type
=LEFT(A1,(FIND(",",A1))-1)

in cell c1, type
=MID(A1,FIND(",",A1)+2,FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", ""))))-(FIND(",",A1)+2))

in cell d1, type
=RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))))

format: "city state zip" e.g. "La Jolla CA 92092-0100"
  • city, state, zip separated by 1 space only
  • state is two letters
in cell b1, type
=LEFT(A1,(FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))))-4)

in cell c1, type
=MID(A1,(FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))))-2,2)

in cell d1, type
=RIGHT(A1, LEN(A1) - FIND("*", SUBSTITUTE(A1, " ", "*", LEN(A1) - LEN(SUBSTITUTE(A1, " ", "")))))

26 comments:

  1. This is very helpful. However, in my list, some states use the 2 character abbreviation and some are spelled out. How would I adjust your formula for that? (in the format "city, state zip".

    ReplyDelete
    Replies
    1. hey, see the format i just added, which should work regardless of length of state:
      format: "city, state zip" e.g. "La Jolla, California 92092-0100"

      Delete
  2. Thanks a million for posting this! You just saved a fellow human many hours tedious work!

    ReplyDelete
    Replies
    1. yay! that is my sole motivation for this blog.

      Delete
  3. format: "city state zip" e.g. "La Jolla CA 92092-0100" - This is the format I used and it worked for everything but the State. It gives me " I " instead of the " MI " I am looking for. Cities are 1 or 2 words, no commas, one space between state and city and 2 spaces between 2 letter state and ZIP. Can you help. Thanks!

    ReplyDelete
    Replies
    1. my formula works with single spaces but your data has double spaces. i would try to use excel's SUBSTITUTE function to replace double space with single space, then apply my formula to the cells with the replacement.

      Delete
  4. Thank you so much. I think I am pretty good at Excel but love it when I can find concise, information information that makes my life easier.

    ReplyDelete
  5. Your site holds much other data that gives more learning and numerous more plans regarding the subjects you have given in your site.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. Very helpful, thank you. Had to use another guide for pictures and the basics of the Excel MID function, but thats just cause im kind of a newbie to excel. Thats not at all supposed to be critical statement towards teh quality of your blog, i just needed a little extra help :) Keep up with your good work.

    ReplyDelete
  8. Gets it done. Hats off.

    ReplyDelete
  9. OHMYGOSH...YOU ARE A GENIUS! THANK YOU, THANK YOU FOR SAVING ME HOURS UPON HOURS OF WORK AND PULLING MY HAIR OUT! LOOKING FORWARD TO CHECKING OUT WHAT ELSE IS ON YOUR BLOG. Okay, done shouting in excitement now. THANK YOU!!!!!

    ReplyDelete
  10. Saved me a lot of work. Thanks for your code.

    ReplyDelete
  11. This comment has been removed by a blog administrator.

    ReplyDelete
  12. Thank you Alex - Where do I send $$

    ReplyDelete
  13. thanks. What about if the given data are full addresses? the state and zip formula are good to go, just the city.. tia :)

    ReplyDelete
  14. This was absolutely perfect! Thanks.

    ReplyDelete
  15. HUUUUUGE help, thank you very much!

    ReplyDelete
  16. So amazing, it worked first try! Saved me a ton of time, thank you!

    ReplyDelete
  17. in my case I need the full street address away from the city... it is currently like this in one column 12345 n. street name terrace way city, ca 12345zip I can't seem to seperate these complicated street name/address from the city... even though they are at the end of the day for matted like this street address space city, ca zip... I need 4 simple columns.. help?!

    ReplyDelete
    Replies
    1. there's not even a newline? if two pieces of information are separated only by a space and both pieces of information also contain spaces, i don't think there's any way to automatically split it up in Excel or Google Sheets unless you got into scripting and looked into some cloud based address normalization API like this: https://www.usps.com/business/web-tools-apis/welcome.htm

      Delete
  18. nope... it's ugly.. I'll take a look

    Thank you,

    -Excel Dummy

    ReplyDelete
  19. Thank You so much worked like a dream

    ReplyDelete