Skip to main content

Extract text between single or double quotes from cells in Excel

Author: Siluvia Last Modified: 2025-04-07

Extracting text from within single or double quotes in Excel cells is a common task that can pose challenges if you're not familiar with Excel's formula. This tutorial will guide you through several methods to simplify this process. Let's delve into these methods to see how they can help you extract quoted text efficiently and accurately.

a screenshot showing the original texts and the extracted quoted texts

Extract text between single or double quotes from cells with formulas

Using Excel formulas to extract text between quotes is a direct approach that utilizes functions such as MID and FIND. This method is ideal for those who prefer using native Excel capabilities. Please do as follows to get it done.

Select a blank cell you will place the extracted text. Type the below formulas and press "Enter". Then drag the AutoFill Handle down to get the rest of the quoted texts.

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)
use a formula to extract text between single quotes
Notes
  • If there is more than one quoted text in a cell, the formula extracts only the first quoted text.
  • If there is no quoted text in the cell, the formula will return the #VALUE! error
  • Explanation of this formula:
    • "FIND("'", A2)": Locates the position of the first single quote in the text.
    • "FIND("'", A2) + 1": Determine the starting position of the text to be extracted, which is the character immediately after the first single quote.
    • "FIND("'", A2, FIND("'", A2) + 1)": Locates the position of the second single quote in the text.
    • "FIND("'", A2, FIND("'", A2) + 1) - FIND("'", A2) - 1": Calculate the length of the text between the two single quotes by subtracting the position of the first single quote from the position of the second single quote and then subtracting 1
    • "MID": Extracts the text between the two quotes using the starting position and length calculated above.

Easily extract text between quotes with Kutools AI

"Kutools AI" is a feature within the "Kutools for Excel" add-on that simplifies many Excel tasks through automation. For extracting text between quotes, Kutools AI eliminates the need to remember or understand complex formulas.

After installing Kutools for Excel, go to the "Kutools AI" tab and then select "AI Aide" to open the "Kutools AI Aide" pane.

  1. Select the range of cells where you want to extract text between quotes.
  2. In the "Kutools AI Aide" pane, enter 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.
    send command to kutools ai
  3. "Kutools AI" then analyzes the command and provides a tailored solution. Please click the "Execute" button.
    click the execute button in kutools ai aide pane

Then all quoted texts are extracted from the selected range immediately.

a screenshot showing the original texts and the extracted quoted texts

Easily extract text between same or different characters with Kutools for Excel

Skip the hassle of writing complex formulas—"Kutools for Excel" lets you extract text between quotes with just a few clicks! Whether you're working with single quotes, double quotes, or any custom characters, Kutools makes the process effortless and error-free. Say goodbye to manual extraction and hello to a faster, smarter way to manage your data!

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Itegarate with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

After installing Kutools for Excel, select "Kutools" > "Formula Helper" > "Text" > "Extract strings between specified text" to access the feature.

  1. In the "Formula Helper" dialog box, configure as follows.
    1. Select the cell containing the quoted text you want to extract.
    2. Specify the start and end characters and click "OK".
      a screenshot of configuring the formula helper dialog box
  2. Drag the "AutoFill Handle" down to get the rest of the quoted texts in other cells.
    a screenshot showing the results

Extracting text between single or double quotes in Excel is a powerful skill that can save you time and improve data accuracy. By following the steps and tips in this guide, you'll be able to handle even the most complex text extraction tasks with ease. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials.

Related articles:

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

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!