Microsoft Excel Functions & Formulas

Use a user-defined function to determine the

position of the first number

The user-defined function described here determines the position

of the first number in a cell.

4

To determine the position of the first number:

1. Type any data with letters and numbers in cells A1:A5.

2. Press <Alt+F11> to open the Visual Basic Editor.

3. From the Insert menu, click Module.

4. Type the following function:

Function FirstNum(rng As Range)

Dim i As Integer

Fori=1ToLen(rng.Value)

Select Case Mid(rng.Value, i, 1)

Case0To9

FirstNum = i

Exit Function

End Select

Next i

End Function

5. Close the VBA Editor by pressing <Alt+Q>.

6. Select cells B1:B5 and type the formula =FirstNum(A1) ...