I would want to return Smith or California
How to extract text between single or double quotes from cells in Excel?
In many cases, people tend to embrace some important contents with single or double quotes in Excel. For using this important text, you need to extract them between single or double quotes from cells firstly. How to achieve it? The method in this article will help you extract only text between single or double quotes from cells in Excel.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Please apply the below formula to extract text between single quotes from cells in Excel.
1. Select a blank cell you will place the extracted text. Type formula =MID(A2,FIND("'",A2)+1,FIND("'",A2,FIND("'",A2)+1)-FIND("'",A2)-1) into the Formula Bar, and then press the Enter key. See screenshot:
1). In the formula, A2 is the cell you will extract text from. Please change it as you need.
2). For extracting text between double quotes, please apply this formula =MID(A2,FIND("""",A2)+1,FIND("""",A2,FIND("""",A2)+1)-FIND("""",A2)-1).
2. Select the resulting cell, drag the Fill Handle to apply the formula to other cells. Then you can see all texts between single quotes are extracted immediately as below screenshot shown.
- How to extract text between the second and third comma from cells in Excel?
- How to extract numbers from mixed alphanumeric text strings in Excel?