Umby

Try this

Function MFW(Rng As Range) As String

Dim arrWords As Variant
Dim RangeText As String
Dim i As Long
Dim CurrCount As Long
Dim MaxCount As Long
Dim MaxWord As String

RangeText = UCase(Rng.Text)

RangeText = Replace(RangeText, ".", "")
RangeText = Replace(RangeText, ",", "")
RangeText = Replace(RangeText, ";", "")
RangeText = Replace(RangeText, ":", "")

arrWords = Split(RangeText, " ")

For i = LBound(arrWords) To UBound(arrWords)
If Len(arrWords(i)) = 3 Then
CurrCount = (Len(RangeText) - _
Len(Replace(RangeText, arrWords(i), ""))) / Len(arrWords(i))
If CurrCount MaxCount Then
MaxWord = arrWords(i)
MaxCount = CurrCount
End If
End If
Next i

MFW = MaxWord

End Function

Note that I had to strip out punctuation because "yards." is not the same as
"yards". I don't make any representation that the ones I elimate will be
sufficient. Also, I converted everything to caps.


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"Umby" wrote in message
.. .
Hi all,
I need a function to get the most frequent word in a cell. The length of
the
output must be at least 3.
e.g. if a cell contents was "Vick rushed for 145 yards. Michael Vick
handled
things in regulation. established in 1881 ; Yards Gear for the Holidays
car
yards in crisis" , the output should be "yards".

Any idea?
Thank you.

Umby