This formula i use in my work quite a lot.

Please note, that if you want to call the function in VBA, then you have to have the function in a module and not in a regular sheet. It took me a while to figure that one out 🙂  when i got started.

I receive many bank statements with deposits, where the only way to find the correct accounting string is by lookup of the address. The formula returns the position of the first numeric character, so if the address in a cell is ‘High Street 12’ i can easily find the name of the street without the number and do my lookup.

Because only I need the function i haven’t saved it as a plugin, but if you want to make it available to the entire organisation that is probably the way to go.

Public Function MyOwnFind(ByVal cell As String) As Integer

For i = 1 To Len(cell)
Dim currentCharacter As String
currentCharacter = Mid(cell, i, 1)
If IsNumeric(currentCharacter) = True Then
MyOwnFind = i
Exit Function
End If
Next i
End Function