Substitue Leaves Excessive Space, Cannot Use TRIM

**goss** · 05-22-2012, 01:00 PM

Hi all,

Using Excel 2007

I need to check thousand of rows for amspersand "&" and Removit it if it exists.
I tried SUBSTITUTE($A$1,"&",""), but leaves excessive space in the result.
I cannot use TRIM() because there may be other existing excessive spaces that I need to retain in the final string.

For example Snow White & The Seven Dwarves should be Snow White The Seven Dwarves

Are there other options than SUBSTITUTE() or use SUBSTITIUTE() w/o the excessive spaces?

thx
w

**dilipandey** · 05-22-2012, 01:03 PM

Hi goss,

Would suggest you to use Ctlr + H (Find & Replace) option available in Excel.. thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

**ChemistB** · 05-22-2012, 01:08 PM

How about SUBSTITUTE($A$1,"& ","") or SUBSTITUTE($A$1," & ","")?

**LaurenR** · 05-22-2012, 02:06 PM

Hey there! I would highlight the column you want to search, then hit ctrl f. Choose "Replace" tab from the top, then in the find box enter the ampersand (&) and in the replace box put your cursor in and hit the space bar. It should replace all "&" with " ". I hope that helps!