Classify text into categories with a Google Sheets formula
Any column of text, any list of categories: headlines, product names, feedback, notes. One formula picks a category per row and returns the option exactly as you wrote it, so the column is ready to pivot.
The pattern
=HUNCHPICK(A2:A1000, "tutorial|news|opinion|product update|other", "What kind of article is this?")In Excel: =HUNCH.PICK(A2:A1000, "tutorial|news|opinion|product update|other", "What kind of article is this?")
Options separated by |. The third argument is the question; leave it out and the default is "Which option best describes this text?", which is right most of the time.
Real outputs
| Row | A | B |
|---|---|---|
| 1 | Title | Kind |
| 2 | Step by step: connecting Google Sheets to your CRM with a webhook | tutorial |
| 3 | Our Q3 update: faster imports and a redesigned dashboard | product update |
| 4 | Why most lead scoring models fail, and what to do instead | opinion |
| 5 | Regulators approve the merger of two spreadsheet software makers | news |
Describe options with a colon
=HUNCHPICK(A2:A1000, "bug: something is broken or crashes|question: how-to or pricing question|feedback: praise or suggestion, no action needed|other")In Excel: =HUNCH.PICK(A2:A1000, "bug: something is broken or crashes|question: how-to or pricing question|feedback: praise or suggestion, no action needed|other")
Everything after the colon is a description the model reads; the part before it is what comes back in the cell. Use descriptions when two options could overlap, or when an option means something specific in your company.
One pick or several yes/no columns?
- One
HUNCHPICKcolumn when each row belongs to exactly one category and you want a pivot. - Several
HUNCHcolumns when a row can carry several tags at once, or when you want a probability per tag to threshold. - Both when you need the primary category and the full tag set. The columns are independent; nothing is charged twice for the same text inside one formula.
Always include other. Without it the model is forced to pick the least wrong option, and your counts inherit the error.
Questions
How many options can a list have?
Does the option text come back exactly as written?
Run it on your own column
Copy the template (or use the Excel add-in), paste your key, point the formula at your data. Every formula here has its Excel spelling underneath. 100 rows free to start, then $29 for 5,000 rows. Credits never expire and there is no subscription.
Reference: HUNCHPICK
More guides
- Text classification in Excel with a formula Classification
- Categorize support tickets in a spreadsheet Ticket triage
- Code open-ended survey responses in Google Sheets Survey coding
- Tag customer reviews by topic in Google Sheets Review tagging
- AI formulas for Google Sheets: what returns a number, what returns text AI formulas compared