Three hundred rows of free text land in a column, and somebody asks what people are saying. At that moment nobody knows, because nothing in the column has been counted, and the only way to count it is to read it. The reading is the work. The point of having a method is to make sure the reading happens once instead of three times, and that whatever comes out of it can be defended when someone asks how the number was produced.
Open text is not harder to analyze than a rating scale. It is harder to analyze carelessly and get away with it, because there is no summary view that hides the judgement being made. Every count from a free text column rests on a rule somebody applied, and the quality of the analysis is the quality of that rule.
Decide which of the two jobs this is
An open question is asked for one of two reasons, and the two need opposite handling.
The first job is measurement. How many people mentioned price, how many mentioned setup, whether the mix moved since last quarter. This produces counts, so it lives or dies on consistency. Every answer has to be judged against the same rule, and the rule has to survive a second person applying it to the same answers.
The second job is triage. Somewhere in those three hundred rows is a cancellation that can still be saved, a bug nobody has filed, and a person who cannot log in. For those, counting is a distraction. What matters is that each one reaches a named person quickly and that anyone looking at the list can tell which ones have already been dealt with.
Most teams need both and run them as a single pass, which is the reason both come out weak. The pass gets interrupted every few rows by something that needs an email, so the coding drifts, and the urgent answers still wait until the whole column has been read. Run triage first and fast, on the whole set, sorting only into needs a reply and does not. Then code for measurement with nothing else competing for attention. The second pass is faster than expected, because by then the shape of the answers is familiar.
Read a sample before deciding what to count
Categories imposed before reading come from the assumptions of whoever wrote the survey. Categories built from the text come from the respondents. The second kind is worth having, and it costs one afternoon.
How much to read, and which ones
Take a random sample rather than the first fifty rows. People who answer in the first hours after a send are not a cross section: they are the most engaged and often the most annoyed, and a codebook built on them will over-weight complaints. Fifty to one hundred answers, drawn at random, is enough to see the recurring categories in most datasets. Sort by a random number and take from the top, so the sample can be described precisely later.
What a usable code looks like
A code is about one thing and can be told apart from its neighbours without a discussion. Usability is not a code. Could not find where to export is a code. Aim for eight to fifteen codes plus a residual bucket, and let one answer carry more than one code, because answers that mention two problems are common and forcing a single choice throws away half of what was said.
Watch the residual bucket. If more than roughly fifteen percent of sampled answers end up in it, the frame is wrong rather than the answers being unusual, and it is cheaper to rebuild the frame now than to explain the bucket later.
Write the rule down, in one line per code
Each code gets a sentence: what counts, one real example, and one near miss that does not count. This document is the difference between a number that can be compared to next quarter and a number that only means something to the person who produced it. It also makes a second coder possible, which is the only real check on this kind of work.
Three ways to attach codes, and what each costs
| Method | Good at | Fails at | Reasonable scale |
|---|---|---|---|
| Reading and tagging by hand | Nuance, sarcasm, answers that mention two things, spotting a category nobody predicted | Consistency once attention drops, and any repeat of the exercise later | Up to a few hundred answers |
| Keyword and wildcard formulas | Repeatability, speed, obvious vocabulary such as a product name or a price complaint | Negation, spelling, synonyms, and any answer phrased indirectly | Any volume, on simple codes |
| A language model given the codebook | Volume with reasonable nuance, and first drafts of a code frame | Silent drift, confident wrong labels, and results that cannot be reproduced exactly | Hundreds to thousands, with a checked sample |
| A dedicated text analytics tool | Repeated studies, large volumes, sharable coding schemes | Cost and setup time that only a recurring study justifies | Recurring studies at thousands of answers |
The practical answer is usually a combination. Hand-code the random sample and treat it as the reference set. Run the automated pass, by formula or by model, over everything including the sample. Then compare the two on the sample and report the agreement rate alongside the counts. Where agreement on a code is poor, that code is either badly defined or badly detected, and the count for it should not be presented as fact.
Never let model output become the headline number without that comparison. The failure mode is not a model that refuses to answer, it is a model that labels everything plausibly and shifts its own interpretation halfway down the file.
Where the spreadsheet stops helping
A spreadsheet handles this work longer than people expect, and then fails in specific ways worth knowing in advance.
Room is not the problem. An Excel worksheet holds 1,048,576 rows by 16,384 columns, and a single cell holds up to 32,767 characters, which is longer than anything a respondent will type into a form field. Converting between tools is where text gets lost: when an Excel file is converted to Google Sheets, any cell with more than 50,000 characters is removed, and a Sheets file is capped at 20 million cells or 100MB.
Counting is where the quiet errors live. COUNTIF ignores upper and lower case, which is usually helpful, and accepts the wildcards asterisk and question mark, which is how most keyword coding gets done. It also returns incorrect results when the criteria string is longer than 255 characters, so long phrase matching has to be split and joined with the concatenation operator.
The larger problem is that substring matching has no idea what a sentence means. A rule looking for expensive also catches not expensive at all. A rule looking for support catches supporting documents. A rule looking for slow catches slowly getting better. Negation and hedging are the two things keyword coding cannot see, and both are common in survey text.
Two habits reduce the damage. Keep the raw answer untouched in its own column and never overwrite it with a cleaned version. And store codes as one column per code holding a one or a zero, rather than one column holding a comma separated list of tags. A single tag column looks tidier and is unusable: every count then depends on substring matching again, and a pivot table treats each unique combination as its own category.
Turn codes into numbers that survive a question
The denominator decides whether the finding is honest. Percentages from an open question belong over the number of people who answered that question, not over everyone who submitted the form. State both figures every time, because the gap between them is itself a finding. If a third of respondents skipped the open box, the counts describe the people who had something to say, which is a different population from the people who filled in the form.
Below about thirty answers, report counts rather than percentages. Eight out of twenty three is a sentence a reader can weigh. Thirty five percent, from the same data, invites a decision the sample cannot support.
Quotes illustrate, they do not prove. Choose them after the counting is finished, one per code, and pick the clearest rather than the most extreme. Check every quote for details that could identify the person who wrote it, particularly in employee surveys and in small customer bases, because a single mention of a team name can undo a promise of anonymity.
Comparisons over time are only valid if the codebook did not change. When it does change, and eventually it will, say so in the same sentence as the number. A shift from eleven percent to nineteen percent means nothing if the definition of the code widened between the two rounds.
The answers that need a reply, not a tally
Triage has different requirements from measurement, and standard survey exports meet none of them. A CSV has no field for who is handling a response and no field for whether it has been handled. Teams add two columns to the export and maintain them by hand, which works at twenty answers a month and fails at two hundred. The failure is visible from the outside: one person gets two different replies, another gets none, and nobody can say which happened.
Google Forms shows responses in a summary view, a per question view and an individual view, and links to a spreadsheet or exports a CSV. Notifications for new responses can be switched on, with more options and customised follow up mail available through a separate add-on. All of that is built around the form and its aggregate answers, not around one response and its progress, which is the correct design for a form tool and the wrong fit for a queue.
If the same open question keeps producing work rather than just findings, the answers belong somewhere that holds an owner and a status on the response itself, so that the question of what is still outstanding is a filter instead of a reread. That applies to most intake with an open box on the end, from application forms to support and repair requests, where the free text is the entire reason the form exists.
Ask it so that next time costs less
Most of the cost of analyzing open text is decided when the question is written.
One open question per form, placed last, collects better material than three scattered through it. Leave it optional: a required text box produces n/a, none and rows of random letters, and those are worse than a blank because they have to be filled in and then excluded.
Anchor the question to the closed question before it. Asking what is the main reason for that score, directly after a rating, produces answers that are already about one thing and already sorted into groups by the rating. Asking for any other comments produces a column that cannot be coded because the answers are not about the same subject.
Pair every open question with something countable. The open box explains why a number moved. It is a poor instrument for establishing that the number moved at all.
What to change first
Take the open question that produced the most answers, draw fifty at random, and code them by hand against a written one line rule per code. If the residual bucket stays under fifteen percent, the frame is usable on the rest of the file. If those answers also need replies rather than counts, put them somewhere that keeps the answer, the owner and the reply together, which is what Halict is built to do.
Q1. How many open-ended responses need to be read before the themes are clear?
A random fifty to one hundred is usually enough to see the recurring categories, and new categories tail off quickly after that. The important part is that the sample is random rather than the first rows in the file, because early respondents skew engaged and critical. Once the codebook is stable, the rest of the file can be coded by rule or by model with a checked sample.
Q2. Can a language model do the coding?
It can label at volume, and it is genuinely useful for drafting a first code frame from a sample. The risk is that it applies its own definitions and changes them partway through a long file without saying so. Hand-code a sample first, run the model over everything including that sample, and publish the agreement rate on the sample next to the counts.
Q3. Why do keyword formulas in a spreadsheet give the wrong counts?
Because substring matching cannot read a sentence. A search for expensive also matches not expensive, and a search for support matches supporting documents. COUNTIF is also case insensitive and returns incorrect results when the criteria string runs past 255 characters. Keyword rules are fine for product names and other fixed vocabulary, and unreliable for anything involving negation.
Q4. Should percentages be based on all respondents or only those who answered the open question?
On the people who answered the open question, with both numbers stated so the reader can see how many skipped it. A high skip rate is a finding in itself rather than a footnote. Below roughly thirty answers, give counts instead of percentages.
Q5. What is the best way to handle responses that need a reply rather than a count?
Separate them out in a first fast pass and give each one an owner and a status, then do the counting afterwards with nothing competing for attention. Two hand-maintained columns in a spreadsheet cover this up to about twenty a month. Past that, the tracking needs to sit on the response itself, or replies start getting duplicated and dropped.
Q6. How can this quarter's open text be compared with last quarter's?
Only by using the same codebook, which means writing it down the first time with an example and a near miss for every code. If a code is widened or split in between, report that alongside the change in the number, because a definition change can produce a bigger swing than any real shift in what respondents said.