 # Thread: RegEx to match two out of three digits

1. ## RegEx to match two out of three digits

I'm trying to analyze some numbers to find two-digit number matches between two, three-digit numbers. For example if I have "617" in range("B1") is there a RegEx statement that would say that "017", "637" and "619" in column "A" all match because they all have two digit matches with "617" in order. In other words "071" would not match because the the 1 and the 7 are not in the same order as they are in "617".

I know how to do it using string functions but I'd like to simplify the code.  Reply With Quote

2. ## Re: RegEx to match two out of three digits

Interesting problem. Doubt this is what you want, but does seem to work (text 3 digits as you specified). Perhaps it will give you some ideas
Code:
```Public Sub t()
Dim sht As Worksheet
Dim lngR As Long
Dim lngMR As Long
Dim strPV As String
Dim strP As String
Dim strV As String
Dim regEx As New RegExp

Set sht = ActiveWorkbook.Sheets("sheet1")
lngMR = sht.Cells(65535, 1).End(xlUp).Row

strPV = sht.Cells(1, 2)
strP = ".*" & Left(strPV, 2) & ".*" & _
"|.*" & Right(strPV, 2) & ".*" & _
"|.*" & Left(strPV, 1) & ".*" & Right(strPV, 1) & ".*"

With regEx
.Global = True
.MultiLine = True
.IgnoreCase = True
.Pattern = strP
End With

For lngR = 1 To lngMR
strV = sht.Cells(lngR, 1).Value
sht.Cells(lngR, 3) = regEx.Test(strV)
Next

Set sht = Nothing
End Sub```

Excel Sheet1 with cells in column a : '017 '637 '619 '071
cell in column b : '617  Reply With Quote

3. ## Re: RegEx to match two out of three digits

here is a mathematical alternative

Code:
```n = 617
x = n \ 100
y = (n - x * 100) \ 10
z = n - x * 100 - y * 10

For t = 1 To 999
a = t \ 100
b = (t - a * 100) \ 10
c = t - a * 100 - b * 10
If (x = a And y = b) Or (x = a And z = c) Or (y = b And z = c) Then Debug.Print t
Next```
17
117
217
317
417
517
607
610
611
612
613
614
615
616
617
618
619
627
637
647
657
667
677
687
697
717
817
917  Reply With Quote

4. ## Re: RegEx to match two out of three digits

If you want to go the Regex-way, maybe something here:
https://www.regular-expressions.info/numericranges.html  Reply With Quote

5. ## Re: RegEx to match two out of three digits

Thanks Ecniv. How would I return the actual matching characters rather tham True/False. In other words
017 ==> 17
637 ==> 67
619 ==> 61  Reply With Quote

6. ## Re: RegEx to match two out of three digits

Thanks Ecniv. How would I return the actual matching characters rather tham True/False. In other words
017 ==> 17
637 ==> 67
619 ==> 61

BTW here's part of the VBA I use now which as it turns out isn't very complicated.
Code:
```   Select Case True
Case Left\$(Cells(lngRowA, "B"), 2) = Left\$(Cells(lngRow, "BT"), 2) Or _
Left\$(Cells(lngRowA, "B"), 2) = Right\$(Cells(lngRow, "BT"), 2) Or _
Left\$(Cells(lngRowA, "B"), 2) = Left\$(Cells(lngRow, "BT"), 1) & Right\$(Cells(lngRow, "BT"), 1)
' Do something with Left\$(Cells(lngRowA, "B"), 2)
Case Right\$(Cells(lngRowA, "B"), 2) = Left\$(Cells(lngRow, "BT"), 2) Or _
Right\$(Cells(lngRowA, "B"), 2) = Right\$(Cells(lngRow, "BT"), 2) Or _
Right\$(Cells(lngRowA, "B"), 2) = Left\$(Cells(lngRow, "BT"), 1) & Right\$(Cells(lngRow, "BT"), 1)
' Do something with Right\$(Cells(lngRowA, "B"), 2)
Case Left\$(Cells(lngRowA, "B"), 1) & Right\$(Cells(lngRowA, "B"), 1) = Left\$(Cells(lngRow, "BT"), 2) Or _
Left\$(Cells(lngRowA, "B"), 1) & Right\$(Cells(lngRowA, "B"), 1) = Right\$(Cells(lngRow, "BT"), 2) Or _
Left\$(Cells(lngRowA, "B"), 1) & Right\$(Cells(lngRowA, "B"), 1) = Left\$(Cells(lngRow, "BT"), 1) & Right\$(Cells(lngRow, "BT"), 1)
' Do something with Left\$(Cells(lngRowA, "B"), 1) & Right\$(Cells(lngRowA, "B"), 1)
End Select```  Reply With Quote

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•

Featured

Click Here to Expand Forum to Full Width