Function Problems

How to Extract Every Quoted Code From an Excel Cell

Huda Qurayshi Sep 30, 2026 870 views

Question details

The user needs to extract multiple strings of text enclosed in quotation marks from a single spreadsheet cell without relying on insecure external links or web service formulas.

How to Extract Every Quoted Code From an Excel Cell

Product: Excel
Device & OS: not provided
Scenario: Working on a professional spreadsheet containing multiple quoted department codes within single cells, requiring a secure and efficient local extraction method.
Observed behavior: Standard functions like TEXTBEFORE and TEXTAFTER only return the first matching value, while WEBSERVICE and external links present unacceptable security risks for work documents.

Before you start

Ensure you have a backup of your data and verify that your workbook is saved in a macro-enabled format (.xlsm) if you intend to use a VBA solution.

Solution 1Recommended

Use a VBA User-Defined Function (UDF)

Create a custom VBA macro using Regular Expressions to safely identify and extract all quoted text instances from a single cell.

A VBA User-Defined Function processes the data locally on your machine, eliminating the security risks associated with the WEBSERVICE function or external links.

By utilizing the VBScript.RegExp object, the macro can seamlessly search through a cell's contents and pull every string wrapped in quotation marks, regardless of how many exist in the cell.

Open the VBA Editor

Press ALT + F11 on your keyboard to open the Visual Basic for Applications (VBA) Editor.

Insert a New Module

In the top menu, click 'Insert' and then select 'Module' to create a blank script window.

Paste the RegExp Code

Write or paste a VBA script that utilizes 'VBScript.RegExp' with the pattern '""([^"]*)""' to match all instances of quoted text and join them into a single return string.

Apply the Custom Function

Close the VBA Editor, return to your spreadsheet, and type your new custom function (e.g., =ExtractQuotes(A1)) into the desired cell to display the extracted codes.

Security Advantage: Because this method processes everything entirely offline, it is fully compliant with standard data security protocols for confidential work spreadsheets.

Solution 2

Extract Using Power Query

Use the built-in Power Query editor to split your cell data by quotation mark delimiters, transforming the text safely without requiring code.

Efficient Spreadsheet Data Processing

Extract Complex Data Securely with WPS Spreadsheet

WPS Office provides robust built-in tools and full VBA (macro) support, allowing you to securely process complex cell data and extract quoted codes without relying on risky external web services.

1. Install WPS Office: Download and install the free WPS Office suite on your computer.
2. Open Your Spreadsheet: Launch WPS Spreadsheets and open the workbook containing the complex text cells.
3. Access the Developer Tools: Navigate to the 'Developer' tab on the ribbon and click 'Macros' or 'Visual Basic' to insert your extraction script.
4. Extract Your Codes: Apply your custom VBA formula to securely pull every quoted department code across your entire dataset.

Fully supports VBA macros for creating custom text extraction functions.Highly compatible with Microsoft Excel (.xlsx, .xlsm, .csv) formats.Ensures 100% offline data processing, ideal for confidential work documents.Lightweight installation with a familiar, easy-to-navigate interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why shouldn't I use the WEBSERVICE function for text extraction?

The WEBSERVICE function transmits your spreadsheet data to external URLs to process information. For work documents containing sensitive data like internal department codes, this creates a severe security and privacy vulnerability.

Can I use TEXTBEFORE and TEXTAFTER to get all quotes?

By default, the TEXTBEFORE and TEXTAFTER functions only return the first occurrence of a matched delimiter. While complex array formulas can be engineered to find subsequent matches, using a VBA User-Defined Function or Power Query is significantly more reliable when the number of quoted items is unknown.

Will my custom VBA function work if I share the file with colleagues?

Yes, but you must ensure the file is saved as a Macro-Enabled Workbook (.xlsm). Additionally, your colleagues will need to click 'Enable Content' or 'Enable Macros' when opening the file in their spreadsheet software to allow the custom extraction function to run.

Excel & Data Problems