Parsing part of an address

[LSSR]

I have excel 2003 and XP. I have a worksheet with some 50,000 entries.
Column E is an address field. Some 7,000 of the addresses have a suite
number in the format of
123 Main St # A
456 Elm St
789 Oak Ave # 44

I need to take delete the space to the left and to the right of the “#†and
place the value (the “A†and the “44†above example) into column F.

Is there a formula that I can paste and fill in column E (and if so, would
not return an error code in column F if the row in column E does not have a “
# “)? My skills are very, very basic and I do not know VBA .

thank you for your time

 

[FSt1]

hi
see if this will work for you....
=IF(ISERROR(SEARCH("#",E8)),"",RIGHT(E8,LEN(E8)-SEARCH("#",E8)+1))

work on all of your examples
regards
FSt1

 

[Pete_UK]

You have replies elsewhere - please do not multipost.

Pete

 

[Shane Devenshire]

Hi,

Although you didn't state it I suppose you want both the address without the
# A and then the A or # A in a separate column.

If the address is in A1, and you enter the following formula in D1:

=IF(ISERR(FIND(" # ",A1)),"",MID(A1,FIND(" # ",A1)+3,IF(ISERR(FIND("
",MID(A1,FIND(" # ",A1)+3,20))-1),10,FIND(" ",MID(A1,FIND(" #
",A1)+3,20))-1)))

And in F1:
=SUBSTITUTE(A1," # "&D1,"")

You will split the addresses. But these are very conditional on the
addresses being consistant.

If this helps, please click the Yes button.

Cheers,
Shane Devenshire

 

[LSSR]

Thank you. I was not sure of where to post it and will be more careful in
the future.