To remove straight double quotation marks from a cell in Excel, use =SUBSTITUTE(A2,CHAR(34),"") when the text is in A2; for example, He said ""Hello"" becomes He said Hello. To put a literal double quote inside a formula’s text, type it twice inside the quoted text, as in ="She said ""Hello""", which displays She said "Hello".
What a double quote means in Excel
Excel uses the straight double quote character (") to mark the beginning and end of text in a formula. That creates a small but important distinction: the quotes that define a text string are not necessarily characters that Excel displays. In ="Hello", the outer quotes tell Excel that Hello is text; the result shown in the cell is simply Hello, without quotation marks.
If you want a quote character to appear in the result, Excel needs a way to distinguish that character from the marks that delimit the text string. The standard method is to double the quote inside the string. Thus, two quote characters inside the formula’s text represent one visible quote in the result. This is why a formula containing several consecutive quotes can be correct even when it looks confusing at first glance.
The word “substitute” can also mean changing or removing quote marks already present in cell contents. For that task, Excel’s SUBSTITUTE function replaces one piece of text with another. It does not change the rule for writing text strings inside formulas; these are related issues, but they call for different techniques.
Remove double quotes with SUBSTITUTE
Suppose imported text in A2 contains straight quotes and you want a cleaned version in B2. With the source text in A2, enter =SUBSTITUTE(A2,CHAR(34),"") in B2. If A2 contains He said "Hello", the result is He said Hello. Fill the formula down to clean additional rows, or use the fill handle to copy it through a data range.
CHAR(34) returns the straight double quote character. The three arguments in the formula are the original text, the character to find, and what to put in its place. The final argument, "", is an empty text string, so every matching quote is removed. SUBSTITUTE replaces every matching occurrence when its optional fourth argument is left out.
You can also write the search character directly in the formula, but it takes four consecutive quote marks to express one literal quote as a text value. With the source text in A2, this formula removes straight quotes and returns He said Hello if that is the content of A2: =SUBSTITUTE(A2,"""",""). Many people find CHAR(34) easier to read, especially when formulas are nested or maintained by someone else.
To replace quotes rather than delete them, change the replacement argument. With source text in A2, =SUBSTITUTE(A2,CHAR(34),"'") changes He said "Hello" to He said 'Hello'. The replacement apostrophe is enclosed in double quotes because it is text; it does not need special escaping.
Replace only a particular quote
SUBSTITUTE has an optional fourth argument, instance_num, that limits the change to a particular occurrence. This is useful when a cell contains several pairs of quotes but only one needs to change. With text in A2, =SUBSTITUTE(A2,CHAR(34),"",2) removes only the second straight double quote it encounters; any other quote characters remain. If the cell has fewer than two matching quotes, the text is returned without that second-occurrence change.
The function counts occurrences from left to right. It is also case-sensitive when you substitute ordinary text, though that detail usually does not matter for the quote character itself. If the desired change is to remove every quote, leave out the fourth argument; specifying an occurrence number intentionally narrows the edit.
SUBSTITUTE returns a new text result and leaves the original cell alone. That makes it suitable for a cleanup column where you can review the output before using it elsewhere. If you need to replace the original values permanently, copy the cleaned results and use Paste Special > Values, taking care not to overwrite data you still need.
Show a quote in a formula result
To include a visible quote in formula-generated text, double the quote inside the text string. With no cell references needed, ="She said ""Hello""" returns She said "Hello". The first and last quote marks delimit the formula’s text. Each doubled pair inside that string produces a single quote mark in the displayed result.
This pattern is handy when joining a label and a value. For example, with a person’s name in A2, =""""&A2&"""" displays that name surrounded by double quotes. If A2 contains Maya, the result is "Maya". The formula begins and ends with text that each contributes one quote character, while the ampersands join those pieces to the cell value.
Excel formulas use straight double quotation marks as syntax. If you copy a formula from a word processor, email, or formatted web page, the marks may have been converted to curly “smart quotes.” Those are different characters, and Excel may reject the formula or treat the text differently. When entering formulas, use the straight quote key on the keyboard.
Use Find and Replace for a one-time edit
If you want to alter the actual cell contents rather than create a formula result, Excel’s Find and Replace can be quicker. On Excel for Microsoft 365 on Windows, press Ctrl+H to open Replace. Enter one straight double quote in Find what, leave Replace with empty to remove quotes, and choose Replace All. To change quotes to another character, type that character in Replace with instead.
Before choosing Replace All, consider whether the quotes are meaningful. A quote might mark dialogue, a product specification, or a phrase that needs to remain quoted. You can first select the relevant range so the edit is limited to those cells, and review the replacements Excel reports. Check the search options if the results do not match what you expect, including whether Excel is searching within the current sheet or the whole workbook.
On a Mac, the Replace command is also available from Edit > Find > Replace; the keyboard shortcut can differ by keyboard and Excel version. In Excel for the web, use the Find and Replace option available from the Home tab. Menu wording and available search scope can vary slightly between versions, so verify the selected range and workbook scope before replacing all matches.
Know when quotes are part of imported data
Quotes often appear in CSV files because the comma-separated values format uses quotes to enclose fields that contain commas, line breaks, or other special content. A properly configured CSV import normally treats those marks as field delimiters rather than ordinary characters in the resulting cell. For example, a field saved as "Smith, Jordan" may be imported as Smith, Jordan, with the enclosing marks removed while the comma stays within the field.
However, quotes can remain when a file is malformed, when quote characters are part of the actual field, or when the file has been opened or copied in a way that treats its contents as plain text. Before stripping them, check a few original records and determine whether the marks are data or merely CSV structure. Removing meaningful quotes from every row can alter the intended text.
Also distinguish straight quotes from curly quotation marks. CHAR(34) targets the straight character only; it will not remove the left and right curly marks often inserted by word-processing software. One way to identify them is to copy a sample into Find and Replace and search for each visible curly character separately. In Excel versions supporting UNICHAR, U+201C and U+201D can be represented as UNICHAR(8220) and UNICHAR(8221), respectively.
For example, with text in A2, =SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8220),""),UNICHAR(8221),"") removes both left and right curly quotation marks. If A2 contains “Hello”, it returns Hello. This formula is specifically for those curly Unicode characters, not ordinary straight quotes.
Common problems and how to avoid them
- Confusing a blank string with a space: In
SUBSTITUTE,""means no characters. A single space between quotes," ", replaces each match with a space instead, which can leave extra spacing in the result. - Using one quote where Excel needs two: To display a literal quote inside formula text, double it. If Excel reports a formula error, check that the text string starts and ends correctly and that the quote inside it is doubled.
- Cleaning only part of a column: A formula entered in one row does not automatically clean every existing record. Fill it down through the data, and confirm the range includes the last row.
- Expecting the formula to edit its source: SUBSTITUTE produces a result in the formula cell. It does not alter A2. Use Find and Replace for a direct edit, or copy formula results and paste values when you need a permanent replacement.
- Removing the wrong kind of quote: Straight and curly quotes are separate characters. If the formula returns unchanged text, inspect the original mark closely or paste it into Find and Replace to search for the exact character.
For a large workbook, formulas are often safer during review because the original values remain available for comparison. For a small, confirmed one-time cleanup, Find and Replace is faster. In either case, save a copy first if the source data is difficult to recreate, and inspect a sample of results before using them in reports or exports.
Frequently Asked Questions
How do I type a double quote in an Excel formula?
Use two consecutive straight double quotes inside a text string to display one literal quote. For example, ="A ""quote"" here" returns A "quote" here.
How do I remove all double quotes from a cell?
If the text is in A2, use =SUBSTITUTE(A2,CHAR(34),""). It returns the text with every straight double quote removed, while leaving the original cell unchanged.
Can I use SUBSTITUTE to replace double quotes with spaces?
Yes. With text in A2, =SUBSTITUTE(A2,CHAR(34)," ") replaces each straight quote with one space. Check the result for doubled spaces if quotes occur next to each other.
Why does SUBSTITUTE not remove the quotation marks?
The marks may be curly rather than straight, or the formula may refer to the wrong cell. Check the character in the source and whether A2 is actually the cell containing the text. A straight quote and a curly quote are not interchangeable.
Does Find and Replace remove quotes from formulas too?
It can search formulas as well as displayed values depending on the search settings. Be cautious when replacing across a workbook: a quote character may be part of formula syntax. Limit the selected range and inspect the search options before using Replace All.
Related
- Excel Shortcut to Capitalize All Letters in a Cell?
- Excel Sheet Keyboard Shortcuts: Essential Keys and Tips
- Excel Shopping List Template: Create, Organize, and Print
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula to Pull Out Text Between Two CharactersTo pull text out from between two characters in Excel, use MID with FIND. If A2 contains Order…
- Excel Formula for Week of the Year: WEEKNUM vs. ISO WeeksTo get a week number from a date in Excel, use =WEEKNUM(A2,2) for weeks that start on Monday,…
- How to Sort an Excel PivotTable Largest to SmallestTo sort an Excel PivotTable from largest to smallest, right-click a number in the value column you want…
- INDIRECT Excel Function: Examples, Uses, and LimitsIn Excel, INDIRECT converts text into a live cell or range reference; if A2 contains B5, =INDIRECT(A2) returns…
- How to Multiply Hours in Excel: Formulas and FormattingTo multiply a duration by a factor in Excel, enter the duration in A2 and the factor in…
- SUMPRODUCT in Excel: Multiple Criteria ExplainedYes. In Excel, SUMPRODUCT can sum values that meet multiple criteria by multiplying TRUE/FALSE tests together; for example,…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.