Splitting an address (US) into separate cells
The FIND, LEFT, MID, RIGHT and LEN text functions are extremely useful and versatile. We have seen
before how to split a person’s name into it’s component parts.
Here, we take a single cell containing a US address and split it into it’s City, State and Zip components. As long as the string we want to partition is well beahved - as is the case here, splitting should be relatively straightforward with the appropriate combination of text functions.
If, for example, we did not have a comma after the city, we could use the formulae below to extract the city and state components…
=MID(B3;LEN(B3)-7;2)
Also, if you want to delete column B, you will first need to preserve the columns you have just created by doing a Paste Special (values only) as shown
here
This worksheet can be downloaded here