Excel Longest Word of Cell Formula

[bsimecek]

I am trying to find a formula that will return the longest word in a cell. Each cell in question has a sentance like structure. I have found VBA code to do this, but I'm trying to find a formula.

Here is the VBA code:

Function LongestWord(str) As String
' Returns the longest word in a string of words
Dim x As Variant
Dim i As Long
str = Application.Trim(str)
x = Split(str, " ")
LongestWord = x(0)
For i = 1 To UBound(x)
If Len(x(i)) > Len(LongestWord) Then
LongestWord = x(i)
End If
Next i
End Function

Anyone have any Ideas?

 

[scrupul0us]

why not use vba, rather how would a formula benefit you more?

 

[Joe4]

Why not use the User Defined Function you provided? Any formula, provided that one can be written, will probably be very long and complicated.

 

[bsimecek]

Formulas will be easier to implement and I will not have to worry about crossing versions as much.

Any ideas?

 

[scrupul0us]

google on how to make a macro a custom formula.. thad be the easiest way (if possible)

 

[Norie]

I really think VBA is the answer.

eg How will you work out with a formula how many words there are?

The only problem I can see about version crossing is the use of the Split function, which AFAIK is only available in later versions.

You could overcome that writing your own Split function.

 

[Joe4]

Actually, UDFs are pretty easy to implement. All you need to do is cut and paste the code in the VB editor, then use it like any other formula, i.e.:

=LongestWord("A1")

 

[bsimecek]

I don't know that much about VBA, I understand the use of forumuals better. If I have a column in excel that needs to be processed, how would i use the vba for each record?

 

[bsimecek]

I recognize this line, but when used in excel 2003, I just get an #NAME? Error. Is there somthing that I need to set up seperately?

 

[Joe4]

You really don't need to know much of anything about VBA to use this code.

Simply go to Tools | Macro | Record New Macro and click OK. Then hit the Stop Button immediately. Now go to Tools | Macro | Macros, select the one macro (probably named Macro1) and click Edit. A window woll pop-up with VBA code. Simply cut and paste the code provided over top of what is already in there, save, and exit the VB editor.

You have now created the user defined fucntion that you can use like I specified in my last post.