Productivity Portfolio

Many membership databases or mailing lists are set up with fields allocated for First Name, Last Name, Street, City, State and Zip. This format works fine if you're creating a mailing label as the post office relies on zip code sorting. But what if you need to do door to door canvassing to check on neighbors or to inform people about upcoming ballot measures? If you pull this type of list into Excel and sort on Street, you get a list that is numerically sorted. As you can see in the example below, the Drake Ave records are not together.

Click to enlarge

Ideally, you would like to sort the list so all the Drake Ave. entries are together. There are several ways to do this in Excel, but one way is to create two columns from the Street field. The first column reflects the street number and the second the street name. You can then resort the list based on the street name and then street number.

To separate street column value into distinct street and number values,

1. Import your data into Microsoft Excel or use the sample spreadsheet.

2. In cell D1, type Nbr.

3. In cell E1, type Street Name

4. In cell D2, type the following Excel formula =LEFT(C2,FIND(" ",C2)-1)

5. Press Enter. The value 1001 should show in D2

In Step 4, we've instructed Excel to look at the original street value in cell C2 and to copy the contents up to, but not including the first space.

The next part involves copying this formula to the rest of the entries. However, we need to reference the correct street cell and not use C2 for the remaining rows.

6. Click cell D2 to select the beginning of our range.

7. Shift-click cell D16. The column should highlight.

8. From the Edit menu, select Fill and then Down.

In column D, you should see the street numbers. Even though they show as numbers, Excel has them classified as text. To properly sort by street number, we should change the cell format.

9. Right-click cell D1 and select Format Cells

10. In the Format Cells dialog, select Number and 0 decimal places.

11. Press OK.

We'll now create a similar formula to capture the street address. This time, we will grab the contents to the right of the first space from the Street column.

12. In cell E2, type the following formula =RIGHT(C2,LEN(C2)-FIND(" ",C2,1))

13. Press Enter. E2 should show as Alturas Dr.

14. Click cell E2 to select the beginning of our range.

15. Shift-click cell E16. The column should highlight.

16. From the Edit menu, select Fill and then Down.

Columns D and E should contain the parsed contents from your original street address. The last step is to sort the spreadsheet by Street and then number.

17. From the Data menu, select Sort.

18. In the Sort by list box, select Street Name

19. In the Then by list box, select Nbr.

20. Click OK.

Your spreadsheet should look similar to the one below. We did make a few tweaks such as hiding column C that was the original street column. From here, you can also use Auto-filter to pull out people from specific streets.

Click to enlarge

Although our example is parsing one cell containing street information, you can use the same steps to parse other types of entries. For example, Step 12 is really parsing everything but the first word because it is searching for a blank space. You can alter the formula to find different values.

Additional Excel Resources

Sample Excel Spreadsheet
5 Minute Excel Tutorial - Converting Street Addresses