Hi KH,

Try this formula (in a new column, say in B1)! It assumes that the
addresses start at A1. Change the 'A1's in the formula appropriately.
Fill-in the formula in the rest of the new column.

=IF(ISNUMBER(FIND("
",TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))))),LEFT(TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))),FIND("
",TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))))-1),TRIM(RIGHT(A1,LEN(A1)-FIND("#",A1))))

Regards,
B.R. Ramachandran

"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


--
KH