Excel : Find longest word in a cell

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.

Excel Tutorials : 100 Excel Tutorials

About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 7 years of experience in data science and predictive modeling. During his tenure, he has worked with global clients in various domains like banking, Telecom, HR and Health Insurance.

While I love having friends who agree, I only learn from those who don't.

Let's Get Connected: Email | LinkedIn

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
1 Response to "Excel : Find longest word in a cell"
  1. Hi, what if I need the second largest word, is ther a formula for it? Thanks.


Next → ← Prev