# How to Find the Longest Word in a Cell in Excel

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
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)
```

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.

Formula : Find Second 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.

Related Posts