Mar 07 2022 11:14 AM
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...
Mar 07 2022 11:26 AM
SolutionLet'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.
Mar 07 2022 11:26 AM
SolutionLet'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.