How to Replace Double Quotes in Excel Using the SUBSTITUTE Function
Question details
The user needs to replace double quotation marks in an Excel cell using the SUBSTITUTE function but encounters syntax errors due to improper character escaping.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Cleaning or formatting text data containing double quotation marks that need to be replaced with a space or removed entirely.
- Observed behavior
- Excel returns a formula syntax error when a standard double quote is typed directly into the SUBSTITUTE function's arguments.
Identify the target cells containing the text you want to modify, and decide what character (such as a space or an empty string) will be used to replace the double quotes.
Use the CHAR(34) Function
The most readable and reliable way to handle double quotes in Excel formulas is by using the CHAR(34) function to represent the quotation mark.
Excel uses the CHAR function to return specific characters based on their numeric ASCII codes. The code for a double quotation mark is 34. Using this avoids confusing Excel's formula parser.
Click on an empty cell where you want the cleaned text to be displayed.
Type the formula =SUBSTITUTE(D7, CHAR(34), " ") into the formula bar. In this example, D7 is the cell containing the original text, and we are replacing the double quote with a space.
Press Enter to execute the formula. You can then drag the fill handle down to apply this formatting to the rest of your data column.
Use Four Consecutive Double Quotes
You can escape a double quote by typing four consecutive double quotation marks in the formula, though this method is visually confusing and prone to typos.
Easily Clean and Format Data with WPS Spreadsheet
WPS Spreadsheet fully supports the SUBSTITUTE and CHAR functions, allowing you to seamlessly process text data and manage complex formulas without hassle.
- 1. Open WPS Spreadsheet: Launch WPS Office and open your workbook containing the text data that needs cleaning.
- 2. Enter the Formula: Select a blank cell and type the SUBSTITUTE formula, utilizing CHAR(34) for the double quotes.
- 3. Apply and Fill: Press Enter to execute the formula, then click and drag the fill handle to apply it across your entire dataset.
Frequently Asked Questions
Why does Excel show an error when I type a double quote directly in a formula?
Excel uses double quotes to define the beginning and end of text strings. When you insert a standalone double quote inside a text string formula, Excel's syntax parser gets confused about where the text actually ends, resulting in a formula error. You must escape it using four quotes ("""") or the CHAR(34) function.
How do I remove the double quotes entirely instead of replacing them with a space?
To completely remove the double quotation marks from your text, leave the new_text argument empty in the SUBSTITUTE function. For example, use the formula =SUBSTITUTE(D7, CHAR(34), "").
Can I replace single quotes using the same SUBSTITUTE method?
Yes, you can replace single quotation marks. You can either type the single quote normally within double quotes (like "'") or use the CHAR(39) function in your SUBSTITUTE formula, such as =SUBSTITUTE(D7, CHAR(39), " ").
