-
Apr 16th, 2019, 09:00 AM
#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.
-
Apr 18th, 2019, 05:21 AM
#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
Link as Reference
Excel Sheet1 with cells in column a : '017 '637 '619 '071
cell in column b : '617
Feeling like a fly on the inside of a closed window (Thunk!)
If I post a lot, it is because I am bored at work! ;D Or stuck...
* Anything I post can be only my opinion. Advice etc is up to you to persue...
-
Apr 18th, 2019, 08:34 AM
#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
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Apr 18th, 2019, 10:20 AM
#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
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Apr 18th, 2019, 10:07 PM
#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
-
Apr 18th, 2019, 10:18 PM
#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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|