Microsoft Office Forums - View Single Post - [Solved] How to use mid or left functions to seperate city,st,zip with no commas

excelledsoftware · 11-10-2014, 10:21 PM

Assuming that all of your addresses have 5 digit zipcodes and 2 character states you can use a combination of mid, right, and len formulas to do this quite easily.

The following formulas start with addresses in cell A2

Code:

For the City: =MID(A2,1,LEN(A2)-9)
For the State: =MID(A2,LEN(A2)-7,2)
For the Zip: =RIGHT(A2,5)

Let me know if that works out.

Thanks