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

Question

I have a row in a cell, let's say it says "Client Ref: F123456PassPlus". Perhaps the line does not have a letter before the numbers, perhaps there is a character in the numbers, and perhaps there is a space between the letter and the numbers. I need to extract only numbers as a variable. I have code for this, but it does not know when to end the loop through the line. He must stop when there is something other than a number or symbol, but it continues.

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 letters of variables that do not have definitions have been predefined, e indicates the code where to start copying, and x indicates the cell containing the string. At the moment, everything works fine, it starts with a number and copies them and builds them in a large and large line, but this will stop only when intpos reaches 15.

Answer 1

I got rid of the Asc check and added a check against each character when you passed it before building the number 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 

Answer 2

There is nothing wrong with the way you are trying to accomplish this task, but I cannot help myself suggest a regex :-)

In this example, all non-digits from the line located in A1 will be selected and presented in the message box. Used Pattern [^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 the link to "Microsoft VBScript Regular Expressions 5.5"

For more information on how to use regex in Excel, including range-looping examples, check out this post .

Results:

Answer 3

This code, freely based on yours, works (produces "12345"). For large strings or more complex extraction tasks, I would consider exploring the regular expression 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