Excel Extract Text Between Quotes with Formulas

Formulas & functions · Updated November 18, 2026

If the text is in A2, use =IFERROR(TEXTBEFORE(TEXTAFTER(A2,""""),""""),"") in Microsoft 365 or Excel 2024 to return the text between the first pair of straight double quotation marks. For older Excel versions, a formula using MID and FIND can do the same job.

What “between quotes” means

Excel stores the characters in a cell as text; quotation marks are not automatically treated as boundaries you can extract from. A formula must locate the opening quote, locate the next quote, and return the characters between those positions. For example, if A2 contains Status: "Ready to ship"; check again tomorrow, the desired result is Ready to ship.

The formula in the opening paragraph returns the first quoted passage it finds. It does not include the quotation marks themselves, and it does not return the text before or after them. This is often what people need when a cell combines a label and a value, such as a note, product description, or copied message.

“Quotes” can also mean curly quotation marks, single quotation marks, or several quoted passages in one cell. Those cases need a small adjustment or a different approach. Before choosing a formula, check which characters your data actually contains and whether you want just the first passage or every passage.

How the modern formula works

With the source text in A2, the formula below uses TEXTAFTER to discard everything through the first straight double quote, then TEXTBEFORE to keep only the text up to the next one:

=IFERROR(TEXTBEFORE(TEXTAFTER(A2,""""),""""),"")

Excel formula text has a special rule: to represent one literal double-quote character inside a quoted text argument, type two double quotes. That is why the delimiter appears as four quote characters in the formula. The outer pair marks the text argument, and the doubled pair represents the actual quote Excel should find.

TEXTAFTER and TEXTBEFORE are available in Microsoft 365 and Excel 2024. IFERROR makes the result blank if Excel cannot find a usable pair, instead of displaying an error such as #N/A. This is convenient for a column that may contain a mix of records with and without quoted text. If you would rather see an error and investigate missing quotes, remove IFERROR and its final blank-result argument.

For the example above, the formula returns Ready to ship. If the cell reads Customer said "call after 4" and left a message, it returns call after 4. It starts at the first quote in the cell, so a quote used earlier for another purpose can change the result.

Formula for older Excel versions

If your Excel does not include TEXTBEFORE and TEXTAFTER, and the source is in A2, use MID and FIND. This version also returns the text between the first two straight double quotes:

=IFERROR(MID(A2,FIND("""",A2)+1,FIND("""",A2,FIND("""",A2)+1)-FIND("""",A2)-1),"")

FIND locates the first quote. The nested FIND begins searching one character after that position, so it locates the next quote rather than finding the same one again. MID starts one character after the opening quote and returns a number of characters equal to the distance between the two quotes, minus the quote characters themselves.

For Code "XJ-42" received in A2, the formula returns XJ-42. The arithmetic is based on character positions, not on the visible length of the extracted result: if the first quote is at position 6 and the second at position 12, MID starts at position 7 and returns 5 characters.

FIND is case-sensitive, but case is irrelevant when searching for a quotation mark. Its useful feature here is that the search can start at a chosen position. If your Excel uses semicolons instead of commas between formula arguments because of regional settings, replace the argument-separating commas with semicolons; the quote escaping remains the same.

Extracting more than one quoted passage

The formulas above return only the first passage. Suppose A2 contains Choose "small" or "large" for delivery. The first-pair formula returns small, not both choices. A cell can contain multiple quoted pairs, so decide whether you need one result per cell or a separate result for every pair.

With the text in A2, TEXTSPLIT can divide the cell at every straight double quote:

=TEXTSPLIT(A2,"""")

In Microsoft 365 or Excel 2024, the result spills into neighboring cells. For the example, the pieces are Choose , small, or , large, and for delivery. The quoted content alternates with the unquoted content when the cell begins with ordinary text. You can then use the appropriate inside-text pieces for your task. If the text starts with a quote, the first split piece may be blank, so inspect the spill rather than assuming the same column always holds the first passage.

TEXTSPLIT is useful for inspecting or separating a simple, consistently formatted string, but it does not understand a full quoted-text convention. If quote characters can occur inside a quoted passage, or if the data has rules for escaped quotes, splitting at every quote may produce misleading pieces. For structured files such as CSV, import the data with a suitable delimiter and text qualifier instead of treating every quote as a simple pair.

Using curly or single quotation marks

Text copied from a website or word processor may use curly marks instead of the straight quote character: “ready” rather than "ready". Excel sees those as different characters. A formula looking for straight quotes will not find curly ones, even though they look similar on screen.

If A2 contains text enclosed by a left curly quote and a right curly quote, use those exact characters as the delimiters. With the text in A2, this modern formula extracts the content between the first opening and closing curly marks:

=IFERROR(TEXTBEFORE(TEXTAFTER(A2,"“"),"”"),"")

The left and right marks are not interchangeable: the formula searches for an opening “ and then a closing ”. If the source uses straight single quotation marks, use a single quote as the delimiter instead. If you are unsure which character is present, copy one directly from the cell into the formula argument, or inspect the source text closely. Replacing curly punctuation with straight punctuation is another option, but do so only if changing the original text is acceptable.

Fill the formula down a column

For a list of records, put the source text in A2:A100 and place the extraction formula in B2. The formulas use a relative reference to A2, so Excel adjusts it to A3, A4, and so on when you fill down. In Windows Excel, select B2 and drag the fill handle at the lower-right corner of the cell, or double-click it to fill alongside a neighboring data column. On Mac, the fill handle works similarly; keyboard shortcut availability can vary by keyboard and Excel settings.

Another option is to select the destination range and use Ctrl+D on Windows to fill the top cell’s formula down. On Mac, use Control+D in Excel for Mac. Check the first few results before filling a large list, especially if some records have no closing quote or use a different punctuation style. The blank result from IFERROR can otherwise make an unexpected format look like a legitimate empty value.

These formulas return calculated results, not a permanent rewrite of the original cells. If you need to replace formulas with fixed text, copy the results and use Paste Special > Values. Keep the original column until you have checked the extracted data; that makes it easier to correct delimiter problems or change the extraction rule later.

Common errors and how to fix them

  • The result is blank. IFERROR hides problems by design. Confirm that the cell contains the exact opening and closing characters the formula searches for. Temporarily remove IFERROR to reveal the underlying error.
  • The formula returns the wrong passage. The formula takes the first pair it encounters. If the cell contains an earlier quote in a label or abbreviation, that quote may be treated as the opening delimiter. Check the full cell contents and decide which pair is intended.
  • The formula shows #NAME?. Your Excel version may not support TEXTBEFORE or TEXTAFTER, or a function name may be misspelled. Use the MID and FIND version in an older version of Excel.
  • The formula is rejected when entered. Check that every opening parenthesis has a closing parenthesis, and that the quote delimiter is typed as four consecutive double-quote characters in the formula. In some regional settings, argument separators are semicolons rather than commas.
  • The output includes unexpected pieces. The source may contain curly quotes, apostrophes, or escaped quotes. A formula finds characters literally; it does not infer which marks are intended as punctuation and which are part of the content.

A useful check is to test the formula on a few deliberately varied examples: a normal pair, a cell with no quotes, a cell with two pairs, and a cell with curly marks. This quickly shows whether the formula matches the actual data pattern before you rely on it across an entire worksheet.

Choosing a formula or another method

Use TEXTBEFORE and TEXTAFTER when you have Microsoft 365 or Excel 2024 and want a compact formula. Choose MID and FIND when compatibility with older Excel workbooks matters. Use TEXTSPLIT when the goal is to break a simple cell into alternating pieces, rather than return just the first quoted value.

For a one-time cleanup of a large imported dataset, Power Query may be more appropriate than filling formulas down. It can transform a column during the import or query process, and the transformation can be refreshed when the source data changes. The exact steps depend on the shape of the input, especially when quotes follow CSV-style rules or occur inside the values themselves.

For a small worksheet, a formula is usually easy to audit: the original text remains intact, and each result updates when its source cell changes. For complex or inconsistent text, first define what counts as a valid pair, how escaped quote marks should behave, and what should happen when a pair is incomplete. No basic first-pair formula can make those decisions reliably without a clear rule.

Frequently Asked Questions

Can Excel extract text between two different characters?
Yes. Replace the quote delimiters with the characters you need, such as parentheses or brackets. In TEXTAFTER and TEXTBEFORE, each delimiter must be entered as a text argument; use the MID and FIND approach if those newer functions are unavailable.

How do I extract text between the second pair of quotes?
The first-pair formulas stop at the first passage. For simple strings with several pairs, split the text at every quote with TEXTSPLIT and inspect the alternating pieces. If records have inconsistent formatting or embedded quotes, define the parsing rules first rather than relying on a fixed piece position.

Why does Excel need four quotation marks in the formula?
Inside an Excel formula, a pair of double quotes represents one literal double-quote character within a text string. The text argument therefore has an opening quote, a doubled literal quote, and a closing quote: four quote characters altogether.

Can I extract text between curly quotation marks?
Yes, provided the formula uses the same left and right curly characters that appear in the cell. Replace the straight-quote delimiters with “ and ”, or copy the punctuation from the source text to avoid confusing curly and straight marks.

What happens if a closing quote is missing?
The extraction formula cannot identify a complete pair and normally returns an error. Wrapping it in IFERROR can display a blank instead, but that may conceal malformed data. Remove the wrapper while checking the source if you need to find incomplete records.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.