Parsing one 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

 

[Pete_UK]

Assume those 3 addresses are in E2:E4. Put this formula in F2 and copy
down into F3 and F4:

=IF(ISNUMBER(SEARCH("#",E2)),RIGHT(E2,LEN(E2)-SEARCH("#",E2)-1),"")

There is no way you can put a formula in column E which will affect
the value within column E, but if you wanted to remove everything to
the right of a # in column E, you could put this in G2 (say) and copy
down:

=IF(ISNUMBER(SEARCH("#",E2)),LEFT(E2,SEARCH("#",E2)-1),E2)

Hope this helps.

Pete

 

[Rick Rothstein]

You can do what you want in two steps without using any formulas or VBA.

1. Select Column E and then click Edit/Replace on the menu bar. In the "Find
what" field, place " # " WITHOUT the quote marks (that is, put space/#/space
characters in the field and then put just a # symbol in the "Replace with"
field. Finally, click the "Replace All" button. Doing this will remove the
spaces from around the # symbol.

2. With Column E still selected, click Data/Text To Columns, select the
Delimited option button and click the Next button. Select the Other check
box and put a # symbol in the field next to that check box, then click the
Next button. Select the second column in the chart at the bottom of the
dialog panel and then select the Text option button in the upper right
corner of the panel (assuming you want the suite numbers, when they are
numbers, to be text to match the text that the letters will be). Finally,
click the Finish button.

 

[LSSR]

That you both for your time. Both examples worked wonderfully.

 

[Pete_UK]

You're welcome.

Pete