extract numbers in text

Posted by John Everett on June 11, 2001 11:27 AM

how do I extract numbers from a text string

I have
123 Yellow Lane

I need
123 (in one cell) and Yellow Lane (in the next cell)

Thanks
John Everett

Posted by IML on June 11, 2001 11:48 AM

You could do it by formula as follows (assuming your address is in A1)

=LEFT(A1,FIND(" ",A1)-1) for number

=RIGHT(A1,LEN(A1)-FIND(" ",A1)) for the rest

You may want to copy - paste special values over this and delete you first row.

Good Luck