I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?


Thanks,

Greg

Try this small UDF:

Function divergence(r1 As Range, r2 As Range) As Integer
v1 = r1.Value
v2 = r2.Value
divergence = 0
i = Application.WorksheetFunction.Max(Len(v1), Len(v2))
For j = 1 To i
ch1 = Mid(v1, j, 1)
ch2 = Mid(v2, j, 1)
If ch1 = ch2 Then
Else
divergence = j
Exit Function
End If
Next
End Function

If the two strings exactly match, it will return a zero. It will also catch
case changes as well.
--
Gary''s Student - gsnu2007i


"Greg Lovern" wrote:

I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?


Thanks,

Greg

Thanks, but I should have mentioned that I can do it in a UDF too.

What I want is to do it with Excel's built-in worksheet functions. Can
it be done that way?


Thanks,

Greg


On May 30, 9:55 am, Gary''s Student
wrote:
Try this small UDF:

Function divergence(r1 As Range, r2 As Range) As Integer
v1 = r1.Value
v2 = r2.Value
divergence = 0
i = Application.WorksheetFunction.Max(Len(v1), Len(v2))
For j = 1 To i
ch1 = Mid(v1, j, 1)
ch2 = Mid(v2, j, 1)
If ch1 = ch2 Then
Else
divergence = j
Exit Function
End If
Next
End Function

If the two strings exactly match, it will return a zero. It will also catch
case changes as well.
--
Gary''s Student - gsnu2007i

"Greg Lovern" wrote:
I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

Greg,

Yes, it can be done with an array function. See my post.

HTH,
Bernie
MS Excel MVP


"Greg Lovern" wrote in message
...
Thanks, but I should have mentioned that I can do it in a UDF too.

What I want is to do it with Excel's built-in worksheet functions. Can
it be done that way?


Thanks,

Greg


On May 30, 9:55 am, Gary''s Student
wrote:
Try this small UDF:

Function divergence(r1 As Range, r2 As Range) As Integer
v1 = r1.Value
v2 = r2.Value
divergence = 0
i = Application.WorksheetFunction.Max(Len(v1), Len(v2))
For j = 1 To i
ch1 = Mid(v1, j, 1)
ch2 = Mid(v2, j, 1)
If ch1 = ch2 Then
Else
divergence = j
Exit Function
End If
Next
End Function

If the two strings exactly match, it will return a zero. It will also catch
case changes as well.
--
Gary''s Student - gsnu2007i

"Greg Lovern" wrote:
I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

You could try this UDF:

Option Explicit

Public Function MissMatch(String1 As Variant, String2 As Variant)
Dim aByte1() As Byte
Dim aByte2() As Byte

Dim k1 As Long
Dim k2 As Long


aByte1 = CStr(String1)
aByte2 = CStr(String2)
MissMatch = 0
If Len(CStr(String1)) 0 Then
For k1 = LBound(aByte1) To UBound(aByte1) Step 2
k2 = k2 + 1
If k1 UBound(aByte2) Then
MissMatch = k2
Exit For
Else
If aByte1(k1) < aByte2(k1) Then
MissMatch = k2
Exit For
End If
End If
Next k1
End If
End Function


Charles
__________________________________________________
The Excel Calculation Site
http://www.decisionmodels.com
"Greg Lovern" wrote in message
...
I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?


Thanks,

Greg

Greg,

Array enter (enter using Ctrl-Shift-Enter):

=SUM(MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1 )<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),1),ROW(IND IRECT("A1:A"&LEN(A2))),LEN(A2))))

With those strings in cells A2 and A3.

HTH,
Bernie
MS Excel MVP


"Greg Lovern" wrote in message
...
I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?


Thanks,

Greg

Thanks, that's exactly what I was looking for.

Greg


On May 30, 10:13 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Greg,

Array enter (enter using Ctrl-Shift-Enter):

=SUM(MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1 )<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),1),ROW(IND IRECT("A1:A"&LEN(A2))),LEN(A2))))

With those strings in cells A2 and A3.

HTH,
Bernie
MS Excel MVP

"Greg Lovern" wrote in message

...

I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

Hi Bernie,

Thanks again -- BTW, I'm not sure what the SUM is doing; it seems to
work as well for me like this:

=MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),
1)<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),
1),ROW(INDIRECT("A1:A"&LEN(A2))),LEN(A2)))


Thanks,

Greg


On May 30, 10:13 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Greg,

Array enter (enter using Ctrl-Shift-Enter):

=SUM(MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1 )<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),1),ROW(IND IRECT("A1:A"&LEN(A2))),LEN(A2))))

With those strings in cells A2 and A3.

HTH,
Bernie
MS Excel MVP

"Greg Lovern" wrote in message

...

I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

Greg,

You're right - <Blush I modified another function that used the SUM and forgot to take it off of
there...

Bernie
MS Excel MVP


"Greg Lovern" wrote in message
...
Hi Bernie,

Thanks again -- BTW, I'm not sure what the SUM is doing; it seems to
work as well for me like this:

=MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),
1)<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),
1),ROW(INDIRECT("A1:A"&LEN(A2))),LEN(A2)))


Thanks,

Greg


On May 30, 10:13 am, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:
Greg,

Array enter (enter using Ctrl-Shift-Enter):

=SUM(MIN(IF(MID(A2,ROW(INDIRECT("A1:A"&LEN(A2))),1 )<MID(A3,ROW(INDIRECT("A1:A"&LEN(A2))),1),ROW(IND IRECT("A1:A"&LEN(A2))),LEN(A2))))

With those strings in cells A2 and A3.

HTH,
Bernie
MS Excel MVP

"Greg Lovern" wrote in message

...

I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

On Fri, 30 May 2008 09:35:58 -0700 (PDT), Greg Lovern wrote:

I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?


Thanks,

Greg

You could use this **array** function.

Make sure the "99" is greater than the length of your longest string.

=MATCH(FALSE,MID(A1,ROW($1:$99),1)=MID(A2,ROW($1:$ 99),1),0)

To enter an **array** function, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

--ron

Excellent, thanks! :-)

Greg


On May 30, 11:26 am, Ron Rosenfeld wrote:
On Fri, 30 May 2008 09:35:58 -0700 (PDT), Greg Lovern wrote:
I have two strings that start with the same characters, but at some
unknown and varying point they begin to differ. I want the position
number of the first non-matching character.

For example:
-- table and chair
-- table plus chair
-- position of first non-matching character is 7 (the "a" in
"and", and the "p" in "plus").

In the above example, both strings start out "table ", then start to
differ beginning with the 7th character. How can I return the position
of the first non-matching character, which in this example is 7?

Thanks,

Greg

You could use this **array** function.

Make sure the "99" is greater than the length of your longest string.

=MATCH(FALSE,MID(A1,ROW($1:$99),1)=MID(A2,ROW($1:$ 99),1),0)

To enter an **array** function, hold down <ctrl<shift while hitting <enter.
Excel will place braces {...} around the formula.

--ron

On Fri, 30 May 2008 11:42:48 -0700 (PDT), Greg Lovern wrote:

Excellent, thanks! :-)

Greg

You're welcome. Glad to help.

By the way, the formula I gave you will return a #NA error if the two strings
match exactly. There are a variety of methods of handling this issue if that
is not desired.

Probably the simplest is to just embed it in an IF statement. e.g:

=IF(A1=A2,"Match",formula)

Also note that the formula, as provided, is NOT case sensitive. If you wanted
a case-sensitive formula, and also to check for a complete Match, then
something like:

=IF(EXACT(A1,A2),"Strings Match",MATCH(FALSE,
EXACT(MID(A1,ROW(1:99),1),MID(A2,ROW(1:99),1)),0))

should work.
--ron

On Fri, 30 May 2008 15:29:05 -0400, Ron Rosenfeld
wrote:

Should be:

=IF(EXACT(A1,A2),"Strings Match",MATCH(FALSE,
EXACT(MID(A1,ROW($1:$99),1),MID(A2,ROW($1:$99),1)) ,0))



--ron

Ron Rosenfeld wrote...
Should be:

=IF(EXACT(A1,A2),"Strings Match",MATCH(FALSE,
EXACT(MID(A1,ROW($1:$99),1),MID(A2,ROW($1:$99),1) ),0))

Variation on this. First, it's convenient to use a defined name like
seq referring to a formula like

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,256,1))

or also define a name like NCHARS referring to 256 or something else
and define seq as

=ROW(INDEX(Sheet1!$1:$65536,1,1):INDEX(Sheet1!$1:$ 65536,NCHARS,1))

Then use an array formula like

=MATCH(FALSE,MID(A1&"x",seq,1)=MID(A2&"y",seq,1),0 )

for case insensitive matches or

=MATCH(FALSE,EXACT(MID(A1&"x",seq,1),MID(A2&"y",se q,1)),0)

for case sensitive matches. These return LEN(A1)+1 if A1 = A2.