Mailing Lists: How to Split the Address Column in Excel

After you purchase a mailing list, you can edit the Excel file as much or as little as you like. Some people like to add additional columns for ‘Notes’, or combine their new list with a current customer list, and every so often, some people need to sort their list by Street Address. In order to do this, you need to know how to separate the ‘Street Address’ into 2 new columns, 1) Street Number, and 2) Street Address.

Step 1

Open your Excel file mailing list.

 

 

 

 

 

 

 

In a LeadsPlease Consumer Mailing List, the street address will be in Column C.

Highlight Column C, and Insert 2 new columns to the right. Your 2 new, blank columns, will be D and E.

Column D will be used for the ‘Street Number’, and column E will be used for the ‘Address’.

 

 

 

 

 

Step 2

Input the following code into cell D2 : =LEFT(C2,FIND(” “,C2,1))

 

 

 

 

 

Then click ‘enter’, and Excel will insert the street number into cell D:

 

 

 

 

 

Step 3

Click inside cell E2, and enter the following formula:    =TRIM(RIGHT(C2,(LEN(C2)-LEN(D2)+1)))

 

 

 

 

 

Then click ‘enter’, and Excel will insert the street address into cell E2.

 

 

 

 

 

Now, just copy and past the 2 formulas into the other cells in column D & E. Done!

 

Leave a Reply

Your email address will not be published. Required fields are marked *