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.

I need the macro to know when it's got a letter in it (or preferably just before), then I would use the `Exit For` in my code. With what I've written, I thought it should be doing that, but apparently not. — Josh Whitfield, Jan 21 '16 at 15:43
Currently, with a 5 character long number, ref ends up with the value "12345passplus::" which is because it keeps adding to the ref variable until intpos reaches 15. — Josh Whitfield, Jan 21 '16 at 16:03

score 5 · Answer 1 · edited May 23 '17 at 11:44

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:

Nice, I was thinking of using regex, but don't know enough of it to write anything. Good thinking! — BruceWayne, Jan 21 '16 at 16:33
Good approach. Using `vbnullstring` is a little more efficient then `""` — brettdj, Jan 22 '16 at 02:04

score 3 · Accepted Answer · answered Jan 21 '16 at 16:15

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

score 2 · Answer 3 · answered Jan 21 '16 at 16:08

This code, loosely based on your, works (produces "12345"). For large strings or more complex extraction needs, I would consider learning about the regex COM object.

Function ExtractNumber(ByVal text As String) As String

  ExtractNumber = ""

  foundnumber = False

  For e = 1 To Len(text)
    ref = Mid(text, e, 1)
        Select Case Asc(ref)
            Case 45 To 57   'this includes - . and /, if you want only digits, should be 48 to 57
                foundnumber = True
                ExtractNumber = ExtractNumber & ref
            Case Else
                If foundnumber = True Then Exit For
        End Select
  Next

End Function

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

3 Answers3