Excel VBA loop through a string of numbers until a letter is found

Question

I have a string in a cell, lets say it says "Client Ref: F123456PassPlus". It's possible the string not have a letter before the numbers, it's possible there is a symbol in the numbers and it's possible there is a space between the letter and the numbers. I need to extract only the numbers as a variable. I have the code to do it, but it doesn't know when to stop looping through the string. It should stop when there is something other than a number or symbol but it carries on instead.

IsNumber = 1
ref = ""
If branch = "" Then
    e = b
Else
    e = b + 1
End If
f = 1
While IsNumber = 1
    For intpos = 1 To 15
        ref = Mid(x, e, f)
        f = f + 1
        Select Case Asc(ref)
            Case 45 To 57
                IsNumber = 1
            Case Else
                IsNumber = 0
                Exit For
        End Select
    Next
    IsNumber = 0
Wend

Any variable letters there that don't have definitions have been previously defined, e tells the code where to start copying and x is the cell that contains the string. For now, it all works fine, it starts at the number and copies them and builds them into a bigger and bigger string, but it will only stop when intpos reaches 15.

Answer 1

There is nothing wrong with how your trying to accomplish this task but I can't help myself from suggesting regex :-)

This example will strip all non-digits from the string located in A1 and present the result in a message box. The pattern used is [^0-9]

Sub StripDigits()
    Dim strPattern As String: strPattern = "[^0-9]"
    Dim strReplace As String: strReplace = vbnullstring
    Dim regEx As New RegExp
    Dim strInput As String
    Dim Myrange As Range

    Set Myrange = ActiveSheet.Range("A1")

    If strPattern <> "" Then
        strInput = Myrange.Value
        strReplace = ""

        With regEx
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = strPattern
        End With

        If regEx.test(strInput) Then
            MsgBox (regEx.Replace(strInput, strReplace))
        Else
            MsgBox ("Not matched")
        End If
    End If
End Sub

Make sure you add a reference to "Microsoft VBScript Regular Expressions 5.5"

For more information on how to use regex in Excel, including examples of looping through ranges check out this post.

Results:

Answer 2

I got rid of the Asc check and added a check against each character as you pass it before building the numerical "string".

IsNumber = 1
ref = ""
If branch = "" Then
    e = b
Else
    e = b + 1
End If
f = 1
While IsNumber = 1
    For intpos = 1 To 15
        char = Mid(x, e + intpos, 1)
        f = f + 1
        If IsNumeric(char) Then
            ref = Mid(x, e, f)
            IsNumber = 1
        Else
            IsNumber = 0
            Exit For
        End If
    Next
    IsNumber = 0
Wend