Find longest word in cell

[BobMango]

Anyone have a quick way to find the length of the longest word within a cell? I want to use a macro to adjust my column widths based on this value.

 

[auto.pilot]

=LEN(A1) would display the number of characters in cell A1

 

[mvptomlinson]

Hi Bob, hopefully this gives you an idea of how to do it:

Sub BobMango()
Dim myArr, i As Long, bigWord As String
 
[COLOR=green]' Split activecell's value into an array using spaces as the delimiter[/COLOR]
myArr = Split(ActiveCell.Value, " ")
bigWord = ""
 
[COLOR=green]' Loop through the array and test if the current word is larger than[/COLOR]
[COLOR=green]' bigWord (initialized as blank). If so, bigWord is set to the[/COLOR]
[COLOR=green]' current word.[/COLOR]
For i = 0 To UBound(myArr)
    If Len(myArr(i)) > Len(bigWord) Then
        bigWord = myArr(i)
    End If
Next i
 
[COLOR=green]' Display the largest word in the cell via message box[/COLOR]
MsgBox bigWord
 
End Sub

 

[BobMango]

Thanks mvptomlinson! I think that will do the trick. I just need to lay that into a loop for all cells in a column and we'll be home free.

 

[mvptomlinson]

If you need assistance with that, let us know!