MrExcel Publishing
Your One Stop for Excel Tips & Solutions

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