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, " ", "")))))
Posted by at

26 comments:

  1. dgcJune 19, 2013 at 7:22 AM

    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. Alexander TaylorJune 19, 2013 at 3:07 PM

      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. DMOctober 7, 2013 at 3:24 PM

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

    ReplyDelete
  3. UnknownOctober 16, 2013 at 6:45 AM

    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. Alexander TaylorOctober 16, 2013 at 8:46 PM

      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. Alternate Image, Inc.October 18, 2013 at 8:42 AM

    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. dashboard reporting with excelJanuary 9, 2014 at 5:02 AM

    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. data analytics servicesJanuary 9, 2014 at 9:13 PM

    This comment has been removed by a blog administrator.

    ReplyDelete
  7. MarkusFebruary 19, 2014 at 12:57 AM

    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. AnonymousFebruary 21, 2014 at 1:11 PM

    Gets it done. Hats off.

    ReplyDelete
  9. AnonymousAugust 29, 2014 at 11:36 AM

    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. UnknownMay 24, 2015 at 3:08 PM

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

    ReplyDelete
  11. The Bianconi'sMay 23, 2016 at 7:36 PM

    Thank you! You are appreciated!

    ReplyDelete
  12. The Bianconi'sMay 23, 2016 at 7:37 PM

    Thank you! You are appreciated!

    ReplyDelete
  13. BrendaPalmeriMay 26, 2016 at 4:07 AM

    This comment has been removed by a blog administrator.

    ReplyDelete
  14. Tom CaffreyJune 14, 2016 at 5:10 AM

    Thank you Alex - Where do I send $$

    ReplyDelete
  15. UnknownJuly 29, 2016 at 4:31 PM

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

    ReplyDelete
  16. AnonymousAugust 2, 2016 at 7:00 PM

    This was absolutely perfect! Thanks.

    ReplyDelete
  17. AnonymousOctober 21, 2016 at 5:48 PM

    HUUUUUGE help, thank you very much!

    ReplyDelete
  18. GabrielJanuary 12, 2017 at 12:13 PM

    Thanks for this. It worked :)

    ReplyDelete
  19. AnonymousJanuary 19, 2017 at 2:21 PM

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

    ReplyDelete
  20. Excel dummyMarch 2, 2017 at 7:37 PM

    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. Alexander TaylorMarch 2, 2017 at 7:47 PM

      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
  21. Excel dummyMarch 2, 2017 at 8:06 PM

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

    Thank you,

    -Excel Dummy

    ReplyDelete
  22. UnknownMarch 16, 2017 at 8:23 AM

    Thank You so much worked like a dream

    ReplyDelete

Subscribe to: Post Comments (Atom)