Hi,

Using the function here to find the number

http://www.ozgrid.com/VBA/ExtractNum.htm

Text to the left can then be extracted with
=LEFT(A1,FIND(ExtractNumber(A1),A1)-2)

and text to the right of the number with
=MID(A1,(FIND(" ",A1,FIND(ExtractNumber(A1),A1))+1),999)

Mike




"Topher" wrote:

I have to import an address file into an excel sheet (I can do that) but one
of the columns in the file is a text field that contains the important house
number mixed with text:

The Old Vicarage 29 The High Street
T & G Plumbers 30 Long Road
The Corner Shop 2 Village Green

These text strings need splitting into 3 separate cells, data before the
number, the number, and data after the number. Can anyone help please.