Extracting # numbers from address
 

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

=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

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