How do I find a combination of a letter a number in a VBA string?

Question

I'd like to check if in a string (with InStr?) the combination of a single alphabetical letter and a number is to be found. In particular the letter can only be:

"R" or "B" or "G" or "Y"

While the numbers can go from 1 to 9.

Examples given:

R1 is legal
Y6 is legal
A2 is not legal
G10 is not legal

In this string only one instance of this combination can be found, and it is marked by an underscore "_" at the beginning. It can also happen that this combination is nowhere to be found.

I would like to avoid the trivial solution (which brings a lot of calculations that I don't need in my program) of a series of Or, or an incredibly long Select Case.

Is there any way to use a custom variable or wildcards here?

Do you mean you have a string like `I have R1 and G9 and R101 A2222 substrings` and you want to receive a collection/array containing `R1` and `G9` only? Or just return `True`/`False` if a string contains your pattern? — Wiktor Stribiżew, Feb 13 '17 at 08:19
For `RegEx`, you may check this post: http://stackoverflow.com/a/22542835/1050927 — Prisoner, Feb 13 '17 at 08:22
I added some info in the question. I can have `blahblahblah_R1.blah` and I would like to get `R1` as a return. — Noldor130884, Feb 13 '17 at 08:22
You can also use Like operator to do evaluation for ex. `yourValue Like "_[RBGY]#"` — Han Soalone, Feb 13 '17 at 10:01

score 3 · Accepted Answer · answered Feb 13 '17 at 08:29

You may use a regex to get the value you are looking for:

_([YRGB]\d)(?!\d)

Here, _ will find an underscore, then ([YRGB]\d) will match and capture a letter from the [YRGB] character class (Y, R, G or B) and any digit (\d) that is not followed with another digit ((?!\d) is a negative lookahead that will fail the match if there is a digit immediately to the right of the current location).

Or with a word boundary (\b, it will require a char other than a letter/digit or _ right after the digit) at the end:

_([YRGB]\d)\b

See the regex demo. The result is the substring captured into Group 1.

Sample VBA code:

Sub DemoFn()
Dim re As RegExp
Dim s As String, res As String
Dim colMatch As MatchCollection, objMatch As Match

s = "blahblahblah_R1.blah"
Set re = New RegExp
With re
  .pattern = "_([YRGB]\d)(?!\d)"
  .Global = False
  .MultiLine = False
End With

Set colMatch = re.Execute(s)
For Each objMatch In colMatch
  res = objMatch.SubMatches.Item(0)
Next
Debug.Print res
End Sub

score 0 · Answer 2 · edited Jan 14 '21 at 06:04

0

You could try the VBA Like Operator:

Sub PatternMatching
    If (("G6" Like "[YRGB][1-9]") = True) Then
        Msgbox("this is a match")
    End If
End Sub

edited Jan 14 '21 at 06:04

Traveler

213
3
10

answered Jan 07 '21 at 16:54

user14960478

1

How do I find a combination of a letter a number in a VBA string?

2 Answers2