How to Use Excel Text to Columns To Separate Addresses

In a previous post we explained how to separate names using Microsoft Excel’s text to columns function. But, what if your data is a little more complicated, like an address? In this post we’ll walk you through some more advanced techniques with Microsoft Excel’s text to columns function. We’ll be using Excel 2007 for this tutorial but the concepts are the same for other versions of Excel.

Excel Text to Columns to Separate Addresses

The technique you use to separate your address into multiple columns in Excel will depend on how your information is stored. In this example, the address column was separated into two lines, the street address on the first line and the city, state, zip on the second line.

Separate the Two Lines into Two Separate Columns

1. Make sure there is an empty column to the right of the address. In our example our address is in column B and column C is empty so we’re ok. If column C also had data in it we would highlight column C and click on insert from the home menu. That would insert a blank column before column C.
2. Select the column with the addresses in it. In our example, column B has the addresses so we would select the entire column by clicking on the letter B at the top of the column. 
3. Select the text to columns function from the data menu. 
4. When the text to columns wizard opens, select Delimited and click Next. 
5. In the 2nd wizard box uncheck all the boxes except for other. Then, put your cursor in the box next to other and type ALT-010 (in other words, hold the ALT key down and type 010). The box may look empty or have a period in it but if you look below you will see that the data is split into two columns right where it was split into two lines. That’s because ALT-010 is the code for line feed. Click next.
   
   If ALT-010 isn’t working:
   • When you use ALT shortcuts like ALT-010 above you have to use the numbers in the number pad (to the right of your keyboard). These don’t work with the numbers across the top of your keyboard.
   • If you don’t have a number pad then most keyboards come equipped with a NumLock button. Usually you have to press a Function key (Fn) and then NumLock to active a number pad using the keys toward the right side of your normal keyboard. If you enable number lock then type ALT-mjm (the m is 0 with the number lock on and the j is 1 with the number lock on).
   • As a last resort, if you can’t find a NumLock button on your keyboard then turn on the on screen keyboard (type on screen keyboard into the Cortana search box). When the keyboard opens, click options and click turn on numeric keypad. Then, press the ALT key and type the 010 in the on screen keyboard.
6. The last box of the text to columns wizard allows you to define the type of data in your resulting columns. We don’t need to do anything in this box so click finish. 
7. Your address will now be split into a street address column and a city, state and zip column. 

Separate the City, State and Zip into 3 Columns

Now say we want to sort our data by state or filter out only certain cities. That will be easier if the city, state and zip were each in their own column. There are several ways to do this. Here’s one way:

1. Since we’re going to take our city, state and zip column and separate it into three columns we need to make sure we have two empty columns to the right of the city, state, zip column. In our example we do so we’re all set. If we didn’t have two empty columns we would select the column to the RIGHT of our city, state and zip column and click insert on the home menu two times. This would insert two empty columns. 
2. Now, to separate the city, state and zip, highlight that column by selecting the letter at the top of the column and select text to columns from the data menu. 
3. Again, when the text to columns wizard opens, select delimited from the 1st window and click next. 
4. Our city, state and zip are separated by commas so in the 2nd box of the wizard we’re going to uncheck every box except for the one that one next to the comma. Then, by looking at the data in the bottom of the window we can see this will separate the city, state and zip exactly the way we wanted so we click next. 
5. This time, in the 3rd box of the wizard we are going to identify a data type. We’re going to identify the data type for the zip code column. This is because if we don’t Excel may assume these are numbers and cut off any zeros in the beginning of the number. So, a zip code of 01234 would only show up as 1234 and cause problems. So, in this box we’ll highlight the zip code column at the bottom of the window by clicking in it and at the top we’ll select text. This will force Excel to store the zip codes as text instead of numbers. Then we’ll click finish. 
6. After adding some titles to our new columns are spreadsheet is all set and ready to go with the addresses split into appropriate columns for sorting or doing mail merges. 

Again, depending on how your data is structured you may have to play around with the delimiters you select in step 2 of the text to column function but the preview at the bottom of that window should help you make sure you select the right combination for your data. Or, if you get stuck, feel free to leave a comment below and we’ll help out.

Conclusion

Separating out the address components into separate columns will make it easier for you search your data, filter it and/or complete mail merges for printing envelopes or labels.

Having Trouble? Hire Me to Edit your Spreadsheet