I was asked to find longest word in a cell. I was able to solve it via formula after an hour of scratching my head. It can be easily possible with UDF (User Defined Function).
![]() |
Excel Formula : Find Longest Word |
Formula : Find Longest Word in a Cell
Suppose your input value (text) is entered in cell A2
=MID(A2,LEN(A2)+1-MATCH(MAX(FIND(" ",(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2))))&" "))),FIND(" ",(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2))))&" ")),0),MAX(FIND(" ",(RIGHT(A2,ROW(INDIRECT("1:"&LEN(A2))))&" ")))-1)Press F2 and Hit CTRL SHIFT ENTER to confirm this formula as it is an array formula. Simple Enter would return #N/A Error.
Hi, what if I need the second largest word, is ther a formula for it? Thanks.
ReplyDeleteI would use the same formula on a different cell where I have substituted the find (the longest word) with an empty string in the original string.
ReplyDelete