Excel formula to compare partial match cells

Question

I am looking for help with a formula.

I need to compare text in two cells (not columns).

One of the cells has a last name, the other cell may have part of the last name with additional numbers and letters (not in any specific order). These are the pairs we would like to locate.

Not all of the cells will match, however for the ones where part of the text matches, would like something like "MATCH".

For example in cell E2= 000034568MILL WALLI and in cell J2=WALLINGER should bring a MATCH, since the WALLI in cell E2 matches part of the text.

Another example:

E2= Benjamin P Rouamba and J2=Roumbamoore should bring back match.

I hope this makes sense and that there is a possible formula for this.

Answer 1

There is no built-in function in Excel that meets your need. You would need to customize your own formula in the VBA Project Module. So I managed to program a function that counts the number of matches. Adter setting this up into a module, you can use it as a normal formula in your worksheet.

If you are not familiar with VBA programming, do not worry. Follow the instructions and copy the code below into the VBA Module.

Function CountPartialMatch(R1 As String, R2 As String, M As Long) As Long

    Dim n As Long, L1 As Integer, L2 As Integer, Min As Integer, C As Integer, S1 As Integer, S2 As Integer
    n = 0
    L1 = Len(R1)
    L2 = Len(R2)
    Min = Application.WorksheetFunction.Min(L1, L2)

    For C = M To Min
        For S1 = 1 To (L1 - (C - 1))
            For S2 = 1 To (L2 - (C - 1))
                If Mid(R1, S1, C) = Mid(R2, S2, C) Then n = n + 1
                Next S2
            Next S1
        Next C

    CountPartialMatch = n

End Function

This formula requires 3 arguments:

=CountPartialMatch(First_Cell, Second_Cell, Min_Chrt)

The argument Min_Chrt is the minimum number of characters the match must have. So if you state 2, it will count all matches with 2 characters, 3 characters, 4, 5, 6... It reduces the number of coincidences. If it is 1, the formula will count every "a" = "a", letter = letter.

Please, let me know if it works fine for you!

Answer 2

If you are willing to use a VBA User Defined Function, you could try this one:

Cells_fMatch

It returns !Err if there is any inconsistency error with the input values (arguments), if a match is found it returns Match, otherwise returns NO Match (return values can be changed as required).

Syntax: Cells_fMatch( sCll_1, sCll_2[, iLenMin] )

Arguments & Description:

sCll_1 : String 1 to be compared

sCll_2 : String 2 to be compared

iLenMin : Optional Minimum length of the strings to be compared for a match. Default value is 1. Data type byte accepts a maximum value of 255, change accordingly if higher length is required.

The function combines the For...Next statement and the Instr function to perform the comparisons providing a fast and accurate results.

Public Function Cells_fMatch(sCll_1 As String, sCll_2 As String, Optional iLenMin As Byte = 1) As String
Dim blCllMatch As Boolean
Dim sCllVal As String
Dim i As Integer

    Rem Set Default Result
    Cells_fMatch = "!Err"

    Rem Validate Input
    If Len(sCll_1) < iLenMin Then Exit Function
    If Len(sCll_2) < iLenMin Then Exit Function

    Rem Compare Cell Values
    For i = 1 To (1 + Len(sCll_1) - iLenMin)
        sCllVal = Mid(sCll_1, i, iLenMin)
        If InStr(sCll_2, sCllVal) > 0 Then
            blCllMatch = True
            Exit For
    End If: Next

    Rem Set Results
    Cells_fMatch = IIf(blCllMatch, "Match", "NO MATCH")

End Function

Example: To compare strings in cells E2 and J2 matching a minimum of 5 consecutive characters (values 000034568MILL WALLI and WALLINGER respectivelly) use this formula:

=Cells_fMatch(E2,J2,5)

These pages will explain further about the resources used in the function:

Function Statement, Dim Statement,

If...Then...Else Statement, For...Next Statement, InStr Function.