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.
Love this Post? Spread the Word!
Comment and share to motivate us to write more!
About Author:

Deepanshu founded ListenData with a simple objective - Make analytics easy to understand and follow. He has over 8 years of experience in data science. During his tenure, he has worked with global clients in various domains like Banking, Insurance, Telecom and Human Resource.

Get Free Email Updates :
*Please confirm your email address by clicking on the link sent to your Email*
Related Posts:
2 Responses 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.

    ReplyDelete
  2. I 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

We have Zero Tolerance to Spam. Comments with links will be deleted immediately upon our review.

Next → ← Prev
Scroll to Top