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.

Table of Contents

## 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.

## Leave a Reply

You must be logged in to post a comment.