CODEWITHSUNDEEP
excelstring
jake
jake

Reputation: 9

Excel - Checking if 2 strings match, if they don't, return the position of the string where the two diverge

I have two strings that I want to compare. The function I want is to basically:

(1) Check if the two strings match exactly

(2) If they do match, return TRUE

(3) If they do not match, return the position of the string where the two diverge

For example:

Cell A1: Barack Obama

Cell A2: Barack Obana

I know that these two strings don't match and the error is the "n" in "Obana". Therefore, the error happens at the string position of 10 in A2. I would like the function to return 10.

My attempt:

=IF(EXACT(A1,A2), "MATCH", ??(SEARCH(A1,A2,1))??

Thanks!

Upvotes: 0

Views: 878

Answers (2)

Scott Craner
Scott Craner

Reputation: 152585

Here is a formula:

=IF(EXACT(A1,A2),"MATCH",AGGREGATE(15,6,ROW(INDIRECT("1:" & MAX(LEN(A1),LEN(A2))))/(NOT(EXACT(MID(A1,ROW(INDIRECT("1:" & MAX(LEN(A1),LEN(A2)))),1),MID(A2,ROW(INDIRECT("1:" & MAX(LEN(A1),LEN(A2)))),1)))),1))

This is a long and convoluted formula and changing the references is not quick. The UDF option given by elmer007 would be easier to use and reference in the long run.

enter image description here

Upvotes: 1

elmer007
elmer007

Reputation: 1445

How about the following VBA function:

Function MatchOrDiverge(BaseString As String, ComparedString As String)

If BaseString = ComparedString Then
    MatchOrDiverge = "MATCH"
Else
    For i = 1 To Len(BaseString)
        If Not (Mid(BaseString, i, 1) = Mid(ComparedString, i, 1)) Then
            MatchOrDiverge = i
            Exit Function
        End If
    Next i
    MatchOrDiverge = Len(BaseString) + 1
End If

End Function

This takes 2 strings as input. First, it checks to see if the 2 strings are the same. If they are, it returns "MATCH".

If the 2 strings are not equal, it loops through the BaseString and checks its characters against the ComparedString. When a character does not match, it returns that character's index.

If the strings match, but the second one is longer (e.g., "cat" and "cattle"), then it returns the length of the BaseString + 1.

Screenshot in action:

enter image description here

Upvotes: 1

Related Questions