How to find the first occurence of any number in a string

[Guest]

Having a variable length string, e.g., "Court House 133324 area 1678" or
"Hollywood Doors Space 5478 room 4", how to do I find the position of the
first numeric caracter?

 

[Guest]

This can be used in Code or in a Sheet as a user defined function... It
returns -1 if a digit is not found...

In Code
dim i as integer
i = firstnumber("Court House 133324 area 1678")

In a sheet
=FirstNumber(A1)

Public Function FirstNumber(ByVal InputString As String) As Integer
Dim intCounter As Integer
Dim intStringLength As Integer
Dim intReturnValue As Integer

intReturnValue = -1
intStringLength = Len(InputString)

For intCounter = 1 To intStringLength
If IsNumeric(Mid(InputString, intCounter, 1)) Then
intReturnValue = intCounter
Exit For
End If
Next intCounter

FirstNumber = intReturnValue
End Function

 

[RB Smissaert]

This might be faster. It will return -1 if there is no number.

Function PositionFirstNumberInString(strString As String) As Long

Dim i As Long
Dim btArray() As Byte

btArray = strString

For i = 0 To UBound(btArray) Step 2
If btArray(i) > 47 And btArray(i) < 58 Then
PositionFirstNumberInString = i \ 2 + 1
Exit Function
End If
Next

PositionFirstNumberInString = -1

End Function


RBS