Excel VBA String Evaluation

Question

I'm wondering if it's possible to use some VBA to find defined patterns in a whole bunch of 5char strings? I've explored the "instr" function but I'm not sure if it will perform the task I require which is basically to find patterns in the string like the following;

ABABA
BCBCB
.....
EFEFE

Or any such pattern where the 1st char is the same as chars 3 & 5, and the 2nd char is the same as char 4.

Any help or direction would be gratefully received.

Kind regards

Jim

Answer 1

My turn:

Function findPattern(inputStr As String) As Variant()
Dim arr() As String
Dim i As Integer
arr = Split(inputStr)
ReDim arr2(UBound(arr)) As Variant
For i = LBound(arr) To UBound(arr)
    If Left(arr(i), 1) = Mid(arr(i), 3, 1) And _
        Left(arr(i), 1) = Mid(arr(i), 5, 1) And _
        Mid(arr(i), 2, 1) = Mid(arr(i), 4, 1) Then

        arr2(i) = "True"
    Else
        arr2(i) = "False"
    End If
    findPattern = arr2
Next
End Function

Sub trying()

    Dim t As String
    t = "ABABA BCBCB IOITU"
    arr = findPattern(t) 'returns an array {True,True,False}

    For x = 0 To 2
    Debug.Print arr(x)
    Next
End Sub

This assumes that you have multiple words in each string. It returns an array of true false.

Edit

To find it there are any patterns use this UDF:

Function findPattern(inputStr As String) As String
Dim i As Integer
For i = 5 To 1 Step -1
If Asc(Mid(inputStr, i, 1)) > 5 Then
    inputStr = Replace(inputStr, Mid(inputStr, i, 1), i)
End If
findPattern = inputStr
Next

End Function

It will return 12121 on "ABABA"

You can paste this in a module in the workbook then use it like a formula: =findPattern("A1") Copy it down. Then sort on the column, it will place all like patterns together with the most patternized (11111) to the least (12345).

Then you could also filter on this column for any pattern you desire.

Answer 2

Try the Like operator:

Const testString = "ABABA"
Dim myChar1 As String, myChar2 As String

'// test 1/3/5
myChar1 = Mid(testString, 1, 1)
'// test2/4
myChar2 = Mid(testString, 2, 1)

If testString Like myChar1 & "[A-Z]" & myChar1 & "[A-Z]" & myChar1 Then
    MsgBox "Matches 1, 3 and 5"
ElseIf testString Like "[A-Z]" & myChar2 & "[A-Z]" & myChar 2 & "[A-Z]" Then
    Msgbox "Matches 2 and 4"
End If

---

Or use the Mid() function:

If Mid(testString, 1, 1) = Mid(testString, 3, 1) And _
   Mid(testString, 3, 1) = Mid(testString, 5, 1) And _
   Mid(testString, 1, 1) = Mid(testString, 5, 1) Then

    MsgBox "Matches 1, 3 and 5"

ElseIf Mid(testString, 2, 1) = Mid(testString, 4, 1) Then

    MsgBox "Matches 2 and 4"

End If

---

OR to check for both conditions:

Dim match1 As String, match2 As String
Const testString As String = "ABABA"

match1 = Left(testString, 1) & "[A-Z]" & Left(testString, 1) & "[A-Z]" & Left(testString, 1)

match2 = "[A-Z]" & Left(testString, 1) & "[A-Z]" & Left(testString, 1) & "[A-Z]"

If testString Like match1 Or testString Like match2 Then
    MsgBox "findwindow likes it when anything matches"
End If

Answer 3

You can do it without VBA and it would still be fast enough:

=IF(AND(MID("ABABA",1,1)=MID("ABABA",3,1),MID("ABABA",2,1)=MID("ABABA",4,1),MID("ABABA",3,1)=MID("ABABA",5,1)),1,0)

just replace "ABABA" with corresponding cell address and that's it

Answer 4

Okay so I actually went for this in the end...

Embarrassingly simple, literally cannot believe I didn't consider this as an option first and instead just assumed it should be done in VBA. A valuable lesson!

Had to mod the formulae anyway because my initial pattern flag (1,3,5 & 2,4) evaluated to big fat FALSE across the board, so I decided to look for 1,3 & 2,4 with 5 being anything. As I mentioned in my comment to @zedfoxus this gets me to where I need to be right now but it would be great to use VBA to productionise this. I'm going to review all your answers so thanks for taking the time to respond, I frickin' love this place!

PEACE!

Answer 5

Throwing my hat in the ring XD