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:
- Select your data range and go to Data > From Table/Range (or Data > From Sheet in Excel 365).
- In the Power Query editor, add a custom column with the formula:
Text.Select([YourColumn], {"0".."9"}). - Change the new column’s data type to a whole number or decimal.
- 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
TEXTto 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
- Excel Strikethrough: How to Apply & Remove It
- Excel Strikethrough: How to Add It & Shortcuts
- Excel Utility for E-Invoice: What It Does
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Add Audio to PowerPoint SlidesTo add an audio file in PowerPoint for Microsoft 365 on Windows, select the slide, choose Insert >…
- Excel Data Validation From Another Workbook: Best MethodsYes, but the dependable approach is to bring or refresh the list in the workbook that needs the…
- How to Hide Columns in Excel Using Keyboard ShortcutsTo hide a column in Excel for Microsoft 365 on Windows, select a cell in it, press Ctrl+Space…
- Why Did My Excel File Get Corrupted? Causes and FixesYour Excel file most likely became corrupted because Excel or another program could not finish saving it correctly,…
- Google Sheets FILTER Function Syntax, Examples, and TipsGoogle Sheets uses the FILTER function to return rows or columns that meet one or more conditions. Its…
- How to Make Columns in Word With Lines Between ThemTo make columns in Word with a vertical divider, open Layout > Columns > More Columns, choose two…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.