"Umby" wrote...
Thank you for your function, however it doesn't consider
some character.
e.g. if cell value = "mip@it mip@it mip@it win win" ,
output = "mip"
...
-----Original Message-----
"Umby" ha scritto nel messaggio
. ..
|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?

in microsoft.public.it.office.excel
thread: cercare parole
date: dic 10, 2003
...

Rewrite the function to be more flexible. Also eliminate the MsgBox call in a
function - never a good idea.


Function mcw( _
textstr As String, _
Optional sep As String = " ", _
Optional matchpat As String = "\b\w{3,}\b", _
Optional caseinsens As Boolean = True _
) As String
'-------------------------------------------------------------
Dim wc() As Long, m As Object, mc As Object, re As Object
Dim k As Long, n As Long, wcmax As Long, t As String

t = textstr

Set re = CreateObject("VBScript.RegExp")
re.Pattern = matchpat
re.IgnoreCase = caseinsens
re.Global = True
Set mc = re.Execute(textstr)

If mc.Count = 0 Then Exit Function

ReDim wc(1 To mc.Count)

For Each m In mc
n = n + 1
k = Len(t)
re.Pattern = "\b" & m.Value & "\b"
t = re.Replace(t, "")
wc(n) = (k - Len(t)) / Len(m.Value)
If wc(n) wcmax Then wcmax = wc(n)
Next m

For k = 1 To n
If wc(k) = wcmax Then mcw = mcw & sep & mc.Item(k - 1).Value
Next k

Erase wc
Set m = Nothing
Set mc = Nothing
Set re = Nothing

mcw = Mid(mcw, Len(sep) + 1)
End Function


Change the second argument to use a word separator other than space. Change the
third and fourth arguments to use different definitions of 'word' as specified
by VBScript regular expressions.

--
Never attach files.
Snip unnecessary quoted text.
Never multipost (though crossposting is usually OK).
Don't change subject lines because it corrupts Google newsgroup archives.