This post will guide you how to get the position of the first number in a text string in Excel. How do I find the position of first number in a text string in Excel 2013/2016.

1. Find the Position of First Number in a Cell using Formula

Assuming that you have a list of data in range B1:B4, in which contain text strings. And you wish to get the position of the first number in those range of cells. How to accomplish it. And you can use an formula based on the MIN function and the SEARCH function. Like below:

=MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},B1&"0123456789"))

Type this formula into a blank cell and press Enter key on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

You would see that the position of the first number is calculated.

If you want to get the position of last number in a text string in your range of cells, you can use another array formula based on the MAX function, the IFERROR function, the FIND function, the ROW function, and the LEN function. Like this:

=MAX(IFERROR(FIND({1,2,3,4,5,6,7,8,9,0},B1,ROW(INDIRECT("1:"&LEN(B1)))),0))

Type this formula into a blank cell and press Ctrl + Shift + Enter keys on your keyboard, and drag the AutoFill Handle down to other cells to apply this formula.

2. Find the Position of First Number in a Cell using User Defined Function with VBA Code

You can also create a User defined function with VBA Code to find the position of first number in a given cell in Excel. here are the steps:

Step1: press Alt + F11 to open the Visual Basic Editor.

Step2: In the Visual Basic Editor, click Insert > Module to insert a new module.

Step3: Copy the below VBA code for the custom function and paste it into the new module. Save the VBA module by clicking File > Save or by pressing Ctrl + S.

Function FirstNumberPosition_Excelhow(rng As Range) As Integer
Dim s As String
Dim i As Integer
s = rng.Value
For i = 1 To Len(s)
    If IsNumeric(Mid(s, i, 1)) Then
        FirstNumberPosition_Excelhow = i
        Exit Function
    End If
Next i
End Function

Step4: Type the following formula in a blank cell:

=FirstNumberPosition_Excelhow(B1)

Step5: Press Enter to calculate the result of the formula using the custom function.

3. Video: Find the Position of First Number in a Cell

This video will demonstrate how to find the position of the first number in a cell using a formula or VBA code in Excel.