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-11T21:36:30+00:00

Hello,

=LOOKUP(2,1/(1=FIND(LEFT(" "&A2&REPT("|",1+LEN(A1)),ROW(INDIRECT("1:"&2+LEN(A2)))),

" "&A1&REPT("#",1+LEN(A2)))),ROW(INDIRECT("1:"&2+LEN(A2))))

"|" and "#" should not appear in your input.

Regards,

Bernd

PS: I do not like worksheet functions of this length :-)

www.sulprobil.com

You mean FORMULAS, right? <g>

Programmers always want to reinvent the wheel.

We're seeing you a lot in these forums, Bernd. Why didn't you post this much in the ng's?

On a side note: while I disagree with your reasoning for not wanting to use Morefunc at all (open source), I am starting to come around to idea of maybe not using it in Excel 2007 as it seems there are some "bugs" when using it in Excel 2007.

--

Biff

Microsoft Excel MVP

Hello Biff,

Right, I mean formulas - but I deleted that because I like it far more than the other formulas I saw so far. (Btw: Why do you "sit" 54 minutes on my post? :-)

A side note: You think the number of my posts here is higher than in NG's previously? Well, let us see. With regards to Morefunc: It is a pity that we cannot get the source code. But since we obviously are not able to get it, I beg to differ with you. I think you would agree that it's fun to disagree sometimes (at least: with me :-).

Regards,

Bernd

www.sulprobil.com
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-11T21:44:24+00:00

Btw: Why do you "sit" 54 minutes on my post? :-)

Sorry, I have no idea what that means?

--

Biff Microsoft Excel MVP
Was this answer helpful?

0 comments No comments
Anonymous

2010-06-11T21:53:48+00:00

Btw: Why do you "sit" 54 minutes on my post? :-)

Sorry, I have no idea what that means?

--

Biff Microsoft Excel MVP

OT: I thought I had changed my post quite soon and I was surprised that you were able to see and to answer to the original one - but maybe I am mistaken ...

www.sulprobil.com
Was this answer helpful?

0 comments No comments