Function Problems

How to Extract All Text in Square Brackets Using Excel Formulas

Elise Williams Sep 30, 2026 869 views

Question details

The user needs a formula to extract all phrases enclosed in square brackets from a single cell containing text with a variable number of bracketed values.

How to Extract All Text in Square Brackets Using Excel Formulas

Product: Microsoft Excel (Microsoft 365)
Device & OS: not provided
Scenario: Extracting specific bracketed data strings from a larger text block in a spreadsheet without manually splitting the text.
Observed behavior: The user requires a dynamic extraction of an unknown number of bracketed values into a single joined result.

Before you start

Ensure you are using a recent version of your spreadsheet software (such as Microsoft 365), as this method relies on modern dynamic array functions like TEXTSPLIT and VSTACK which are not available in older versions.

Solution 1Recommended

Use Dynamic Array Functions to Extract Bracketed Text

Utilize a combination of TEXTSPLIT, REDUCE, and TEXTJOIN to dynamically extract and combine all texts inside square brackets from a source cell.

This approach uses modern dynamic array capabilities to evaluate the text, split it at the bracket boundaries, isolate the enclosed words, and stitch them back together into a single string.

Select the target cell

Click on the empty cell where you want the extracted bracketed text to appear (for example, cell B2).

Enter the dynamic array formula

Assuming your source text is in cell A2, type the following formula exactly: =TEXTJOIN(" ",TRUE,REDUCE("",SEQUENCE(INT(COUNTA(TEXTSPLIT(A2,{"[","]"}))/2),,2,2),LAMBDA(a,i,VSTACK(a,"["&INDEX(TEXTSPLIT(A2,{"[","]"}),,i)&"]"))))

Execute the formula

Press the Enter key. The formula will automatically split the text at the brackets, select the bracketed segments, and join them into one continuous result.

Use Dynamic Array Functions to Extract Bracketed Text

Troubleshooting #NAME? Error: If the formula returns a #NAME? error, it indicates that the required TEXTSPLIT, REDUCE, or VSTACK functions are not available in your current spreadsheet software version.

Extract Data Easily with WPS Spreadsheet

Efficient Data Extraction and Formula Management with WPS Spreadsheet

WPS Office provides robust support for advanced formulas and data processing tasks. You can seamlessly work with complex text extraction formulas and enjoy high compatibility with standard Excel workbooks.

1. Open your file in WPS: Launch WPS Office and open the spreadsheet containing the text data you need to process.
2. Apply the extraction formula: Select an empty cell next to your data and paste your dynamic text extraction formula.
3. Execute and review: Press Enter to instantly process the text and extract all bracketed segments accurately.

Supports advanced data processing and modern formulasHighly compatible with Microsoft Excel formats (.xlsx)Free, lightweight, and user-friendly interfaceBuilt-in advanced data extraction and manipulation tools

microsoft office alternative - wps office

Frequently Asked Questions

Why does my formula return a #NAME? error?

This error occurs when your spreadsheet software does not support the newer functions used in the formula, such as TEXTSPLIT, REDUCE, or LAMBDA. You need a modern spreadsheet version like Microsoft 365 to use these specific functions.

Can I extract text from parentheses instead of square brackets?

Yes. You can modify the formula by replacing the square brackets {"[","]"} inside the TEXTSPLIT and VSTACK function arguments with parentheses {"(",")"}.

How can I extract the text without keeping the brackets in the final result?

To remove the brackets from the final output, modify the VSTACK section of the formula to omit the concatenation of "[" and "]". You would only return the INDEX result directly.

Excel & Data Problems