Cellwise Try it free

Excel guide · 3 min read

How to extract numbers from a string in Excel

Quick answerIf the number always sits next to the same character, cut it out and convert it: =VALUE(TEXTAFTER(A2,"-")) turns "INV-2041" into 2041. For messy text, type the first number in the next column and press Ctrl+E to Flash Fill the rest.

Sample data

Every result on this page was calculated from this sheet by the same spreadsheet engine the Cellwise app uses.

ABC
1InvoiceWeightNote
2INV-204112.5 kgBox 7 of 12
3INV-20428 kgRack A3, shelf 14
4INV-210715.25 kgOrder 448 rushed
5INV-21103 kgCustomer ref 90213

Pick the method by how tidy the text is

If every cell follows the same pattern, such as a prefix and a dash, or a number and a unit, a simple split works and stays accurate. If the numbers are scattered through free text, use Flash Fill or a heavier formula, and always check the results.

Numbers after a prefix: TEXTAFTER

TEXTAFTER (Excel 365 and 2024) returns the text after the dash. That is still text, so wrap it in VALUE to get a real number you can sort and sum.

In older Excel, =VALUE(MID(A2,FIND("-",A2)+1,100)) does the same.

=VALUE(TEXTAFTER(A2,"-"))

Result: 2041

Returns 2041 as a number.

Numbers before a unit: TEXTBEFORE

For values like "12.5 kg", take what comes before the space. Decimals survive because VALUE understands them.

In older Excel use =VALUE(LEFT(B2,FIND(" ",B2)-1)).

=VALUE(TEXTBEFORE(B4," "))

Result: 15.25

Returns 15.25.

Fixed length: strip the letters

If the non-number part is always the same text, SUBSTITUTE it away. Here every invoice starts with "INV-", so removing that leaves only the digits. The double minus converts the text result into a number, just like VALUE.

=--SUBSTITUTE(A4,"INV-","")

Result: 2107

Returns 2107.

Messy text: Flash Fill

For notes like "Order 448 rushed" the number moves around. Flash Fill (Excel 2013 and later) learns from examples. Give it two or three, especially for cells that hold more than one number, then check every row. The result is static text, not a formula.

  1. In D2, type the number you want from C2, for example 7.
  2. In D3, type 14 so Excel sees you want the last number in "Rack A3, shelf 14".
  3. Select D4 and press Ctrl+E, or go to Data > Flash Fill.
  4. Review the filled values. Correct any wrong row and Flash Fill updates the others.
  5. If you need numbers, convert the column with Data > Text to Columns > Finish.

Messy text: a formula in Excel 365

Excel 365 can test every character with a dynamic array: =TEXTJOIN("",TRUE,IFERROR(MID(C4,SEQUENCE(LEN(C4)),1)*1,"")). MID splits the text into single characters, *1 turns digits into numbers and letters into errors, IFERROR drops the errors, and TEXTJOIN glues the digits back together.

It joins every digit in the cell, so "Box 7 of 12" gives 712. Use it when each cell holds one number. Newer Microsoft 365 builds also have REGEXEXTRACT, where =REGEXEXTRACT(C4,"[0-9]+") returns the first run of digits.

Common mistakes

The result will not add up

Text functions return text. Wrap the result in VALUE or put -- in front.

Leading zeros disappear

Converting "00721" to a number gives 721. Skip VALUE for IDs, postcodes and account numbers.

Digits from two numbers merge

The digit-joining formula gives 712 for "Box 7 of 12". Use Flash Fill with examples, or split at a delimiter.

Flash Fill does not update

It fills values once. Run it again (Ctrl+E) after the source changes, or use a formula.

FAQ

Is there a function to extract numbers from text in Excel?

There is no single classic function. Use TEXTBEFORE, TEXTAFTER or MID for consistent patterns, Flash Fill for mixed text, or REGEXEXTRACT if your Microsoft 365 has it.

How do I extract numbers from the start of a string?

If a space follows the number, =VALUE(TEXTBEFORE(A2," ")) or =VALUE(LEFT(A2,FIND(" ",A2)-1)) returns it.

Why does Flash Fill not appear?

It may be switched off. Turn it on in File > Options > Advanced > "Automatically Flash Fill", or run it manually with Ctrl+E.

How do I keep decimals?

Split at a delimiter and use VALUE, which keeps the decimal point. The digit-joining formula drops the point, so it is not suited to decimals.

Functions used

Related guides

Updated