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