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-11T22:49:57+00:00

    Rick,

    I have been watching this thread with great interest; but not contributing because it's way beyond me, and see your latest contribution. It works prefectly; well in my testing it does, except, unless I'm missing something it seems to have lost case sensitivity.

    It's already a 'keeper' but am I asking too much for you to make it case sensitive too?

    If this post answers your question, please mark it as the Answer.

    Mike H

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-11T22:30:21+00:00

    Okay, I think this modification to my formula works correctly. Give it a try and see. It is still an array-entered** formula...

    =IF(AND(B2="",C2=""),"",MIN(IF(MID(B2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))),1)<>MID(C2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))),1),ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2)))),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
  3. Anonymous
    2010-06-11T21:58:31+00:00

    I've noticed that I'm getting unexpected results.

    The formula is returning some strange results:

    Text 1             Text 2            Formula Result

    aaaaaa           aaaaab           6  (This is correct)

    blah                blax               4  (this is also correct)

    apple              bpple              6

    aaaaaaaaab    aaaaaaaaac    10

    aaaaaa           aabbab           6

     

    Now:

    If copy the formula to the next line, I get:

    Text 1             Text 2            Formula Result

    aaaaaa           aaaaab           2

    Once I go into the 2nd formula's cell and do a ctrl-shift-enter, the result is 5.

    Can array-entered functions be copy/pasteed?

     

    Hello Jordan,

    I suggest to give my (non-array) formula a try. I think it works (you will not have "|" or "#" in your data, right?).

    Regards,

    Bernd

    www.sulprobil.com

    Was this answer helpful?

    0 comments