How to Extract Text from the Last Square Bracket in Excel
Question details
The user needs an Excel formula to extract the string located inside the final set of square brackets within a cell containing multiple bracketed texts.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Data cleaning or text manipulation where specific data is trapped inside the last pair of brackets in a messy text string.
- Observed behavior
- The user requires a formula that accurately identifies the last opening and closing square brackets and returns only the text between them.
Ensure your dataset is backed up before applying complex text extraction formulas. Verify that the cells you are targeting actually contain at least one set of square brackets to avoid formula calculation errors.
Using the TRIM, SUBSTITUTE, and REPT Formula
This highly effective formula replaces the target brackets with large spaces, making it easy to isolate the last bracketed section regardless of string length.
This solution works seamlessly across all Excel versions without needing the latest dynamic array functions. By using REPT to add 255 spaces where the brackets are, RIGHT and LEFT functions can easily grab the desired segment.
Click on an empty cell adjacent to your data where you want the extracted text to appear.
Type the formula: =TRIM(LEFT(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(G2,"[",REPT(" ",255)),255)),"]",REPT(" ",255)),255)) replacing 'G2' with your actual cell reference.
Press Enter to execute the formula and view the extracted text.
Click and hold the small green square at the bottom right corner of the cell, then drag the fill handle down to apply this formula to other cells in your column.
Using the FIND and SUBSTITUTE Method
An alternative approach that identifies the position of the last bracket by substituting it with a unique character before extracting the text.
Easily Extract Text with WPS Spreadsheet
WPS Office Spreadsheet fully supports advanced text formulas like TRIM, SUBSTITUTE, LEFT, and RIGHT, making it simple to process complex strings and clean your data efficiently without buying expensive software.
- 1. Download and Install: Download and install WPS Office for free from the official website.
- 2. Open your workbook: Open your existing Excel workbook (.xlsx or .xls) directly in WPS Spreadsheet.
- 3. Input the formula: Select the target cell and input the exact text extraction formula (=TRIM(LEFT(...))) just as you would in Microsoft Excel.
- 4. Apply and drag: Press Enter to extract the text, and drag the fill handle down to apply the extraction across your entire dataset.
Frequently Asked Questions
Why does my formula return an error if there are no brackets in the cell?
Text extraction formulas look specifically for the '[' and ']' characters. If they are missing, the FIND or SUBSTITUTE functions cannot locate them, resulting in a #VALUE! error. You can fix this by wrapping your entire formula in an IFERROR function, like =IFERROR(your_formula, ""), to return a blank cell instead of an error.
Can I modify the formula to extract text from regular parentheses instead?
Yes. Simply edit the formula and replace the square brackets '[' and ']' with regular parentheses '(' and ')'. The logical steps for isolating the last set of characters will remain exactly the same.
Is there a simpler way to do this in newer versions of Excel?
In Microsoft 365, you can use modern text functions like TEXTAFTER and TEXTBEFORE. To extract text inside the last set of square brackets, you can use a formula combination relying on TEXTAFTER(G2, "[", -1) to grab everything after the last opening bracket.
