Forum Discussion
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)
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)
- Wow! I am so impressed and super grateful! You were correct about the extra spaces so the last suggestion worked! Thank you, thank you!
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)