Extract text between single or double quotes from cells in Excel
Extracting text from within single or double quotes in Excel cells is a common task, especially in data-cleaning, processing database exports, or preparing lists populated by quoted text. This kind of extraction is not directly available in Excel's default features, which can make it a bit challenging if you're not familiar with advanced Excel functions or automation tools. The ability to extract quoted text helps in streamlining data for reporting, transforming imported data, or converting textual content into structured information for further analysis.
This tutorial will guide you through several practical and user-friendly methods to extract text between single or double quotes from Excel cells. We discuss solutions using native formulas, Kutools for Excel's advanced features—including both its AI Aide and "Extract Strings Between Specified Text" tool—as well as a powerful VBA code approach for extracting all quoted text instances, even handling multiple or nested quotes. Each method comes with scenario explanations, step-by-step directions, and tips to help you avoid common errors.
- Extract text between quotes with formulas
- Easily extract text between quotes with Kutools AI
- Easily extract text between same or different characters with Kutools for Excel
- VBA code – Extract all quoted texts or handle complex quote scenarios
Extract text between single or double quotes from cells with formulas
Using Excel formulas to extract text between quotes is a direct and transparent approach that leverages functions such as MID and FIND. This approach is well suited for those who prefer to work with standard Excel features and require quick results for cells containing a predictable, single pair of quotes. However, formulas work best in simple cases—when there is only one quoted section per cell and the quotes do not overlap or nest.
To apply this solution:
Select a blank cell where you want the extracted text to appear. Enter the appropriate formula provided below and press "Enter". You can then drag the AutoFill Handle down to extract from other rows as well. Make sure that the referenced cell (for example, A2 in these formulas) matches the position of your data.
Extract text between single quotes
=MID(A2,FIND("'",A2)+1,FIND("'",A2,FIND("'",A2)+1)-FIND("'",A2)-1)Extract text between double quotes
=MID(A2,FIND("""",A2)+1,FIND("""",A2,FIND("""",A2)+1)-FIND("""",A2)-1)
- This formula extracts only the first quoted text found in the cell. If your cell contains multiple quoted sections, only the first occurrence will be extracted.
- If the referenced cell does not contain the specified quote character, the formula will return the #VALUE! error. To prevent formula errors or handle blanks, consider wrapping the formula in an IFERROR() function if needed.
- Formula explanation:
FIND("'", A2): Finds the position of the first single quote in the text.FIND("'", A2) +1: Sets the starting position for extraction, just after the first single quote.FIND("'", A2, FIND("'", A2) +1): Finds the position of the next single quote after the first, used as the end boundary.MID(...): Returns the characters found between the two quote marks based on the start and length calculated above.
Tip: For cells without quotes, consider using =IFERROR(formula, "") to display a blank cell instead of an error.
Easily extract text between quotes with Kutools AI
Kutools AI is an intelligent assistant within the "Kutools for Excel" add-in, designed to automate many Excel operations that would otherwise require complex formulas or manual manipulation. For those who want to quickly and reliably extract values between quotes—including handling larger ranges or irregular data—the AI feature provides an easy alternative, especially helpful if you're unfamiliar with Excel formulas.
After installing Kutools for Excel, simply navigate to the "Kutools AI" tab and select "AI Aide" to open the execution pane on the right side of Excel.
- Select the group of cells from which you want to extract text between quotes. You can select one column, multiple rows, or even a custom cell range according to your needs.
- In the "Kutools AI Aide" pane, type the following command in the edit box and press "Enter".Please perform the following action: Extract values between single quotes in the selected range and place in adjacent cells.
- Kutools AI will analyze your command and automatically identify the suitable extraction method for your task. When ready, simply click the "Execute" button provided in the pane.
Within moments, the quoted texts will be extracted into adjacent cells, with no need to write formulas or deal with manual copying. This method is especially advantageous for batch processing and varied quote scenarios.
Tips: If your text uses double quotes or custom characters, just adjust your prompt accordingly (for example, "Extract values between double quotes ...") for accurate results. Double-check the results, especially if your data includes irregular characters or mismatched quotes.
Easily extract text between the same or different characters with Kutools for Excel
Working with data that contains not only quotes but also other unique delimiters? Skip the hassle of writing complex formulas—"Kutools for Excel" makes it possible to extract text between single quotes, double quotes, or even customized characters with just a few straightforward clicks. This is particularly beneficial for non-technical users or anyone who needs to extract from multiple, varied columns or formats in bulk. The interface is intuitive, reducing the risk of formula errors and making your workflow more efficient.
After installing Kutools for Excel, access the function by navigating to Kutools > Formula Helper > Text > Extract strings between specified text.
- In the "Formula Helper" dialog, configure as follows:
- Choose your target cell containing the quoted or delimited text.
- Define your start and end characters—such as ' or " as needed—and then click "OK". (You can specify any character or set of characters, not limited to quotes.)
- Drag the AutoFill Handle downward to apply the extraction to the rest of the list, efficiently processing entire columns or ranges.
Advantages: This method does not require knowledge of formulas, reduces the chance of common errors, and can handle a wide range of start/end delimiters. However, for more advanced tasks such as extracting multiple quoted sections within the same cell, consider the VBA or AI solution described below.
Extracting text between single or double quotes in Excel is a practical and highly valuable skill, aiding in data organization and preparation for further processing. By following the approaches outlined above, you can address both simple and moderately complex extraction scenarios. For more advanced needs—such as handling multiple or nested quotes per cell—see the VBA-based method that follows. If you want to enhance your Excel proficiency further, our website offers thousands of easy-to-follow tutorials.
VBA code – Extract all quoted texts or handle complex quote scenarios
For situations where your data includes multiple quoted entries within the same cell, or presents complex patterns such as nested or inconsistent quote usage, Excel's built-in formulas and even standard text tools may not suffice. In such cases, a VBA (macro) solution offers unmatched flexibility. Using VBA, you can extract all text fragments found between single or double quotes in each cell—even retrieving several matches per cell or dealing with tricky combinations of quotes.
This approach is especially useful for advanced users, data analysts, or anyone frequently confronted with irregularly structured textual data. Using a macro enables you to automate repetitive extraction and guarantees consistency across large datasets.
How to use:
1. Open your workbook and press Alt + F11 to bring up the Microsoft Visual Basic for Applications editor. In the menu, click Insert > Module to create a new module. Copy and paste the following code into the module window:
Sub ExtractAllQuotedText()
Dim rng As Range
Dim cell As Range
Dim matches As Object
Dim regEx As Object
Dim outputCol As Long
Dim symbol As String
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set rng = Application.Selection
Set rng = Application.InputBox("Select range to extract from", xTitleId, rng.Address, Type:=8)
symbol = Application.InputBox("Extract between single (') or double ("") quotes? Enter ' or """, xTitleId, "'")
If symbol = "'" Then
Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.Pattern = "'([^']*)'"
ElseIf symbol = """" Then
Set regEx = CreateObject("VBScript.RegExp")
regEx.Global = True
regEx.Pattern = Chr(34) & "([^" & Chr(34) & "]*)" & Chr(34)
Else
MsgBox "Symbol must be single (') or double ("") quote!", vbCritical
Exit Sub
End If
outputCol = rng.Columns(rng.Columns.Count).Column + 1 ' Output starting in next column
For Each cell In rng
Set matches = regEx.Execute(cell.Text)
Dim resultArr() As String
ReDim resultArr(matches.Count - 1)
Dim i As Integer
For i = 0 To matches.Count - 1
resultArr(i) = matches(i).SubMatches(0)
Next i
If matches.Count > 0 Then
cell.Worksheet.Cells(cell.Row, outputCol).Value = Join(resultArr, ", ")
Else
cell.Worksheet.Cells(cell.Row, outputCol).Value = ""
End If
Next cell
MsgBox "Extraction complete! Results are in the adjacent column.", vbInformation
End Sub2. Close the VBA editor and return to your worksheet (press Alt + F11 again).
3. Select the cell range with your data, then press the F5 key or click Run.
4. You'll be prompted to select the range if you haven't already, and then to choose between extracting text from single (') or double (") quotes. Enter ' or " as appropriate.
5. The macro extracts all quoted sections from each selected cell—regardless of how many there are—and places a comma-separated list of extractions into the next empty column to the right of your data.
Notes and troubleshooting:
- If your data contains both single and double-quoted text and you want to extract both, run the macro twice—once for each quote type.
- The macro skips empty cells and those without any valid quoted text.
- For unusual quote or nested quote scenarios, the pattern is designed to extract non-nested quoted strings; further enhancements are possible for handling deeper nesting, though such patterns are rare in normal Excel text.
- If nothing is extracted, double-check your quote characters and ensure no formatting or hidden characters interfere.
Summary tip: Choose the solution best fitting your data structure. For basic one-off extractions, formulas will suffice. For batch tasks with flexible boundaries and non-formula users, Kutools for Excel or Kutools AI is easiest. Use VBA for the most advanced requirements, such as extracting all matches or dealing with irregular quote usage.
Related articles:
- How to extract text between the second and third commas from cells in Excel?
- How to extract numbers from mixed alphanumeric text strings in Excel?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in