Function Problems

Extract the First and Last Numbers from Text in Excel

Aamir Naveed AkramAamir Naveed Akram Nov 18, 2026 869 views

Question details

Extract the first and last numeric values from a text string such as “462 box 0 pcs.”

Extract the First and Last Numbers from Text in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Extract the first value

Select F4, enter =TEXTBEFORE(E4," "), and press Enter. For “462 box 0 pcs,” the result is 462.

2
Extract the pieces value

Select G4, enter =TEXTBEFORE(TEXTAFTER(E4," ",-2)," "), and press Enter. The result is 0.

3
Apply the formulas to other rows

Select F4 and G4, then drag the fill handle down alongside the source data.

Use TEXTBEFORE and TEXTAFTER in Excel 365
Results are text: Wrap either formula in VALUE(...) if you need a numeric result for calculations. These formulas assume single spaces between the four parts.
Extract values in WPS Spreadsheet

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. 1. Open the workbook: Open WPS Spreadsheet and use Open to select your Excel workbook.
  2. 2. Extract the first quantity: With the source text in E4, select F4 and enter =LEFT(E4,FIND(" ",E4)-1).
  3. 3. Extract the pieces value: Select G4 and enter =MID(E4,FIND("box ",E4)+4,FIND(" pcs",E4)-FIND("box ",E4)-4).
  4. 4. Fill and save: Drag the fill handle down for additional rows, then choose Save to keep the formulas in your workbook.
Extract quantities from labeled text into separate cells.Open and edit Microsoft Excel XLSX and XLS files.Use familiar spreadsheet formulas and fill handles.Work with a free, easy-to-use office suite.
microsoft office alternative - wps office

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.