Terry's Computer Tips - Newsletter
June 11, 2006

---

Volume 1, Number 52 — Sunday, June 11, 2006

Part 1 | Part 2 | Part 3

---

7.  Excel - A Lesson in Text Manipulation

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))),iLEFT(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 formulat 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 formulae above, there could still be an issue on unusually-formatted street numbers. While we normally have numerics for the street number, e.g. "12345", sometimes a

---

8.  Recommend my Terry's Computer Tips Newsletter to Your Friends

If you like my  Terry's Computer Tips email newsletter or the online edition, you can help me increase the number of subscribers to my free emailed newsletter.  Recommend it to a friend.

Not only do you get notices that the newsletters are available, but subscribing is the only way to get my Special Edition Newsletters which go only to subscribers.

If you get my free Terry's Computer Tips email newsletter, please feel free to forward your copy of the newsletter to a friend or friends that you think would be interested. Be sure to forward the entire newsletter, including my copyright notices and any advertising.

Of course, if you do not get my free email newsletter, I invite you to subscribe, too!
Click to subscribe.

The current issue and the newsletter archives are available online at http://www.terryscomputertips.com/archives/ and via a link from the navigation bar on each Terry's Computer Tips web page.

---

Part 1 | Part 2 | Part 3

Volume 1, Number 52 — Sunday, June 11, 2006
Part 1 | Part 2 | Part 3

Copyright © 2006 Terry A. Stockdale.  All rights reserved.