Extracting Numbers from Text in Excel

How-to basics · Updated November 22, 2026

The fastest way to extract numbers from text in Excel is to use the VALUE function combined with MID and ROW for a dynamic array, or the simpler RIGHT and LEFT functions when the number is at a known position. For example, to pull the number from the end of the text in cell A1, use =VALUE(RIGHT(A1, LEN(A1)-MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A1&"0123456789"))+1)). This article explains several reliable methods, from simple formulas to Power Query, and helps you choose the right one for your data.

Why Extracting Numbers Can Be Tricky

Excel stores numbers as numeric values, but when a number appears inside a text string—like “Order 12345” or “SKU-AB-7890″—it is just a character. To use that number in calculations, you must first isolate it and convert it to a numeric data type. The challenge is that text formats vary: the number might be at the beginning, middle, or end, and it might have decimals, commas, or leading zeros. A single formula rarely works for every scenario, so understanding the structure of your data is the first step.

Simple Methods for Fixed Positions

If the number always appears at the same spot, use LEFT, RIGHT, or MID.

  • Number at the start: With text in A2, =VALUE(LEFT(A2, 5)) extracts the first five characters and converts them to a number.
  • Number at the end: =VALUE(RIGHT(A2, 3)) takes the last three characters.
  • Number in the middle: =VALUE(MID(A2, 7, 4)) starts at character 7 and pulls 4 characters.

These work only when the length is constant. If the number length varies, you can combine with FIND to locate a delimiter. For instance, to extract the number after a hyphen: =VALUE(MID(A2, FIND("-", A2)+1, LEN(A2))).

Using Flash Fill for Quick Extraction

Flash Fill (Excel 2013 and later) is the easiest no-formula method. Type the desired number in the adjacent column, press Ctrl+E (Windows) or Cmd+E (Mac), and Excel fills the rest based on the pattern it detects. For example, if A2 contains “Item 123” and you type “123” in B2, then press Ctrl+E, Excel will extract the numbers from all rows. Flash Fill works best when the pattern is consistent, and it does not update automatically if source data changes—you must re-run it.

Extracting Numbers from Any Position with a Formula

When the number can appear anywhere, use an array formula that finds the first digit and then collects all consecutive digits. In Excel 365 or Excel 2021, you can use TEXTJOIN with MID and SEQUENCE:

Assuming text in A2, enter:

=VALUE(TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, SEQUENCE(LEN(A2)), 1)), MID(A2, SEQUENCE(LEN(A2)), 1), "")))

This formula checks each character, keeps digits, and joins them. It returns the number as a numeric value. For older Excel versions, you can use a similar array formula with ROW(INDIRECT("1:"&LEN(A2))) instead of SEQUENCE, but you must press Ctrl+Shift+Enter to confirm it.

Handling Decimals, Commas, and Negative Signs

The basic digit-only formula ignores decimal points, commas, and minus signs. To include a decimal point, modify the condition to accept both digits and the period. For example, to keep digits and a single decimal point:

=VALUE(TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, SEQUENCE(LEN(A2)), 1)), MID(A2, SEQUENCE(LEN(A2)), 1), IF(MID(A2, SEQUENCE(LEN(A2)), 1)=".", ".", ""))))

This is more complex. A simpler approach for decimals is to use LOOKUP with RIGHT to grab the number at the end, as shown in the opening example. For negative numbers, you can prepend a minus sign if the text contains one, but that requires additional logic.

Using Power Query for Robust Extraction

Power Query (Get & Transform Data) is ideal for large datasets and repeated tasks. To use it:

  1. Select your data range and go to Data > From Table/Range (or Data > From Sheet in Excel 365).
  2. In the Power Query editor, add a custom column with the formula: Text.Select([YourColumn], {"0".."9"}).
  3. Change the new column’s data type to a whole number or decimal.
  4. Click Close & Load to bring the results back to a worksheet.

Power Query’s Text.Select removes all characters except those in the list, so it handles any position. It also refreshes automatically when you update the source data.

Common Problems and Pitfalls

  • Leading zeros disappear: When you convert to a number, Excel drops leading zeros (e.g., “007” becomes 7). To keep them, format the cell as text before entering the formula, or use TEXT to preserve the string.
  • Numbers with commas: Commas in text (e.g., “1,234”) are not digits, so they get removed, turning “1,234” into “1234”. To keep the comma, add it to the allowed characters list.
  • Multiple numbers in one cell: The formulas above extract only the first contiguous block of digits. To get all numbers, you need a more complex UDF (User Defined Function) in VBA.
  • Array formula not working: In versions before Excel 365, array formulas require Ctrl+Shift+Enter. If you get a #VALUE! error, check that you confirmed it correctly.

Frequently Asked Questions

How do I extract a number from the middle of a string without a fixed length?

Use a formula that locates the first digit and then takes all consecutive digits. For example, with text in A2, =VALUE(MID(A2, MIN(FIND({0,1,2,3,4,5,6,7,8,9}, A2&"0123456789")), SUM(--ISNUMBER(--MID(A2, ROW(INDIRECT("1:"&LEN(A2))), 1))))) works in older Excel as an array formula. In Excel 365, the TEXTJOIN method is simpler.

Can I extract numbers from multiple cells at once?

Yes. If you use a formula, drag it down or double-click the fill handle. Flash Fill also works on a column. Power Query processes the entire column in one go.

Why does my formula return a #VALUE! error?

This usually happens when the result is not a valid number—for example, if the extracted text contains a space or a letter. Check that your formula only includes digits and a single decimal point. Also, ensure you have entered array formulas correctly with Ctrl+Shift+Enter in older Excel.

How can I keep leading zeros when extracting numbers?

Don’t convert to a number. Keep the result as text by omitting VALUE. For example, =TEXTJOIN("", TRUE, IF(ISNUMBER(--MID(A2, SEQUENCE(LEN(A2)), 1)), MID(A2, SEQUENCE(LEN(A2)), 1), "")) returns text like “007”. Alternatively, format the cell as text before applying the formula.

Is there a way to extract numbers using VBA?

Yes. You can write a simple User Defined Function. Save a copy of your workbook first, as macros cannot be undone with Ctrl+Z. Here is an example:

Function ExtractNumbers(rng As Range) As Double
Dim i As Integer
Dim result As String
For i = 1 To Len(rng.Value)
If IsNumeric(Mid(rng.Value, i, 1)) Then
result = result & Mid(rng.Value, i, 1)
End If
Next i
ExtractNumbers = Val(result)
End Function

This function extracts all digits and returns them as a number. To use it, press Alt+F11, insert a module, paste the code, then use =ExtractNumbers(A2) in a cell.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.