SOLVED

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

Copper Contributor

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

Hi @Rollercoasters4me 

 

Sample.png

 

 

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

 

@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?Screenshot 2023-12-28 144826.png 

best response confirmed by Rollercoasters4me (Copper Contributor)
Solution

@Rollercoasters4me 

 

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)

 

(Benefits of using Tables instead of Ranges)

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

@Rollercoasters4me 

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

@Rollercoasters4me 

 

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)

 

(Benefits of using Tables instead of Ranges)

View solution in original post