SOLVED

How to SORT/FILTER by number of words in a cell?

Copper Contributor

How to SORT/FILTER by number of words in a cell?

In Excel, is there a way to sort or filter only the cells that contain a single word in it?
Thank you for your help. I'm a newbie.
I have results that show how many times a single word or a phrase appeared in a text. I want to sort out the single words also with the quantity it appears.
For example, "of the" appears 16 times in the text, "for" appears 15 times in the text.

*I want to grab only the cells with a single word of text and the quantity it shows up.

I want the results to be...

15 for

14 have

13 life

12 this

etc

 16 of the 15 for 15 you will 14 have 14 will be 13 life 13 you may 12 this 12 there 11 that you may 11 blessings 10 heavenly 10 you are 10 is 10 always
5 Replies

Re: How to SORT/FILTER by number of words in a cell?

``````=FILTER(Table,
LEN(SUBSTITUTE(Table[Words]," ","")) = LEN(Table[Words])
)``````

Re: How to SORT/FILTER by number of words in a cell?

@L z. You are amazing! I kept trying your suggestion over and over and I finally got it to work on my sample text!! Wahoo!
Now, I can't get it to be successful on my full data. It is about 6145 rows long. The error code I get is "#CALC!".
Like I said, I am a newbie for sure. I use Microsoft365.
It looked like you formatted the data into a table. I did that and made sure my table was named "Table". In this trial run with the full data, I copied the sheet and added all data and checked the new table to be called "table5" and I double checked the new data range in table5.
Any thoughts?

best response confirmed by Rollercoasters4me (Copper Contributor)
Solution

Re: How to SORT/FILTER by number of words in a cell?

A #CALC! error with FILTER usually happens when, after filtering, there's nothing to return. To prevent that error you can amend your formula as follow:

``````=FILTER(Table5,
LEN(SUBSTITUTE(Table5[Words]," ","")) = LEN(Table5[Words]),
""
)``````

However, you will probably not get what you expect. Looking at your picture there seem to be a <space> at the begining of each [Words]. If it's really a space this could explain the issue. If you have space(s) at the begining and/or at the end of your [Words] you can update your formula as follow:

``````=FILTER(Table5,
LEN(SUBSTITUTE(Table5[Words]," ","")) = LEN(TRIM(Table5[Words])),
""
)``````

If this doen't fix the issue please post your actual worbook with uour > 6K rows (if not yet allowed to attach a file here you can upload & share it with OneDrive, Google Drive or the like)

Re: How to SORT/FILTER by number of words in a cell?

Wow! I am so impressed and super grateful! You were correct about the extra spaces so the last suggestion worked! Thank you, thank you!

Re: How to SORT/FILTER by number of words in a cell?

You were correct about the extra spaces Haha, spaces are rarely good friends in techno. :)

Glad this now works for you & Thanks for providing feeback and marking as solution (helps those who Search)

1 best response

Accepted Solutions
best response confirmed by Rollercoasters4me (Copper Contributor)
Solution

Re: How to SORT/FILTER by number of words in a cell?

A #CALC! error with FILTER usually happens when, after filtering, there's nothing to return. To prevent that error you can amend your formula as follow:

``````=FILTER(Table5,
LEN(SUBSTITUTE(Table5[Words]," ","")) = LEN(Table5[Words]),
""
)``````

However, you will probably not get what you expect. Looking at your picture there seem to be a <space> at the begining of each [Words]. If it's really a space this could explain the issue. If you have space(s) at the begining and/or at the end of your [Words] you can update your formula as follow:

``````=FILTER(Table5,
LEN(SUBSTITUTE(Table5[Words]," ","")) = LEN(TRIM(Table5[Words])),
""
)``````

If this doen't fix the issue please post your actual worbook with uour > 6K rows (if not yet allowed to attach a file here you can upload & share it with OneDrive, Google Drive or the like)