-3

How can I test whether a string is in the format beginning with "R", followed by up to 8 numbers?

user2319146
  • 371
  • 1
  • 11
  • 3
    That is a very broad question with very limited context. Try giving a bit more details of you issue and what you've tried so far ? [This](https://stackoverflow.com/questions/22542834/how-to-use-regular-expressions-regex-in-microsoft-excel-both-in-cell-and-loops/22542835#22542835) could be a way of solving your issue. – Display name Dec 02 '18 at 23:43
  • Thank you. I want to trigger a certain action when a cell is activated, but only if it contains a string in a certain format. I was going to use `InStr` but I am not sure how to adapt it. – user2319146 Dec 02 '18 at 23:51
  • Test character by character; use the `Like` operator; regular expressions. There are all kinds of methods of testing. – Ron Rosenfeld Dec 02 '18 at 23:57
  • Let say you want at least 1 number after the "R", you can test it with: `debug.print test_string like "R#*" and len(test_string) <10` – Display name Dec 03 '18 at 00:05
  • Possible duplicate of [How to match a simple number pattern in VBA using RegEx](https://stackoverflow.com/q/4809453/608639) – jww Dec 21 '18 at 17:25

2 Answers2

2

This is easily accomplished using Regular Expressions.

Function testString(ByVal tstStr As String) As Boolean

    With CreateObject("VBScript.RegExp")
        .Pattern = "^R\d{0,8}$"
        .IgnoreCase = False
        testString = .test(tstStr)
    End With

End Function

.test() returns a Boolean value if tstStr matches your .Pattern.

Breaking down the pattern ^R\d{0,8}$

  • ^ Start of string
  • R matches a literal R
    • If you are okay with also matching a lowercase r, then you can set IgnoreCase = True
  • \d matches a digit 0-8 times {0,8}
  • $ matches the end of string

Since this is a function, you can test any input string

Debug.Print testString("R123456")
Rem: Prints 'True'
K.Dᴀᴠɪs
  • 9,945
  • 11
  • 33
  • 43
  • Thank you. Since I will only use this once, can I take it out of the function and test against ActiveCell.Value? – user2319146 Dec 03 '18 at 00:55
  • I am a firm believer that specialized code should be self contained. Of course, you can do this by doing `= .test(ActiveCell.Value)` if you choose to do so. If you keep it a function, you can use `testString(ActiveCell.value)` as well. – K.Dᴀᴠɪs Dec 03 '18 at 01:00
  • It will be quicker doing that. If you were compiling with speed optimisations enabled it would make the function inline. – CatCat Dec 03 '18 at 05:55
1

See https://theburningmonk.com/2012/05/performance-test-string-contains-vs-string-indexof-vs-regex-ismatch/ on why RegEx is not the best choice for simple tasks.

Str = "R12345678"
If Left(Str,1) = "R" then 
    if IsNumeric(Mid(Str, 2)) then 
        MsgBox "Match"
    End If
End If
CatCat
  • 483
  • 4
  • 5