Finding the character where two strings do not match

Anonymous
2010-06-11T16:18:02+00:00

I have 2 cells B2 and C2 with strings.

I can tell whether they match or not easily enough.  But I am not sure how to identify the character where the two strings diverge.  Can it be done with a formula (ideas?) or do I need to create a new one (eh?)?

Thanks all. :)

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2010-06-12T14:02:24+00:00

Nice formula Bernd; however, I would suggest this modification to it so that it can be copied down into blank rows (in anticipation of future entries)...

=IF(AND(B2="",C2=""),"",LOOKUP(2,1/(1=FIND(LEFT(" "&B2,ROW(INDIRECT("1:"&1+LEN(B2))))," "&C2)),ROW(INDIRECT("1:"&1+LEN(B2)))))

I would note for the OP that this formula is entered normally (that is, it is NOT an array-entered formula). And I would also note that the formula is is case-sensitive; the case-insensitive version of it would be this...

=IF(AND(B14="",C14=""),"",LOOKUP(2,1/(1=FIND(LEFT(" "&UPPER(B14),ROW(INDIRECT("1:"&1+LEN(B14))))," "&UPPER(C14))),ROW(INDIRECT("1:"&1+LEN(B14)))))

Was this answer helpful?

3 people found this answer helpful.
0 comments
Answer accepted by question author
Anonymous
2010-06-12T09:25:06+00:00

I have 2 cells B2 and C2 with strings.

I can tell whether they match or not easily enough.  But I am not sure how to identify the character where the two strings diverge.  Can it be done with a formula (ideas?) or do I need to create a new one (eh?)?

Thanks all. :)

Hello,

I suggest to use

=LOOKUP(2,1/(1=FIND(LEFT(" "&B2,ROW(INDIRECT("1:"&1+LEN(B2))))," "&C2)),ROW(INDIRECT("1:"&1+LEN(B2))))

Regards,

Bernd

PS: [Edited on 12-June 11:57 GMT] If VBA is an option you can also use

Function NonMatchPos(s1 As String, s2 As String) As Long

Dim i As Long

i = 1

Do While i <= Len(s1) And i <= Len(s2)

    If Mid(s1, i, 1) <> Mid(s2, i, 1) Then Exit Do

    i = i + 1

Loop

NonMatchPos = i

End Function

www.sulprobil.com

Was this answer helpful?

1 person found this answer helpful.
0 comments

48 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2010-06-12T00:18:01+00:00

    Taking some some off, here is another case sensitive solution with the followign test results:

    aaaaaa aaaaab 6 6 6

    | blah | blax | 4 | 4 | 4 | | apple | bpple | 1 | 6 | 1 | | aaaaaaaaab | aaaaaaaaac | 10 | 10 | 10 | | aaaaaa | aabbab | 3 | 6 | 3 | | a | asdfg | 2 | 2 | 2 | | aaa | a | 2 | 2 | 2 | | Abc | abc | 1 | 4 | 1 | | aBc | aBC | 3 | 4 | 3 |

    A                  B                   F1       F2      F3 | A | B | 1 | 1 | 1 | | abc | abC | 3 | 4 | 3 | | abc | abc | 4 | 4 | 4 | | aaaab | aaaab | 6 | 6 | 6 |

    F1: =LOOKUP(2,1/(1=FIND(LEFT(" "&A1&REPT("|",1+LEN(B1)),ROW(INDIRECT("1:"&2+LEN(A1)))),

    " "&B1&REPT("#",1+LEN(A1)))),ROW(INDIRECT("1:"&2+LEN(A1))))

    F2: =MAX(IF(MID(A1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(B1)))),1)<>MID(B1,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(B1)))),1),0,ROW(INDIRECT("1:"&MAX(LEN(A1),LEN(B1)))))+1)

    F3: =IFERROR(MATCH(FALSE,EXACT(MID(A1,ROW($1:$10),1),MID(B1,ROW($1:$10),1)),0),LEN(A1)+1)

    F3 is array entered and won't work in 2003.

    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-11T23:32:21+00:00

    I've seen some of your responses to question that others have asked here in the forums and I don't believe for a moment that this is "way beyond" you. Anyway, yes, as I indicated in one of my earlier postings, I was mistaken about the case-sensitivity claim in my original message. If you look elsewhere in this thread, you will see a UDF that I posted which does offer a case-sensitivity option... perhaps that will be something you could use in the future.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-11T22:57:27+00:00

    By the way, if you are up for using a UDF (user defined function), then you might want to consider this one...

    Function Departure(Text1 As String, Text2 As String, Optional CaseSensitive As Boolean) As Variant

      If Text1 = "" And Text2 = "" Then

        Departure = ""

      Else

        For Departure = 1 To WorksheetFunction.Min(Len(Text1), Len(Text2))

          If StrComp(Mid(Text1, Departure, 1), Mid(Text2, Departure, 1), -CaseSensitive) <> 0 Then Exit Function

        Next

      End If

    End Function

    I have provided an optional third argument (named CaseSensitive) that, as the name implies, will allow you to do the comparison either case-sensitive (pass TRUE for this option) or non-case-sensitive (either pass FALSE or omit the argument altogether for this option). So, your call to this UDF on the worksheet would be...

    Case-sensitive:  =Departure(B2,C2,TRUE)

    Case-insensitive:  =Departure(B2,C2)    [or]     =Departure(B2,C2,FALSE)

    Was this answer helpful?

    0 comments