LinkBack URL
About LinkBacks
Hello.
I have a big excel with address written like this (example) in a single cell:
Manhattan Beach, CA 90266-7225
I would like separate into 3 cells - separating into City, State, Zip.
Anyone knows the formula?
Thanks you so much!
Hello.
I have a big excel with address written like this (example) in a single cell:
Manhattan Beach, CA 90266-7225
I would like separate into 3 cells - separating into City, State, Zip.
Anyone knows the formula?
Thanks you so much!
hi there. it's always good to upload a few eg in an excel file & show us your desired results. if you could, share with us some patterns you know because not all of us are familiar with your City, State & Zip. from what i assume; City before comma, State before the space of the number, & last series of numbers are the Zip. so maybe:
=LEFT(A1,FIND(",",A1)-1)
=LEFT(MID(A1,FIND(",",A1)+2,255),FIND(" ",MID(A1,FIND(",",A1)+2,255))-1)
=MID(A1,FIND("^",SUBSTITUTE(A1," ","^",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))),255)
the last one could be as simple as:
=RIGHT(A1,10)
provided ZIP is always 10 characters
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
benyben123, Good Evening.
If your data have the same layout then you can use these formulas:
If your data is at A2:
B2 --> =LEFT(A2,SEARCH(",",A2)-1)
C2 --> =MID(A2,SEARCH(",",A2)+2,2)
D2 --> =RIGHT(A2,10)
Is it what you desire?
Tell us if it worked for you.
I hope it help you.
...If my answer helped you, Please, click on. * Add Reputation (at left)
Best regards.
Marc?lio Lob?o
Thank you so much!
I'll try it when I get back home.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks