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: Newest
Most helpful Newest Oldest
  1. Anonymous
    2010-06-13T08:09:19+00:00

    I don't really see a problem mixing the empty string with numbers as return values from formula; however, given that you don't, we could return 0 instead of the empty string from the modification to your formula which I posted...

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

    and then Custom Format the column using #;; (pound sign followed by two semi-columns) for the format pattern... that would display either the number if a number greater than 0 is returned or nothing if 0 is returned. That kind of meets both our preference, right? [grin]

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-13T06:18:23+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)))))

    Hello Rick,

    Thank you. I try to avoid mixing strings ("") and numbers within the same cells.

    If we want to spot identical inputs easily I would use

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

    If you test this expression with IF identical inputs would lead into the FALSE clause.

    We could also map string inclusions to result values -1 and -2, indicating that values less or equal zero show equality of the input strings, but IMHO this would stretch the formula approach a bit too far.

    [I think I should no longer edit any of my entries - I DO NOT LIKE FORMULAS OF THIS LENGTH :-)  Take my UDF]

    Regards,

    Bernd

    www.sulprobil.com

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-13T06:06:31+00:00

    Here is another shot:

    =SUMPRODUCT(--ISNUMBER(FIND(LEFT(" "&A1,ROW(INDIRECT("1:"&1+LEN(A1))))," "&B1)))

    It tests out as case sensitive, 2003 compatible, and is a non-array

    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,

    Same here, test with "abc" and " abc" or simply with "a" and " a".

    This formula is not correct.

    Regards,

    Bernd

    www.sulprobil.com

    Was this answer helpful?

    0 comments