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:54:47+00:00

    Hello,

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

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

    "|" and "#" should not appear in your input.

    Regards,

    Bernd

    PS: I do not like worksheet functions of this length :-)

    www.sulprobil.com

    You mean FORMULAS, right? <g>

    Programmers always want to reinvent the wheel.

    We're seeing you a lot in these forums, Bernd. Why didn't you post this much in the ng's?

    On a side note: while I disagree with your reasoning for not wanting to use Morefunc at all (open source), I am starting to come around to the idea of maybe not using it in Excel 2007 as it seems there are some "bugs" when using it in Excel 2007.

    --

    Biff

    Microsoft Excel MVP

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-11T20:27:11+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?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-11T20:01:56+00:00

    Your posting was titled "Finding the character where two strings do not match"... the only thing that made sense for that when the two strings were identical was to report one greater than the length of the strings (at the last character, that is, the length of the text, they still match). I figured whatever formula or cell you used my formula in would have to test for this condition. As for the #REF error when both cells are empty... I'm sorry, I forgot to check that condition... but you are right, just encase my formula in an IF function testing for both cells equalling the empty string. Since it seems my posting fulfills your need, might I suggest that you mark it as the "answer" to your question.

    Was this answer helpful?

    0 comments