Posted to microsoft.public.excel.programming
|
|
Most popular word
Thank you very much!
Umby
-----Original Message-----
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
.
|