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?

 

[Depi mour]

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?

Hello, I would like to ask about tis code, it finds the first longest word but it takes , also. Do you know any solution? I want to count the longest word only, without , ?.

Thank you very much