Here is a function that uses regular expressions to do what you need. It uses this regex:

^\D*(\d)

Breaking down the pattern

• ^ Start of string
• \D* Any character that is not a digit (capital D), with a * quantifier that means 0 or more times
• (...) Capturing group - it will capture the text that you want to keep and return it as a submatch
• \d any numerical value (0-9).

Note: If you want it to capture more than the first digit (such as string123 - you want to return 123, you can change (\d) in the above regex to (\d+). The + is a quantifier that means one or more of \d.

See this regex work at Regex101. The green highlighted portion is what would be returned from the below function.

Creating the function / UDF

Public Function getFirstDigit(testString As String) As String ' or As long

    With CreateObject("VBScript.RegExp")
        .Pattern = "^\D*(\d)"
        .Global = False
        If .test(testString) Then
            getFirstDigit = .Execute(testString)(0).SubMatches(0)
        Else
            getFirstDigit = "1"
        End If
    End With

End Function

You can use this function within both VBA and as a worksheet function.

Using in VBA:

Msgbox getFirstDigit("Test String 123")

Using in the worksheet

=getFirstDigit($A$1)

Variant without RegExp

Public Function firstIntInString(testStr As String) As Integer
    Dim x%
    For x = 1 To Len(testStr)
        If Mid(testStr, x, 1) Like "#" Then
            firstIntInString = Mid(testStr, x, 1) 
            Exit For
        Else
            firstIntInString = 1 'in case when string doesn't contains digits
        End If
    Next x
End Function