Here is a very small UDF:

Public Function matchwords(r1 As Range, r2 As Range) As Integer
v1 = r1.Value
v2 = r2.Value
s1 = Split(v1, " ")
s2 = Split(v2, " ")
matchwords = 0
For i = 0 To UBound(s1)
vv1 = s1(i)
For j = 0 To UBound(s2)
vv2 = s2(j)
If vv1 = vv2 Then
matchwords = matchwords + 1
End If
Next
Next
End Function

So if A1 contains:
Now is the time for all


and B1 contains:
time is on our side

the formula:

=matchwords(A1,B1) will display 2

NOTE:

This is only a demo and not a full solution. For example, you need to be
concerned with multiple matches:

the
compared to
the the the

Also if you want upeer/lower case to afffect the match.


UDFs are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To use the UDF from the normal Excel window, just enter it like a normal
Excel Function

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To learn more about UDFs, see:

http://www.cpearson.com/excel/Writin...ionsInVBA.aspx



--
Gary''s Student - gsnu200904


"K" wrote:

Hi all,

If I have name such as Fidelity Funds - Global Technology Fund in column A
and name Technology Fund in column B. is there a way to present the result
of total number of matching words in column C? In this case, column C would
have 2 because of word Technology and Fund in Column A and B. I need to
apply that method to 8000 records.

Thanks,

K