splitting contents of a cell

[Luc]

Hi all,

I have a list of addresses split over five or so columns. One of the
columns has the format "# street name". Is it possible to write a formula
that lifts the street name out of that cell but not the number? so from a
cell with "25 Hight Street" I would want just "High Street".

hope that makes sense,

thanks,

luc

 

[icestationzbra]

if cell A1 has 25 HIGH STREET, the following formula in B1:

=RIGHT(A1,LEN(A1)-SEARCH(" ",A1))

will extract HIGH STREET.

 

[Luc]

thanks a lot for that. it worked perfectly.