 # RegEx to match two out of three digits

• Apr 16th, 2019, 09:00 AM
MartinLiss
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.
• Apr 18th, 2019, 05:21 AM
Ecniv
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
• Apr 18th, 2019, 08:34 AM
westconn1
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```
Quote:

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
• Apr 18th, 2019, 10:20 AM
Zvoni
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
• Apr 18th, 2019, 10:07 PM
MartinLiss
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
• Apr 18th, 2019, 10:18 PM
MartinLiss
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```