retrieving cells with lots of text

[DrCLaireHooker]

Hi, I have survey data in an excel spreadsheet; there are a couple of free-text questions. While many respondents wrote only short answers to the free text questions, some wrote a LOT! Is there a way of:

(a) sorting the answers from the cells with most characters (ie the longest answers) to the shortest;

(b) finding all cells with more than a specified number or characters or words? eg all cells with more than 200 characters or more than 50 words?

thank you! I tried to search this community but maybe I know so little about excel that I could not even specify useful search terms...

[#2 · Accepted solution]

@DrCLaireHooker 

Let's say column F has free text answers, starting in F2 (with a header in F1).

Insert an empty column to the right of column F.

Enter Length or something like that in G1.

In G2, enter the following formula, then fill down:

=LEN(F2)

You can now sort the data on column G, or apply a filter based on column G.

You can do the same for other columns with free text.

[#3]

thank you so much!