Excel Formula to Replace Curly and Smart Quotes with Straight Quotes

Importing text into Excel often leads to frustrating syntax errors due to troublesome "smart" curly quotes. While standard funding sources and enterprise databases deliver clean data exports, manual transfers from documents frequently introduce these formatting bugs. Resolving this issue grants you immediate data integrity and seamless database compatibility. The stipulation, however, is that Excel requires nested SUBSTITUTE functions to target both left and right curly marks. This technique is essential when preparing text for SQL queries or CSV uploads. Below, we outline the exact Excel formula and step-by-step implementation to automate this cleanup.

If you have ever copied data from Microsoft Word, a PDF document, or a web page and pasted it into Excel, you have likely run into the dreaded "smart quotes" issue. Also known as curly quotes, these stylized typographer's marks (“, ”, ‘, and ') look great in articles and essays, but they are a nightmare for data processing, coding, database migrations, and Excel formulas.

Excel requires standard, straight quotes (" and ') to recognize text strings in formulas. If your data contains smart quotes, logical tests like VLOOKUP, MATCH, or IF statements will fail to find matches, and CSV exports may become corrupted. Fortunately, you can easily clean up your data using Excel formulas, manual tools, or automated workflows.

In this comprehensive guide, we will walk through the exact Excel formulas to instantly replace smart quotes with straight quotes, explain the mechanics behind the solution, and explore alternative methods to keep your data perfectly clean.

The Core Problem: Smart Quotes vs. Straight Quotes

Before writing the formula, it helps to understand why Excel views these characters differently. Although they look similar, computers read them as entirely distinct characters with different character codes:

Because these characters have different underlying code values, Excel does not recognize them as equivalent. To clean up your text, you must replace all four curly variations with their respective straight counterparts.

The Excel Formula: Nested SUBSTITUTE

The most reliable and dynamic way to solve this is by using the SUBSTITUTE function. Because we have multiple characters to replace, we must nest several SUBSTITUTE functions inside one another.

The Ultimate Multi-Quote Replacement Formula

To replace both double and single curly quotes in cell A2, copy and paste the following formula into an adjacent column:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2, CHAR(8220), CHAR(34)), CHAR(8221), CHAR(34)), CHAR(8216), CHAR(39)), CHAR(8217), CHAR(39))

How This Formula Works

Excel executes nested functions from the inside out. Here is a breakdown of how this formula systematically purges curly quotes from cell A2:

• Step 1: SUBSTITUTE(A2, CHAR(8220), CHAR(34)) searches cell A2 for any left curly double quotes (“) and replaces them with straight double quotes (").
• Step 2: The next outer SUBSTITUTE takes that result and searches for right curly double quotes (”, represented by CHAR(8221)), replacing them with straight double quotes.
• Step 3: The third SUBSTITUTE takes the cleaned text and replaces left curly single quotes (‘, represented by CHAR(8216)) with straight single quotes (', represented by CHAR(39)).
• Step 4: The final outer SUBSTITUTE replaces right curly single quotes (', represented by CHAR(8217)) with straight single quotes.

Why Use CHAR Codes Instead of Typing Quotes?

You might wonder why we use CHAR(34) and CHAR(39) instead of typing the quotes directly into the formula. While you can type curly quotes directly inside formula strings (e.g., "“"), typing straight double quotes inside a formula is incredibly tricky.

In Excel formulas, double quotes are used to define text boundaries. If you want to use a literal straight double quote inside a text string, you have to escape it by typing four double quotes in a row (""""). This makes formulas difficult to read and prone to syntax errors. Utilizing the CHAR() function bypasses this confusion entirely, making your formulas clean, readable, and robust.

Alternative Formula: Handling Only Double Quotes

If your dataset only contains double curly quotes and you want to leave single quotes untouched, you can use a simplified, shorter version of the formula:

=SUBSTITUTE(SUBSTITUTE(A2, CHAR(8220), CHAR(34)), CHAR(8221), CHAR(34))

This targets only the left and right double curly quotes, turning them into standard double quotes.

Alternative Formula: Handling Only Single Quotes

Conversely, if you only need to clean up curly apostrophes or single quotes, use this variation:

=SUBSTITUTE(SUBSTITUTE(A2, CHAR(8216), CHAR(39)), CHAR(8217), CHAR(39))

Quick Non-Formula Method: Find and Replace

If you only need to perform this clean-up as a one-time task and do not want to set up helper columns with formulas, you can use Excel's built-in Find and Replace tool. However, because Excel handles smart quotes natively in its search field, you must follow these steps carefully:

1. Select the range of cells or column you want to clean.
2. Press Ctrl + H (or Cmd + Shift + H on Mac) to open the Find and Replace dialog.
3. In the Find what box, type or copy-paste the left curly quote: “.
4. In the Replace with box, type a straight double quote: ".
5. Click Replace All.
6. Repeat this process for the right curly double quote (”), replacing it with the straight double quote (").
7. Repeat again for curly single quotes (‘ and '), replacing them with straight single quotes (').

Tip: If your version of Excel automatically converts your typed straight quotes into smart quotes during this process, you may need to temporarily disable the "Smart Quotes" autocorrect feature in your options (explained below).

Automating with Power Query (For Large or Reoccurring Datasets)

If you regularly import external text files, CSVs, or web data into Excel, using Power Query is the most efficient way to automate the removal of smart quotes. Once set up, Power Query will clean your data automatically every time you click "Refresh."

1. Select your data table and go to the Data tab, then click From Table/Range to open the Power Query Editor.
2. Right-click the header of the column containing the messy text and select Replace Values....
3. In the Value To Find box, paste the left curly quote “. In the Replace With box, type a straight quote ". Click OK.
4. Repeat this step for the other three types of curly quotes (”, ‘, and ').
5. Once finished, click Close & Load on the Home tab to return the clean data to your Excel worksheet.

How to Prevent Smart Quotes in Excel

While fixing existing data is essential, preventing smart quotes from appearing in the first place will save you hours of troubleshooting in the future. Excel does not typically convert straight quotes to smart quotes as you type, but Microsoft Word and Outlook do. If you compose text in Word and copy it to Excel, the smart quotes will travel with it.

To turn off the automatic generation of smart quotes across your Microsoft Office apps, follow these steps:

1. Open Excel or Word and click on the File tab.
2. Select Options at the bottom of the left sidebar.
3. Go to the Proofing category.
4. Click on the AutoCorrect Options... button.
5. Navigate to the AutoFormat As You Type tab.
6. Under the "Replace as you type" section, uncheck the box for "Straight quotes" with “smart quotes”.
7. Click OK to apply and exit.

Once disabled, Microsoft Office will preserve your clean, straight quotes exactly as you type them, ensuring compatibility with your spreadsheets, database scripts, and code exports.

Conclusion

Smart quotes can easily break search tools, lookup formulas, and data analysis pipelines in Excel. By utilizing the nested SUBSTITUTE formula combined with CHAR codes, you can instantly sanitize your datasets without worrying about syntax errors or complex formatting rules. For manual or repeatable workflows, the Find and Replace tool and Power Query provide great alternatives. Turn off the "Smart Quotes" auto-formatting feature in your Office settings to stop this frustrating issue from occurring ever again.