Extracting # numbers from address

[Guest]

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

 

[Jerry W. Lewis]

=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

 

[Guest]

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