Replacing quotes in Excel

As shown below, occasionally when you import data from another system into Excel, the data you want is surrounded by quotes (“). If you try to Find/ Replace them or use a SUBSTITUTE or similar formula to get rid of them it doesn’t work. This is because you need to use a special trick when replacing quotes in Excel.


As shown above, even though you want to replace a single quote at a time, you need to put double quotes between the double quotes, i.e. to replace a single quote you actually need 4 quotes in a row.

Excel’s new data cleanup tool

Data cleanup online course using only Excel

IF function not working with numbers