Parsing part of an address
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
|