Regex that selects the first numbers to appear, in a loop

user_12845199 0
Viewed 63

so basically i have these types of strings

Unique

Decimal string

Hundred

and i would like to replaces the firsts numbers to appear, unfortunately they sometimes come in hundreds decimals or uniques. Here is the code and the patterns that i tried using.

Sub Validator()
Dim r As Range
Dim s As String
Dim news As String
Dim regex As New RegExp
Dim regex2 As New RegExp
regex.Global = True
regex.Pattern = ",[\s\S]*$"
regex2.Pattern = "(^|\\s)([0-9]+)($|\\s)"


Sheet1.Activate


For Each r In Range("B1:B17")
    s = r.Value
    
    news = regex2.Replace(s, " ")
    
    r.Value = news

Next r

End Sub

Please ignore pattern 1 he basically takes out everythign after the comma and it is succesful the problem is pattern 2 which is doing basically nothing when i execute my code and i know the code is functional because the first one came out correctly.

A3 Sport 1.8 16V TFSI S-tronic 3p

A3 Sedan Prestige Plus 1.4 TFSI Flex Tip

TT 1.8 TB 180cv

Should be the end results

vba regex
2 Answers

You may remove the first number using

Sub Validator()
Dim r As Range
Dim s As String
Dim news As String
Dim regex As New RegExp
Dim regex2 As New RegExp
regex.Global = False             ' <---- Enabling first match search only (you may also just remove this line)
regex2.Pattern = "\d+(?:\.\d+)?" ' <---- One or more digits, and an optional occurrence of
                                 '       a dot and one or more digits right after
Sheet1.Activate
For Each r In Range("B1:B17")
    s = r.Value
    r.Value = regex2.Replace(s, " ")
Next r

End Sub

Note the regex.Global default value is False, so you may simly remove the line with regex.Global = False.

If your numbers can have mixed decimal separator, like . and ,, use regex2.Pattern = "\d+(?:[.,]\d+)?".

user_38329700

Looking at your examples I think I'd go with regular VBA functionality as these sample do not look too complicated.

For example:

Sub Test()

Dim str As String: str = "44 A3 Sedan Prestige 1.4 TFSI Flex Tip., 8561"
Debug.Print Mid(Split(str, ",")(0), InStr(1, str, " ") + 1, Len(str))

End sub

If you insist on regular expressions it looks like you may just use:

Sub Test()

Dim str As String: str = "44 A3 Sedan Prestige 1.4 TFSI Flex Tip., 8561"

With CreateObject("vbscript.regexp")
    .Global = True
    .Pattern = "^\S+\s([^,]+)"
    Debug.Print .Execute(str)(0).Submatches(0)
End With

End Sub

Both options return:

A3 Sedan Prestige 1.4 TFSI Flex Tip.
user_97581940
Related