How to find first/all numbers in a string in Excel?

This method introduces a set of formulas that can be used not only to find and extract the first number within a text string, but also to determine the exact position where that first numeric character occurs in the cell. Applying these formulas is especially useful for users who need to analyze standardized strings with variable number positions, such as product codes or IDs embedded with descriptive text.

Find and extract the first number in a text string with an array formula

Select a blank cell where you want to return the first number from a text string, enter the formula =MID(A2,MIN(IF((ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2)))),ISNUMBER(MID(A2,ROW(INDIRECT("1:"&LEN(A2))),1)+0)*ROW(INDIRECT("1:"&LEN(A2))))),1)+0 (A2 is the text cell where you will look for the first number) into it, and press Ctrl + Shift + Enter simultaneously. This array entry ensures the formula processes each character in your string individually.
After confirming, drag the cell's Fill Handle down to apply the formula to additional rows as needed.

Once applied, the first numbers of each text string are found and extracted as shown below:

Find the first number’s position in a text string with formulas

Select a blank cell where you want to return the position of the first number, and enter the following formula: =MIN(IF(ISNUMBER(FIND({1,2,3,4,5,6,7,8,9,0},A2)),FIND({1,2,3,4,5,6,7,8,9,0},A2))) (A2 here represents the cell you want to check). This formula finds the earliest occurrence of any digit (0–9). Drag the formula down to fill adjacent cells as needed.

Note: Alternatively, use this array formula: =MIN(FIND({0,1,2,3,4,5,6,7,8,9},A2&{0,1,2,3,4,5,6,7,8,9})). Remember to confirm with Ctrl + Shift + Enter. When copying formulas, always update any fixed cell references if your structure changes. If the string does not contain any numbers, these formulas will return an error. You can wrap the formula with IFERROR to display custom messages in such cases.

If your goal is to obtain all numbers from a cell by simply stripping away any non-numeric characters, Kutools for Excel’s Remove Characters utility offers a convenient solution. This feature is particularly suitable for users who frequently process imported data or need quick batch-cleaning of cells, and prefer a straightforward interface over complex formulas.

1. Select the range you want to process to extract all numbers, and navigate to the menu: Kutools > Text > Remove Characters. Refer to the screenshot for guidance:

2. In the Remove Characters dialog box that appears, ensure only the Non-numeric checkbox is selected, then click OK to proceed.

As a result, the tool will strip all non-numeric characters from the selected cells, and you will be left with only the numeric components from each cell.

Before running this operation, consider making a backup of your range in case you need to revert. For text strings containing numbers separated by letters (e.g., "A23B17"), all numbers will be concatenated together (“2317”). If you expect a different arrangement, consider combining this feature with other functions.

If you want to directly extract all numbers within a text cell (regardless of format) as a single value, the EXTRACTNUMBERS function in Kutools for Excel provides a user-friendly and highly efficient method. This is especially helpful for users who need batch processing but prefer a function-based, formulaic way that can be applied flexibly throughout the worksheet.

1. Select a blank cell where you want the extracted numbers to appear, then click Kutools > Functions > Text > EXTRACTNUMBERS. See the illustration below:

2. In the Function Arguments dialog box, assign the relevant text cell to the Txt field, and enter TRUE in the N field (to return a numeric value). Then, click OK to confirm and extract the numbers.

Notes:
(1) You may enter TRUE, FALSE, or leave the N field blank. Specifying TRUE returns a numeric value; FALSE or leaving it blank will return the numbers as text (useful for preserving leading zeros).
(2) This feature can also be used as a formula directly in the worksheet: =EXTRACTNUMBERS(A2,TRUE). Enter this in your preferred output cell.
(3) When copying the formula to adjacent rows, make sure cell references are adjusted where necessary.

The extracted numbers from each text cell will display as illustrated below:

When formulas and built-in features are insufficient—for instance, if you need to extract either only the first number, the first complete number sequence, or all separate numbers from a string—using VBA code provides more flexibility and automation. This method is especially recommended for users who regularly deal with unique extraction requirements across large datasets, or wish to automate the process with reusable macros. Please note that enabling macros is required, and always back up your file before running code on large datasets.

1. On the main Excel window, click Developer Tools > Visual Basic to open the VBA editor. In the Microsoft Visual Basic for Applications window, select Insert > Module. Copy and paste the following code into the new Module:

Extract the first numbers: Extracts just the first continuous numeric sequence found in each string (for example, "abc12def45" will return12).

Sub ExtractFirstNumber()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim sNum As String
    Dim FirstNum As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        sNum = ""
        FirstNum = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                sNum = sNum & Mid(txt, i, 1)
                
                If i = Len(txt) Or Not (Mid(txt, i + 1, 1) Like "[0-9]") Then
                    FirstNum = sNum
                    Exit For
                End If
            ElseIf sNum <> "" Then
                FirstNum = sNum
                Exit For
            End If
        Next i
        
        cell.Offset(0, 1).Value = FirstNum
    Next cell
End Sub

Extract all numbers: Extracts all numbers in sequence, separating individual number blocks with spaces (for example, "abc12def45" will return "12 45").

Sub ExtractAllNumbers()
    Dim rng As Range
    Dim cell As Range
    Dim txt As String
    Dim i As Integer
    Dim arrNums As String
    
    On Error Resume Next
    xTitleId = "KutoolsforExcel"
    
    Set rng = Application.Selection
    Set rng = Application.InputBox("Select range", xTitleId, rng.Address, Type:=8)
    
    For Each cell In rng
        txt = cell.Value
        arrNums = ""
        
        For i = 1 To Len(txt)
            If Mid(txt, i, 1) Like "[0-9]" Then
                arrNums = arrNums & Mid(txt, i, 1)
            Else
                If arrNums <> "" Then
                    arrNums = arrNums & " "
                End If
            End If
        Next i
        
        arrNums = WorksheetFunction.Trim(arrNums)
        cell.Offset(0, 1).Value = arrNums
    Next cell
End Sub

2. In the VBA editor, click the Run button or press F5 to run the code. A dialog box will prompt you to select the target range. The code writes the extracted number(s) into the column immediately to the right of the original data.

Be sure your worksheet does not already use the columns immediately to the right of your data to avoid overwriting. If the code does not seem to work, check whether macros are enabled for your workbook, and confirm that your text values do contain numbers.

Excel’s built-in Flash Fill feature can automatically extract numbers from text strings by learning from example patterns. This method is especially useful for quick manual operations on well-structured data, or when you want a simple, formula-free solution that doesn't require setting up complex logic.

To use Flash Fill to extract numbers from a string:

1. In a blank column adjacent to your data, manually type the desired output for the first cell. For example, if A2 contains "OrderA12B3", type "123" into B2 to demonstrate the extraction result.
2. Go to the cell below your example (e.g., B3), and start typing the expected result for the next row. Excel may display a preview of the pattern it recognizes. If the preview matches your intention, simply press Enter. Otherwise, continue demonstrating the expected patterns with a few more inputs.
3. Alternatively, after providing the example in the first cell, select the entire intended output range and go to Data > Flash Fill (or press Ctrl + E). Excel will automatically fill in the extracted numbers according to the learned pattern.

Flash Fill recognizes consistent input and output relationships, so ensure your examples are clear and accurate. This feature works best when the data format is regular—if your data structure is more variable, or you need precise control, consider using a formula or macro solution.

If Flash Fill fails to appear, check that it is enabled under File > Options > Advanced > Editing options > ensure Automatically Flash Fill is checked.