I'm trying to separate the house number from the rest of the address within a
column and I can't follow what you say to do. Can you give it to me in exact
steps (i.e., click this and insert that) and send it to my email address?

patbaeske at charter dot net




"Mike H" wrote:

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.