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-14T21:22:59+00:00

    For those that enjoy arrays I have devised a shorter array than my previously suggested one, and it does not require 2007 and it handle leading spaces in the second argument, however, it does not handle errors elegenty, it propogates them.  F2 below:

    All of these formulas handle all special characters in the ASCII code set. 

    Summary of analysis:

    F1: =IFERROR(MATCH(0,--EXACT(MID(A1,ROW($1:$10),1),MID(B1,ROW($1:$10),1)),),LEN(A1)+1)

    F2:  =MATCH(1,FIND(LEFT(" "&A1,ROW(INDIRECT("1:"&1+LEN(A1))))," "&B1),1)

    F3:  =LOOKUP(2,1/(1=FIND(LEFT(" "&A1,ROW(INDIRECT("1:"&1+LEN(A1))))," "&B1)),ROW(INDIRECT("1:"&1+LEN(A1))))

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

    Issue F1 F2 F3 F4

    The only position inwhich spaces cause problems is the leading position of the 2nd argument. | Array Entered | Yes | Yes | No | No |

    | Require 2007 or later | Yes | No | No | No |

    | Error propogation problem? | Yes | Yes | Yes | No |

    | Handle leading space in 2nd argument | Yes | Yes | Yes | No |

    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
  2. Anonymous
    2010-06-14T17:36:39+00:00

    Bernd Pl schrieb am 12.06.2010 11:25 Uhr:

    Function NonMatchPos(s1 As String, s2 As String) As Long

    [... Code ...]

    How will you distinguish these two results?

    "abc" / "abc"  => 4

    "abc" / "abcd" => 4

    And the following result is at least strange.

    "" / "" => 1

    The following code returns 0 if both strings are equal.

    Peter

    Function NotMatch(ByVal s1 As String, ByVal s2 As String) As Long

       For NotMatch = 1 To WorksheetFunction.Min(Len(s1), Len(s2))

          If Mid(s1, NotMatch, 1) <> Mid(s2, NotMatch, 1) Then Exit For

       Next

       If NotMatch > Len(s1) And Len(s1) = Len(s2) Then NotMatch = 0

    End Function

    Was this answer helpful?

    0 comments
  3. Anonymous
    2010-06-14T14:53:23+00:00

    I see you marked my modification of Bernd Pl's as the "Answer". I just wanted to advise you that you can mark more than one response to your question as an "Answer". Because my response was built on the formula that Bernd posted, I think it would only be fair if you would mark his message as an "Answer" too... plus, if your final solution made use of any other postings, you might want to consider including them as "Answers" as well.

    Was this answer helpful?

    0 comments