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-11T20:00:30+00:00

    Hello,

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

    " "&C2&REPT("#",1+LEN(B2)))),ROW(INDIRECT("1:"&2+LEN(B2))))

    "|" and "#" should be two different characters which do not appear in your input.

    Regards,

    Bernd

    www.sulprobil.com

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-11T19:29:40+00:00

    Wow... 1 2-hr meeting after my post and I have a whole slew of stuff. :)  Thanks!

    Rick, your last formula works well for what I'm looking for.  It does, however, report the character position that follows my string if there's a match:

    B2     C2

    blah  blah  Your formula=5

    I think I can get around that given I know that > len(b2)

    Also, if both B2 and C2 are empty, I get a #REF error.  I can probably get around that by encapsulating the function in an IF.

    Again, Thanks!

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-11T18:44:57+00:00

    Okay, forget the formula I posted... it will not always work correctly, nor is the first one case-sensitive. Instead, give this array-entered** formula a try (it is case-insensitive)...

    =MAX(IF(MID(B2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))),1)<>MID(C2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))),1),0,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))))+1)

    ** Commit either of these formulas by pressing Ctrl+Shift+Enter (NOT just enter by itself)

    Was this answer helpful?

    0 comments