Subscriber Don Frattini wrote recently in another mailing list asking how to split a text field into two pieces. Specifically, he had a text filed that had the street address including the number, and wanted to get the number and the street in different cells. After I answered his question, Don was kind enough to let me quote his question here.

I have a list with the address as “12345 Lake Sherwood Ave. East” all in one cell. How can I (or can I?) remove the 12345 and put it in a separate cell, other than individually cutting and pasting each of 313 entries?

Don Frattini

To do this, you can take advantage of one of Excel’s “Logical” functions (IF), an “Information” function (ISNUMBER), a “Text” function (VALUE), and the Text functions LEFT and RIGHT.

Assuming the address is in cell A7, and you want the street number in cell B8, the formula for cell B8 would be

=

IF(ISNUMBER(VALUE(LEFT(A7,5))),LEFT(A7,5),

IF(ISNUMBER(VALUE(LEFT(A7,4))),LEFT(A7,4),

IF(ISNUMBER(VALUE(LEFT(A7,3))),LEFT(A7,3),

IF(ISNUMBER(VALUE(LEFT(A7,2))),LEFT(A7,2),

IF(ISNUMBER(VALUE(LEFT(A7,1))),LEFT(A7,1),”error” )))))

To put the right-hand side of the address in cell C8, the formula in cell C8 would be

=

IF(ISNUMBER(VALUE(LEFT(A7,5))),RIGHT(A7,LEN(A7)-5),

IF(ISNUMBER(VALUE(LEFT(A7,4))),RIGHT(A7,LEN(A7)-4),

IF(ISNUMBER(VALUE(LEFT(A7,3))),RIGHT(A7,LEN(A7)-3),

IF(ISNUMBER(VALUE(LEFT(A7,2))),RIGHT(A7,LEN(A7)-2),

IF(ISNUMBER(VALUE(LEFT(A7,1))),RIGHT(A7,LEN(A7)-1),”error”)))))

Copy the above formula (either of them), move to the spreadsheet and click in the desired destination cell. BUT, don’t paste the formula there! After selecting the cell, paste the formula INTO THE FORMULA BAR.

If you copy and paste either of the above formulas into a cell, you’ll get five vertical cells with *text*; each line will go in one cell.

But, if you select a cell and then paste in its formula bar, the entire formula will go in one cell. Then you can copy the cell into other cells.

By the way, this approach will also retain the multi-line nature of the above formulas in the formula bar — making it easier to follow when you go back to it later. It also makes it easier to spot a typo in the formula!

Other contributors to that mailing list suggested that, since we know the separator is a space character, we could use of Excel’s Find, Trim, Left, Right and Len functions to split the data.

With the formula above, there could still be an issue on unusually-formatted street numbers.

## Leave a Reply