This article explains how to find the longest word in a cell in Excel, along with examples.
I was asked to find the longest word in a cell. I was able to solve it via formula after an hour of scratching my head. However, it can be easily possible with UDF (User Defined Function).
 |
| Excel Formula : Find Longest Word |
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)
After pasting the above formula in your Excel sheet, Press F2 and Hit CTRL SHIFT ENTER to confirm this formula as it is an array formula. Simple Enter would return #N/A Error.
The above formula uses the MID, LEN, MATCH, MAX, FIND, RIGHT, and ROW functions to calculate the length of each word in the text and then finds the position of the space before the longest word and then extracts the longest word.
The following formula splits the text in cell A2 into an array of words and then finds the length of each word and returns the second largest length using the LARGE function. The INDEX function is then used to return the word with the corresponding length. This formula assumes that words are separated by spaces. Make sure there are no leading or trailing spaces in the cell. To extract the second longest word, you can use the following formula:
=INDEX(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),(ROW(INDIRECT("1:"&LEN(A2)))-1)*LEN(A2)+1,LEN(A2))),MATCH(LARGE(LEN(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (ROW(INDIRECT("1:"&LEN(A2)))-1)*LEN(A2)+1, LEN(A2)))), 2), LEN(TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))), (ROW(INDIRECT("1:"&LEN(A2)))-1)*LEN(A2)+1, LEN(A2)))), 0))
Make sure to hit CTRL SHIFT ENTER to confirm this formula instead of just pressing ENTER.
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 worked with global clients in various domains like Banking, Insurance, Private Equity, Telecom and HR.
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