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-14T14:08:47+00:00

    This is the answer (marked the wrong one earlier).

    I've actually combined these two and put a "settings" tab together so the case sensitive and insensitive can be used as needed (not my original intent, but could be darn useful).  Settings!A2 is Case Sensitive setting (Yes/No), and Settings!B2 is the length of text to show from the departure point (to make the text reported back more meaningful).  What I ended up with is:

    Column:

    A2:  A label to describe what's being compared.

    B2:  Indicator if the item is optional (informational only for the person using the sheet).

    C2:  Text 1 (Expected)

    D2:  Text 2 (Actual)

    E2:  Overall Pass/Fail:  =IF(Settings!$A$2="Yes",IF(EXACT(B2,C2),"Pass","Fail"),IF(B2=C2,"Pass","Fail"))

    F2:  1st Fail Point:  =IF(Settings!$A$2="Yes", IF(OR(D2="",D2="Pass"),"",LOOKUP(2,1/(1=FIND(LEFT(" "&B2,ROW(INDIRECT("1:"&1+LEN(B2))))," "&C2)),ROW(INDIRECT("1:"&1+LEN(B2))))), IF(OR(D2="",D2="Pass"),"",LOOKUP(2,1/(1=FIND(LEFT(" "&UPPER(B2),ROW(INDIRECT("1:"&1+LEN(B2))))," "&UPPER(C2))),ROW(INDIRECT("1:"&1+LEN(B2))))))

    G2:  Expected Text from Divergence:  =IF(OR(D2="", D2="Pass"), "", MID(B2,E2,Settings!$B$2))

    H2: Actual Text from Divergence:  =IF(OR(D2="", D2="Pass"), "", MID(C2,E2,Settings!$B$2))

    I2:  Size Comparison:  =IF(LEN(B2)=LEN(C2), "", IF(LEN(B2)>LEN(C2), CONCATENATE("Expected Larger by ", LEN(B2)-LEN(C2), " Char(s)"), CONCATENATE("Actual Larger by ", LEN(C2)-LEN(B2), " Char(s)")))

    This copies down to other rows easily.

    Thanks everyone for your help and input!  (Now I'm going to have to go learn about stuff entered as an array....)

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-14T00:12:36+00:00

    Hellow Bernd,

    I see you were just playing with me - you said to test abc against " abc" and of course if you put abc in A1 and space abc in B1 it works fine for all the formulas.  But I see...

    Actually they don't handle #Name! or other errors well either. 

    And of course none of these formulas handle sparklines!  Maybe VBA?

     

    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

    Hello Shane,

    I never play with people. I seriously try to answer the question stated.

    My answers to you provided examples for which your formulas simply did not work correctly:

    Both your SUMPRODUCT formula and your LOOKUP formula without 1/1= return for the input "a" and " a" the result 2 (correct is: 1).

    That your SUMPRODUCT formula can return a 0 if one input is #NAME? I have not noticed before you mentioned this (I use to stop with the first error detected).

    We all err from time to time - I think this is human (and I think it is ok to admit it). Now, in this thread I think you have shown that you are quite human, to be honest.

    Regards,

    Bernd

    www.sulprobil.com

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-13T15:18:13+00:00

    Hellow Bernd,

    I see you were just playing with me - you said to test abc against " abc" and of course if you put abc in A1 and space abc in B1 it works fine for all the formulas.  But I see...

    Actually they don't handle #Name! or other errors well either. 

    And of course none of these formulas handle sparklines!  Maybe VBA?

    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