Finding the character where two strings do not match

Anonymous

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. :)

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

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)))))

0 comments

Answer accepted by question author

Anonymous

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

0 comments

48 additional answers

Anonymous

2010-06-13T11:18:09+00:00

Most of the formula here; at least those I tested, return (length of string+ 1) if the strings are the same, this returns an error if the strings are the same length hence the EXACT statement.

I used EXACT as opposed to Bernd's =(B2<>C2*.... because that defeats the case sensitivity of the formula.

=IF(EXACT(B2,C2),"Strings Match",MATCH(FALSE,EXACT(LEFT(B2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2))))),LEFT(C2,ROW(INDIRECT("1:"&MAX(LEN(C2),LEN(B2)))))),0))

*ARRAY entered CTRL+Shift+Enter

If this post answers your question, please mark it as the Answer.

Mike H

Hello,

Then take

=(1-EXACT(B2,C2))*LOOKUP(...)

:-)

Regards,

Bernd

www.sulprobil.com
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-13T10:32:15+00:00

Most of the formula here; at least those I tested, return (length of string+ 1) if the strings are the same, this returns an error if the strings are the same length hence the EXACT statement.

I used EXACT as opposed to Bernd's =(B2<>C2*.... because that defeats the case sensitivity of the formula.

=IF(EXACT(B2,C2),"Strings Match",MATCH(FALSE,EXACT(LEFT(B2,ROW(INDIRECT("1:"&MAX(LEN(B2),LEN(C2))))),LEFT(C2,ROW(INDIRECT("1:"&MAX(LEN(C2),LEN(B2)))))),0))

*ARRAY entered CTRL+Shift+Enter

If this post answers your question, please mark it as the Answer.

Mike H
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-13T09:07:25+00:00

Actually, since we are returning only numbers now, we can eliminate that IF function call...

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

and similarly for the case-insensitive formula as well.
Was this answer helpful?

0 comments No comments