Extract the First and Last Numbers from Text in Excel
Question details
Extract the first and last numeric values from a text string such as “462 box 0 pcs.”

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet cell contains a quantity, a text label, a pieces value, and a unit label.
- Observed behavior
- The user wants the first quantity and the pieces value in separate cells.
Check that the source text follows the pattern “462 box 0 pcs” with single spaces, and identify its cell address. The examples below use E4; replace it with your source cell if needed.
Use TEXTBEFORE and TEXTAFTER in Excel 365
Use modern text functions to extract both values when the text has the same four-part structure as the example.
Select F4, enter =TEXTBEFORE(E4," "), and press Enter. For “462 box 0 pcs,” the result is 462.
Select G4, enter =TEXTBEFORE(TEXTAFTER(E4," ",-2)," "), and press Enter. The result is 0.
Select F4 and G4, then drag the fill handle down alongside the source data.

Use LEFT, MID, and FIND in older Excel versions
Use traditional functions when TEXTBEFORE and TEXTAFTER are unavailable and the labels are “box” and “pcs.”
Extract Numbers from Text with WPS Spreadsheet
Open your workbook in WPS Spreadsheet and use LEFT, MID, and FIND formulas to separate the first quantity and pieces value.
- 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
- 2. Extract the first quantity: With the source text in E4, select F4 and enter =LEFT(E4,FIND(" ",E4)-1).
- 3. Extract the pieces value: Select G4 and enter =MID(E4,FIND("box ",E4)+4,FIND(" pcs",E4)-FIND("box ",E4)-4).
- 4. Fill and save: Drag the fill handle down for additional rows, then choose Save to keep the formulas in your workbook.

Frequently Asked Questions
How do I turn the extracted text into a number?
Wrap the extraction formula in VALUE. For example, use =VALUE(TEXTBEFORE(E4," ")) in Excel 365 to return a numeric value.
Why does the formula return an error?
Check that E4 contains the expected spaces and labels. The traditional pieces formula requires “box ” and “ pcs”; the modern formula expects four parts separated by single spaces.
Can I use these formulas if my labels are different?
Yes. In the MID and FIND formula, replace “box ” and “ pcs” with the markers surrounding your pieces value, and adjust the character offset if the first marker has a different length.
Do the formulas find the last number in any text string?
No. The examples are designed for a consistent pattern like “462 box 0 pcs,” where the last number appears just before the unit label. Other layouts may need a different formula.




