2ND Largest Word In A Cell

Hi there,

You can try to keep the function you have got, and insert the following:

Sub Second_LongestWord()
Dim Second_LongestWord As String
tString = "This is a Test String"
Second_LongestWord = LongestWord(Replace(tString, LongestWord(tString), ""))
End Sub

Hi bsimercek:

And if you use the formula for the longest word as suggested by fairwind, you can find the secondlongest word as depicted in the following ...

It looks like I can use:

Function Second_LongestWord(str) As String
'Dim Second_LongestWord As String
Second_LongestWord = LongestWord(Replace(str, LongestWord(str), ""))
End Function

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

If Agresti & Agresti is the cell in question, "Agresti" is returned as the longestWord and "&" is returned as the secondLongestWord. Is there a way to fix this?