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-11T18:06:33+00:00

    Give this array-entered** formula a try...

    =MAX(IF(MID(B2,ROW(A1:A15),1)<>MID(C2,ROW(A1:A15),1),0,ROW(A1:A15)))+1

    Note: If the two pieces of text are identical, then the formula returns a value one greater than the length of the text. Also, as written, this formula is case-sensitive. If you need case-insensitivity, then try this array-entered** formula instead...

    =MAX(IF(UPPER(MID(B2,ROW(A1:A15),1))<>UPPER(MID(C2,ROW(A1:A15),1)),0,ROW(A1:A15)))+1

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

    Was this answer helpful?

    0 comments
  2. Anonymous
    2010-06-11T17:03:08+00:00

    Will there always be a point where the 2 entries don't match? Are the entries the same length?

    Can you post SEVERAL representative samples of the data you're dealing with and let us know what results you expect?

    --

    Biff

    Microsoft Excel MVP

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-11T16:57:17+00:00

    Try the following array formula:

    =IF(LEN(B2)=0,IF(LEN(C2)=0,"",LEFT(C2,1)),IF(LEN(C2)=0,LEFT(B2,1),MID(B2,MAX(((MID(B2,ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2)))),1)=(MID(C2,ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2)))),1)))*ROW(INDIRECT("1:"&MIN(LEN(B2),LEN(C2))))))+1,1)))

    This will return the first character from B2 that is different from the corresponding character position in C2. If B2 is empty and C2 is not, the first letter of C2 is returned. If B2 is not empty and C2 is empty, the formula returns the first letter in B2. If both B2 and C2 are empty, the result is an empty string.

    This is an array formula, so you MUST press CTRL SHIFT ENTER rather than just ENTER when you first enter the formula and whenever you edit it later. If you do this correctly, Excel will display the formula in the formula bar enclosed in curly braces { }. The formula will not work correctly if you do not use CTRL SHIFT ENTER. See www.cpearson.com/Excel/ArrayFormulas.aspx for much more information about array formulas.

    Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com

    Was this answer helpful?

    0 comments