-1

I have records like RF123456789, RF1234567890 etc.

I just want to match the records which are starting with 'RF' followed by exactly 9 digits of number. If it is more than 9 digit or less than 9 digit It should show Invaid. I have wrote the below code, but the problem with this is , if the number is more than 9 also it is showing valid. I understand that I have written to check only if it starts with RF and followed by 9 digits, so in case of 10 digits it is obviously matching my pattern. Is there any way I could restrict it for only 9 digits rather that 10?

Set myrange = Range("C2:C" & Rng)
For Each c In myrange
    strinput = c.Value
    patn = "([r|R][f|F][0-9]{9})"
    If patn <> "" Then
        With regex
            .Global = True
            .MultiLine = True
            .IgnoreCase = False
            .Pattern = patn
        End With
        If regex.Test(strinput) Then
            c.Offset(0, 5) = "Valid"
        Else
            c.Offset(0, 5) = "Invalid"
        End If
    End If

    ''checking Column D and E are matching or not''
    If c.Offset(0, 1) <> "" Then
        If c.Offset(0, 1) = c.Offset(0, 2) Then
            c.Offset(0, 6) = "Matching"
        Else
            c.Offset(0, 6) = "Not Matching"
        End If
    Else
        c.Offset(0, 6) = "Empty"
    End If
Next
Erik A
  • 31,639
  • 12
  • 42
  • 67
Vignesh Yalax
  • 11
  • 3

3 Answers3

4

You shouldn't use regex for this at all. A simple Like statement would do.

Just use strinput Like "RF#########". That returns true if it starts with RF (case insensitive) and then has 9 digits, else false.

Erik A
  • 31,639
  • 12
  • 42
  • 67
  • Agreed. Much more sensible. – QHarr Sep 26 '18 at 14:38
  • Great Thanks. It worked. I changed the entire code though.. :P – Vignesh Yalax Sep 26 '18 at 15:22
  • 3
    @VigneshYalax As a new contributor, you might not realize that since this answer solved your problem, it is customary to "Accept" the answer so that Erik gets credit for helping you, and so that others are helped when searching similar problems. – Jericho Johnson Sep 26 '18 at 17:02
1

Wrap it in boundaries?

\bRF\d{9}\b

Try here

\bRF\d{9}\b / gm

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

RF matches the characters RF literally (case sensitive)

\d{9} matches a digit (equal to [0-9])

{9} Quantifier — Matches exactly 9 times

\b assert position at a word boundary (^\w|\w$|\W\w|\w\W)

Update:

There are lots of ways to state having a lead of 2 numbers or RF. One might be:

\b(\d{2}|RF)\d{9}\b

Try it.

Someone more familiar with regex can no doubt advise on efficient regex methods.

QHarr
  • 83,427
  • 12
  • 54
  • 101
  • Hello Thanks for the quick response. It worked but my problem now is I am using two conditions like it can start with number or RF. Below is the code please help me buddy. Set myrange = Range("C2:C" & Rng) For Each c In myrange strinput = c.Value patn = "([0-9]{9,10})|([r|R][f|F][0-9]{9})" If patn <> "" Then With regex .Global = True .MultiLine = True .IgnoreCase = False .Pattern = patn – Vignesh Yalax Sep 26 '18 at 14:44
  • 2
    ^^^ Scope creep! – FreeMan Sep 26 '18 at 14:58
  • @VigneshYalax Second method added. It is good practice to state the exact requirement first time in the question to elicit fit for purpose answers. – QHarr Sep 26 '18 at 15:10
0

You can also use string functions like left() right() len() and isnumeric()

To check that the entire string is 11 characters long (RF + 9 digits) you could use:

If len(yourstring) = 11 then

To check that the string starts with RF use:

If left(yourstring,2)="RF" then

To check that the last 9 characters are numbers only first dimension a variant:

dim num_check as variant
num_check = right(yourstring,9)
If isnumeric(num_check) = False then

You can nest or combine these if statements to get the desired result.

help-info.de
  • 6,695
  • 16
  • 39
  • 41
VBAjake
  • 1
  • 1