The solution I gave you works for 41a

"Topher" wrote:

Thanks for that guys but to add to the problem becuase I am looking at
address data there are times when the number could be associated with a
letter i.e. 41a High Road. Is there a way to deal with this aswell?

Thanks

"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.