The following formulas are intended for use in an Excel spreadsheet in order to parse an address cell that has two lines separated by a carriage return. The formulas will create two new cells, each containing one of the address lines.

Forumulas in Excel can be tricky. The following instructions assume that you have some experience with Excel using formulas and know how to copy them.

1) Make a copy of your Excel file in case things go wrong.
2) Insert two new columns just after the address column.
3) Once you've entered the formulas (4) and (5), change the references to the original cell as appropriate. In the following examples:

A1 = Original address cell with the two lines and carriage return.

4) In the first NEW column, top cell, enter the following formula;

=IF(A1 = "", "", IF(ISERROR(FIND(CHAR(10),A1,1)),A1,LEFT(A1,FIND(CHAR(10),A1,1)-1)))

Change all "A1" to whatever your original address cell is. This should put the top address line in this cell. Copy the top cell in the column to all the cells below it in the same column.

5) In the second NEW column, top cell, enter the following formula:

=IF(A1="","",IF(ISERROR(FIND(CHAR(10),A1,1)),"",(MID(A1,FIND(CHAR(10),A1)+1,LEN(A1)-FIND(CHAR(10),A1)+1))))

Change all "A1" to whatever your original address cell is. This should remove the carriage return and put the bottom address line in this cell. Copy the top cell in the column to all the cells below it in the same column.

These formulas should work for:
1. Addresses that have a carriage return: will return values in the two new cells
2. Addreses that are one line only with no carriage return: will return a value in the first new cell
3. Addresses that are blank: will return nulls in the two new cells

Back to the top