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

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

"LSSR" wrote:

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

You have replies elsewhere - please do not multipost.

Pete

On Nov 12, 11:54*pm, LSSR wrote:
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

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" wrote:

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

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

"Pete_UK" wrote:

You have replies elsewhere - please do not multipost.

Pete

On Nov 12, 11:54 pm, LSSR wrote:
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