WPS Spreadsheet: Extract a Named Range or Reference from an Excel Formula
Question details
The user needs to extract the named range or bracketed reference written in a formula rather than return the calculated value from the referenced cell.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- A formula cell such as L12 contains a reference inside brackets, and the user wants to display the reference text, such as "Distance Spreading (miles)", instead of its result, such as 28.1.
- Observed behavior
- The formula returns the calculated value from the referenced cell instead of the text inside the relevant brackets.
Identify the cell that contains the source formula, such as L12, and confirm that the reference you want to extract is enclosed in square brackets. Check which bracketed occurrence you need if the formula contains multiple references.
Use FORMULATEXT, TEXTAFTER, and TEXTBEFORE to extract the bracketed reference
Use this method when the target name or reference appears inside square brackets in a formula and you need its text rather than the calculated result.
FORMULATEXT reads the formula as text, while TEXTAFTER and TEXTBEFORE isolate the characters between the selected brackets. In this example, the second opening bracket is used because the desired reference is the second bracketed item.
In WPS Spreadsheet, click the cell where you want the extracted name or reference to appear.
Enter =TEXTBEFORE(TEXTAFTER(FORMULATEXT(L12),"[",2),"]",1), replacing L12 with the cell that contains your source formula.
Press Enter. WPS Spreadsheet reads the formula in L12 and returns the text between its second pair of square brackets instead of the referenced cell's calculated value.
If the target is the first or another bracketed reference, change the 2 in TEXTAFTER to the matching opening-bracket occurrence number.
Extract Formula References Directly in WPS Spreadsheet
WPS Spreadsheet can read a formula as text and isolate a named range or bracketed reference with FORMULATEXT, TEXTAFTER, and TEXTBEFORE. The familiar spreadsheet interface makes it easy to enter, adjust, and reuse the extraction formula.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheet, and open the workbook containing the formula.
- 2. Choose an output cell: Click an empty cell beside the source formula where the extracted reference should appear.
- 3. Enter the formula: Type =TEXTBEFORE(TEXTAFTER(FORMULATEXT(L12),"[",2),"]",1), changing L12 and the occurrence number as needed.
- 4. Review and reuse the result: Press Enter to display the extracted text, then copy the formula to other rows if the source-cell references should update relatively.
Frequently Asked Questions
Why does the original formula return 28.1 instead of the reference name?
A normal reference evaluates the referenced cell and returns its value. FORMULATEXT reads the formula itself as text, allowing TEXTAFTER and TEXTBEFORE to extract the name or reference written inside brackets.
What does the number 2 in TEXTAFTER do?
The 2 tells TEXTAFTER to search for the second opening square bracket. Change it to 1 for the first bracketed reference or to another occurrence number when the formula contains more references.
Which cell should replace L12 in the formula?
Replace L12 with the address of the cell that contains the formula you want to inspect. For example, if the source formula is in D8, use FORMULATEXT(D8).
Can this formula extract a reference that is not enclosed in square brackets?
The provided formula specifically extracts text between square brackets. If the formula uses a named range without brackets, adjust the text-extraction delimiters to match the characters surrounding that name.
