Extracting the first digit from an alpha-numeric string

Question

Is it possible to use VBA to catch and use the first number in a text string and if there is no number then just use 1?

The code I'm currently using is looking at column AC and finding a specific text then using the first two letters of that text and the right most number and replacing the corresponding row in column N.

What I'm attempting to do now is account for when there is no number in the text (should =1), or there is but it's in the mid but the location moves (thus a mid statement wouldn't work). If this is possible Any suggestions or push in the right direction would be appreciated.

 Dim wsl As Worksheet
 Dim LR As Long, i As Long

 mydate = Format(Date, "YYMMDD")

 Set wsl = LagoDLFile.Sheets("cid_SeventhAvenue_" & mydate & "")
 LR = wsl.Range("AC" & wsl.Rows.Count).End(xlUp).Row

 For i = 2 To LR
     If wsl.Range("AC" & i) Like "*EOC*" Then
         wsl.Range("N" & i) = "EOC"
     ElseIf wsl.Range("N" & i) Like "700" Then
         wsl.Range("N" & i) = "CHK"
     ElseIf wsl.Range("AC" & i) Like "*CTOB*" Then
         wsl.Range("N" & i) = "COF"
     ElseIf wsl.Range("AC" & i) Like "EXOBC*" Then
         wsl.Range("N" & i) = "WR" & Right(wsl.Range("AC" & i), 1)
     ElseIf Left(wsl.Range("AC" & i), 3) = "OBC" Then
         wsl.Range("N" & i) = "OB" & Right(wsl.Range("AC" & i), 1)
     ElseIf Left(wsl.Range("AC" & i), 3) = "IFC" Then
         wsl.Range("N" & i) = "IF" & Right(wsl.Range("AC" & i), 1)
     ElseIf Left(wsl.Range("AC" & i), 3) = "IBC" Then
         wsl.Range("N" & i) = "IB" & Right(wsl.Range("AC" & i), 1)
     End If
 Next i
 End Sub

Answer 1

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)