Excel determine if two cells match based on consecutive matching characters

Question

How to do a partial string match between two cells where the partial string can be located anywhere in the cell.

Example: Cell 1 may have AXG58934x0 and Cell 2 may have 05893400 Or Cell 1 may have 5893400A and Cell 2 may have X1000000589340000y The cells should match on the "58934" portion.

Would want Excel to look at Cell 1 and Cell 2 and if it finds a match on a character look at the next character and if that matches then look at the next character and if 5 consecutive characters match then return the word "match".

I have tried every possible formula I can think of so I think you are right that VBA would be the only way. Would you happen to know of something that could work? — movy1313, Nov 16 '18 at 20:05
Tried that already and downloaded the addin. It did not work. It was hit or miss. Sometimes it said there was a partial match and sometimes it said no match when I could see there was. — movy1313, Nov 16 '18 at 20:10
You said there are many add ins? I only found the one by MS. If you can point me in the right direction for an add in that would work I would appreciate it! — movy1313, Nov 16 '18 at 20:11
If that addin does not work then no matter of vba programming will do it. You are basically asking excel to reason, which it cannot do. — Scott Craner, Nov 16 '18 at 20:14
Fuzzy matches will never be 100% reliable. For example, two cells have the following `0000054321` and `0000056789`? 60% of the characters are the same, yet most people would probably say they shouldn't be considered to match — cybernetic.nomad, Nov 16 '18 at 20:14
I am not sure why the fuzzy lookup add in did not work. Maybe I need to look into it again. The problem I was having wasn't that it said 60% matched and it wasn't really a match the problem I was having with it was that it was saying 0% matched when there were matches in the characters. This wasn't happening consistently though sometimes it would find the partial match and other times it would not find anything. — movy1313, Nov 16 '18 at 20:20
Would anyone happen to have a fuzzy match formula I can try to get that 60% result? — movy1313, Nov 16 '18 at 20:25

urdearboy · Accepted Answer · 2018-11-18T20:09:00.883

1

[`VBA` Solution] To Implement:

Hit Alt + F11 to open Visual Basic
Locate the book you are working on in the VBAProject viewer
Right Click & Select Insert Module
Paste the below code in the code space
Go back to excel and you can call the function how you would call any function =CSTMATCH()

The function takes 2 inputs (first string and second string) as seen in photo below

Option Explicit

Public Function CSTMatch(Target1 As Range, Target2 As Range) As Boolean

CSTMatch = False
Dim String1 As String, String2 As String, i As Long

'The goal here is to assign the larger String to the variable String1
If Len(Target1) >= Len(Target2) Then
    String1 = Target1
    String2 = Target2
Else
    String1 = Target2
    String2 = Target1
End If

For i = 1 To Len(String1) - 4
    If Mid(String1, i, 5) <> "00000" Then
        If InStr(String2, Mid(String1, i, 5)) Then
            CSTMatch = True
            Exit Function
        End If
    End If
Next i

End Function

Example of inputs/outputs of UDF are below

edited Nov 18 '18 at 20:09

answered Nov 16 '18 at 20:19

urdearboy

14,439
5
28
58

YOU ARE AWESOME!!! That totally worked! Thank you so much! I wish I had asked this question a long time ago! – movy1313 Nov 16 '18 at 20:51
Fuzzies are tricky beasts. Be aware that `test1` and `test1` will also return as `FALSE`. That said, this is solution has enough simplicity and few enough false positives (or true negatives) to deserve an upvote – cybernetic.nomad Nov 16 '18 at 20:59
Can one modification to the module be made to show FALSE if the consecutive matches are all zeros (00000) ? – movy1313 Nov 16 '18 at 22:05
As long as any 5 combination of letters or numbers match works for me. I did see the solution to try IsNumeric though if I need to match on only numbers. The only issue I am seeing right now with my results is that five consecutive zeros are being considered a match when the rest of the characters are different. – movy1313 Nov 16 '18 at 22:11
Sorry.. actually there is one other issue I have. In addition to wanting the module to ignore five consecutive zeros can it also ignore a semicolon? I have a semicolon delimeter between two values and found it saying it's a match because the last two digits and the first two digits of another value matched with the semicolon in between making them look like matching strings. – movy1313 Nov 16 '18 at 22:14
I can work around the delimiter issue with some formatting but ignoring the consecutive zeros would be a great addition if you had a few minutes to share that update. Thanks again for all your help!! You are a life saver! – movy1313 Nov 17 '18 at 03:22
@movy1313 updated to exclude `00000` matches – urdearboy Nov 18 '18 at 20:09
Thank you again urdearboy !! Worked perfectly! I am going to see if I can figure out how to use your code and make it so in the formula I can request how many characters to match on so I can use it in other instances. For example: =CSTMATCH((A2,B2),5,"match", "no match") Where the "5" would indicate the number of characters to match on and the other text in quotes would be what is returned if true or false. But seriously Thank you again!!! this was a HUGE help! – movy1313 Nov 19 '18 at 15:54
The equation would look like `CSTMATCH(A2, B2, 5)` and you will need to just replace all the 5's in the code with your variable. You will also need to replace the 4 in the opening loop with your variable and then subtract one – urdearboy Nov 19 '18 at 15:57

score 1 · Answer 2 · answered Nov 16 '18 at 22:10

Inspired by @urdearboy

This will give you the ratio of consecutive characters in the shortest string to the one in the longest

Option Explicit

Public Function CSTMatch2(Target1 As Range, Target2 As Range) As Double

CSTMatch2 = 0

Dim String1 As String, String2 As String, i As Long, j As Long, noChar As Long

noChar = 0

'The goal here is to assign the larger String to the variable String1
If Target1 = Target2 Then
    CSTMatch2 = 1
    Exit Function
End If

If Len(Target1) >= Len(Target2) Then
    String1 = Target1
    String2 = Target2
Else
    String1 = Target2
    String2 = Target1
End If

For j = 1 To Len(String2)
    For i = 1 To Len(String1) - j
        If InStr(String2, Mid(String1, i, j)) Then
            noChar = noChar + 1
            Exit For
        End If
    Next i
Next j

Debug.Print noChar, Len(String1), Len(String2)
CSTMatch2 = (noChar) / (Len(String1))


End Function

Sample:

Excel determine if two cells match based on consecutive matching characters

2 Answers2

[VBA Solution] To Implement:

[`VBA` Solution] To Implement: