=FIND("#",address)
will return the location of the # sign, (or an error if it is not
there). You could then use LEFT(), RIGHT() and MID() functions to split
up the address around that point and looking for the next space.

Jerry

KH wrote:

I trying to extract the unit numbers from an address which have the unit
number all over and then clearing the extracted #unit from the original
column.

Any easier formula?

e.g

123 Albert St #01-01
#02-112 St James St
12 Tomlinson Rd #01-01/02 George Bldg
#10-01-03 Smith St