« Financial Functions: Creating a Payment Chart | Main | Basic Functions: WEEKDAY »
December 28, 2005
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
Posted by Dave at December 28, 2005 11:19 AM