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-13T06:02:02+00:00

Hi Rick,

You did read the original post? The OP is using 2007? You did read my post - I pointed out that the formula would not work in 2003?

I don't understand the 1/1= part, why not simplify bernd's origina formula to

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

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,

If you do not understand the 1/1= part then test with these 2 strings: "abc" and " abc" :-)

Regards,

Bernd

www.sulprobil.com
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-13T00:46:54+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
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-13T00:32:56+00:00

I completely missed the "F3 is array entered and won't work in 2003" note in your first "testing" message because when I responded to it, I was concentrating directly on the F2 formula you used in it and wasn't looking to try any of the other formulas out at that time. You didn't repeat the "F3 is array entered and won't work in 2003" note in your second "testing" message, so I was completely unaware that you had mentioned it initially... so I figured I would mention it. Let me apologize to you for my confusion over this point and to those following this thread for any confusion I may have caused them as well.
Was this answer helpful?

0 comments No comments