Forum Discussion
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 |
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)
(https://answers.microsoft.com/en-us/msoffice/forum/all/use-tables-not-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2)
5 Replies
Lorenzo 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?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)
(https://answers.microsoft.com/en-us/msoffice/forum/all/use-tables-not-ranges/992a6e69-9c06-4b01-9230-1e12a87b81b2)
