CODEWITHSUNDEEP
stringvbaexcel
Josh Whitfield
Josh Whitfield

Reputation: 321

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

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.

Upvotes: 7

Views: 5604

Answers (3)

Automate This
Automate This

Reputation: 31394

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:

enter image description here

Upvotes: 5

Scott Holtzman
Scott Holtzman

Reputation: 27269

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

Upvotes: 3

dnep
dnep

Reputation: 562

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

Upvotes: 2

Related Questions