Excel Longest Word of Cell Formula

B

bsimecek

I am trying to put together a formula that will find the longest word i
a cell. The cell in question will have a sentance like structure.
have a VBA sub that will do it, but I need to find a formula that wil
do the same.

The VBA is:
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

Does anyone have an idea
 
D

Dave Peterson

How about using your function (not really a sub):

=longestword(a1)

And you can use your UDF.

Or were you really looking for a formula using builtin worksheet functions?

If you keep this function in your personal.xls workbook, you can use it on any
open workbook:

=personal.xls!longestword(a1)
 
J

Jason Morin

Try this:

1. Press Ctrl+F3 and define the name "bre" (no quotes) as:

=MID($A1,ROW(INDIRECT("1:"&LEN($A1))),TRANSPOSE(ROW
(INDIRECT("1:"&LEN($A1)))))

Press OK.

2. Define another name "tah" as:

=MAX(IF(ISERROR(SEARCH(" ",bre)),LEN(bre)))=IF(ISERROR
(SEARCH(" ",bre)),LEN(bre))

3. Now put this in a cell and press ctrl/shift/enter:

=INDEX(bre,MAX(tah*ROW(INDIRECT("1:"&ROWS(tah)))),MAX
(tah*TRANSPOSE(ROW(INDIRECT("1:"&ROWS(tah))))))

I have it set to look at column A, starting with row 1.

HTH
Jason
Atlanta, GA
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top