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

=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

=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

=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, " ", "")))))

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".

ReplyDeletehey, see the format i just added, which should work regardless of length of state:

Deleteformat: "city, state zip" e.g. "La Jolla, California 92092-0100"

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

ReplyDeleteyay! that is my sole motivation for this blog.

Deleteformat: "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!

ReplyDeletemy 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.

DeleteThank 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.

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

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteVery 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.

ReplyDeleteGets it done. Hats off.

ReplyDeleteOHMYGOSH...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!!!!!

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

ReplyDeleteThank you! You are appreciated!

ReplyDeleteThank you! You are appreciated!

ReplyDeleteThis comment has been removed by a blog administrator.

ReplyDeleteThank you Alex - Where do I send $$

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

ReplyDeleteThis was absolutely perfect! Thanks.

ReplyDeleteHUUUUUGE help, thank you very much!

ReplyDeleteThanks for this. It worked :)

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

ReplyDeletein 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?!

ReplyDeletethere'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

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

ReplyDeleteThank you,

-Excel Dummy

Thank You so much worked like a dream

ReplyDelete