WPS Spreadsheets: Extract the Last Number from Text in a Cell
Question details
The user needs to extract the final measurement from a product description stored in a spreadsheet cell.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A product description in cell C3 contains multiple measurements separated by commas, with the required measurement appearing at the end of the relevant text segment and enclosed in quotation marks.
- Observed behavior
- The final measurement, such as 60, needs to be returned as a numeric value without its quotation mark.
Confirm that the source description is in cell C3 and that the target measurement is in the second comma-separated segment. Check that the final value is separated from the surrounding text by a space and ends with a quotation mark.
Use LET and TEXTSPLIT to extract the final measurement
Use this formula when the target value is the last space-separated item in the second comma-separated segment of the text.
The formula separates the content at the comma, selects the relevant segment, splits that segment into space-separated parts, removes the quotation mark from the final part, and converts the result to a number.
Open the worksheet, select the cell where you want the extracted number to appear, and make sure the source text is in C3.
In the selected cell, enter =LET(parts,TEXTSPLIT(INDEX(TEXTSPLIT(C3,","),2)," "),--SUBSTITUTE(TAKE(parts,,-1),CHAR(34),"")) and press Enter.
Verify that WPS Spreadsheets returns the final measurement, such as 60, as a number without the quotation mark.
Extract the Final Measurement with WPS Spreadsheets
WPS Spreadsheets provides LET, TEXTSPLIT, TAKE, and SUBSTITUTE functions to isolate the final measurement from product text, remove quotation marks, and return a usable numeric value.
- 1. Open the workbook: Launch WPS Office, open the spreadsheet, and locate the product description in cell C3.
- 2. Choose the output cell: Click an empty cell next to the source data where the extracted measurement should appear.
- 3. Enter the formula: Type =LET(parts,TEXTSPLIT(INDEX(TEXTSPLIT(C3,","),2)," "),--SUBSTITUTE(TAKE(parts,,-1),CHAR(34),"")) into the formula bar and press Enter.
- 4. Fill the formula if needed: Drag the fill handle down to apply the formula to additional product descriptions, adjusting the source reference for each row.
Frequently Asked Questions
Which cell does the extraction formula read?
The formula reads the product description from cell C3. Replace C3 with the cell containing your own text if the source is elsewhere.
Why does the formula use INDEX with TEXTSPLIT?
TEXTSPLIT separates the description at commas, and INDEX selects the second comma-separated segment before the formula looks for its final space-separated item.
Can the result be used in calculations?
Yes. The double minus operator converts the cleaned text into a numeric value, so the extracted measurement can be added, averaged, or compared.
What does CHAR(34) remove from the result?
CHAR(34) represents a double quotation mark. SUBSTITUTE removes that mark from the final extracted value before converting it to a number.
What if my text has a different structure?
Adjust the comma segment selected by INDEX or the delimiter used by TEXTSPLIT to match your data. The formula works best when the target measurement is the final space-separated item in the selected segment.
