Excel Longest Word of Cell Formula

Where have you put the code for the function?

It should be in a new module, goto Insert>Module while in VBE and put code in the new module.

It should appear on the function list under the category User defined.

Thanks!
Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!

Give a man fire, he will be warm for a day. Set a man on fire, and he will be warm for the rest of his life!

bsimecek - your error happened becouse there is a slight error in the first line when declaring the function.

Change it to Function LongestWord(ByVal str As String)

this is how it will work. (Office 2003 + WinXPPro tested.)

I would suggest that you add this user formula to your default excell template so it is loaded everytime you start excel. The forum has many articles describing how to do it.

In your default excel template:

Alt + F11 - open VBE
instert a module into your template
insert the code:

<font face=Courier New>            <SPAN style="color:#00007F">Function</SPAN> LongestWord(<SPAN style="color:#00007F">ByVal</SPAN> str <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>)
            <SPAN style="color:#007F00">' Returns the longest word in a string of words</SPAN>
            <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
            <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
            str = Application.Trim(str)
            x = Split(str, " ")
            LongestWord = x(0)
            <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(x)
            <SPAN style="color:#00007F">If</SPAN> Len(x(i)) > Len(LongestWord) <SPAN style="color:#00007F">Then</SPAN>
            LongestWord = x(i)
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> i
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>

</FONT>

press F4 to rename the module and give it a sounding name

Save and Close your work.

next time you open excel this function can be used just like Sum(A1:A2) and can be found in the User Defined Functions

Take care

Hi

If you want to use native formula, try:

=MID(A1,LEN(A1)+1-MATCH(MAX(FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" "))),FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" ")),0),MAX(FIND(" ",(RIGHT(A1,ROW(INDIRECT("1:"&LEN(A1))))&" ")))-1)

Needs to be entered with Ctrl + shift + enter.

Guys, Fairwinds - here we get into a very interesting subject....

Any idea how to make both, the formula and the UDF LongestWord, work with ranges?

That would be interesting

If you mean an aray with one word in each cell:

=INDEX(A1:A10,MATCH(MAX(LEN(A1:A10)),LEN(A1:A10),0))

(Ctrl + shift + enter)

If you mean an array with several words in each cell, I would not even try doing it with one single formula.

Fairwinds, - you are right, to have a bunch of nested array formulas might turn into a living hell

I played with the code a bit and came up with this one modification of the a.m. formula, which is calculating all the cells in a specified range, even if there are some empty cells in the range.

I am sure the code might be written smoother, but this one works for me, I would be glad to digest a healthy portion of criticism.

<font face=Courier New><SPAN style="color:#00007F">Function</SPAN> ShowLongestWord(<SPAN style="color:#00007F">ByVal</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range)
    <SPAN style="color:#00007F">Dim</SPAN> str <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> x <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> LongestWord <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
            ShowLongestWord = <SPAN style="color:#00007F">Empty</SPAN>
                 <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> rng
<SPAN style="color:#00007F">If</SPAN> IsEmpty(c) <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">GoTo</SPAN> empty_c:
                        str = c
                        str = Application.Trim(str)
                        x = Split(str, " ")
                        LongestWord = x(0)
                        <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(x)
                        <SPAN style="color:#00007F">If</SPAN> Len(x(i)) > Len(LongestWord) <SPAN style="color:#00007F">Then</SPAN>
                        LongestWord = x(i)
                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
empty_c:
                        <SPAN style="color:#00007F">Next</SPAN> i
                                <SPAN style="color:#00007F">If</SPAN> Len(LongestWord) > Len(ShowLongestWord) <SPAN style="color:#00007F">Then</SPAN>
                                ShowLongestWord = LongestWord
                                <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
            <SPAN style="color:#00007F">Next</SPAN> c
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

Is there a way to change this VBA to show the second longest word in a cell?

Yes, there is. Just introduce another variable, call it second longest and introduce another IF statement, which will compare and save results.

Just another level in evaluation.

Is there any issue with using the replace function such as this?

http://www.mrexcel.com/board2/viewtopic.php?p=529741#529741