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.
About Author:
Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 10 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and Human Resource.
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