Word frequency and top emoji from comments in Google Sheets
Reading two thousand comments takes an afternoon. Counting their words takes one formula and shows, in a list of fifty rows, what the section is about. Three functions do the work: SPLIT cuts each comment into words, FLATTEN pours them into one column, and QUERY counts them.
Updated Nov 19, 2026 · 7 min read
Set up
Put the comments in a tab named Comments, with the text in column E. Copy for Sheets, a live =IMPORTDATA link or an import of the workbook all give that layout. The formulas below go on a second tab and read from the first, so the data stays untouched.
The ranges stop at row 5000. Raise that number if the export is longer; open-ended ranges such as E2:E also work and are slower on big sheets.
The word count in one formula
=QUERY(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT(LOWER(REGEXREPLACE(REGEXREPLACE(Comments!E2:E5000&"", "@[A-Za-z0-9._]+", " "), "[^\p{L}\p{N}\s]", " ")), " ")))), "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc limit 50 label Col1 'word', count(Col1) 'times'", 0)
From the inside out:
Comments!E2:E5000&""turns every cell into text. A comment that is only a number would otherwise break the next step.- The inner
REGEXREPLACEremoves @mentions, so tagged friends' handles are not counted as words. - The outer
REGEXREPLACEreplaces everything that is not a letter (\p{L}), a digit (\p{N}) or a space with a space. Punctuation and emoji go; letters of every alphabet stay. LOWERmakes "Love" and "love" the same word.SPLIT(…, " ")cuts each comment at the spaces, giving one row per comment and one column per word.IFERRORblanks the rows where there was nothing to split, andARRAYFORMULAmakes all of this run on the whole column.FLATTENturns that wide grid into one tall column of words.QUERYgroups the column, counts each word and keeps the top 50.Col1is how QUERY names the single column of an array that has no letters.
Remove the stop words
The first result is always "the", "i", "this", "so", "to". Add a tab named Stop and list the words to ignore in column A, one per cell: the, a, an, and, or, to, of, in, on, for, is, it, i, you, me, my, so, this, that, with, at, be, are, was, your. Add the equivalents for other languages your audience uses: de, la, el, que, y, en, un, una, para, o, a, os, as, um, uma, é, não, com.
Then change the where clause so QUERY skips them:
=QUERY(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT(LOWER(REGEXREPLACE(REGEXREPLACE(Comments!E2:E5000&"", "@[A-Za-z0-9._]+", " "), "[^\p{L}\p{N}\s]", " ")), " ")))), "select Col1, count(Col1) where Col1 is not null and not Col1 matches '" & TEXTJOIN("|", TRUE, Stop!A:A) & "' group by Col1 order by count(Col1) desc limit 50 label Col1 'word', count(Col1) 'times'", 0)
TEXTJOIN glues the stop words into the|a|an|and|…, and matches compares each word against that whole pattern. A word must equal one of the stop words exactly to be dropped, so "to" does not remove "tomorrow". Adding a word to the Stop tab updates the count at once. Keep going until the list reads like topics.
Count people, not repetitions
One person writing "love love love love" adds four to the count. For most questions you want to know in how many comments a word appears. Deduplicate the words within each comment before flattening. Put the cleaned text in a helper column on the Comments tab first (column M holds platform, so use N), with the header clean:
=ARRAYFORMULA(IF(E2:E5000="", "", TRIM(LOWER(REGEXREPLACE(REGEXREPLACE(E2:E5000&"", "@[A-Za-z0-9._]+", " "), "[^\p{L}\p{N}\s]", " ")))))
Then count the comments containing each of your top words. With the words from the first formula in A2 downward on the count tab, in C2:
=COUNTIF(Comments!N$2:N$5000, "* " & A2 & " *") + COUNTIF(Comments!N$2:N$5000, A2 & " *") + COUNTIF(Comments!N$2:N$5000, "* " & A2) + COUNTIF(Comments!N$2:N$5000, A2)
The four parts count the word in the middle, at the start, at the end, and as the whole comment, so only whole words are matched. Column C divided by the number of comments is the share of comments mentioning the word, which is the number to quote. The helper column also feeds sentiment analysis in Google Sheets.
Two-word phrases
Single words lose meaning: "stock" could be "in stock" or "out of stock". Count pairs of neighbouring words from the clean column:
=QUERY(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT(REGEXREPLACE(Comments!N2:N5000, "(\S+) (\S+) ?", "$1_$2|"), "|")))), "select Col1, count(Col1) where Col1 contains '_' group by Col1 order by count(Col1) desc limit 30 label Col1 'pair', count(Col1) 'times'", 0)
The pattern joins the first and second word with an underscore, then the third and fourth, and so on, and marks the end of each pair with |. It counts every second pair, which is enough to bring the common phrases to the top. Read the result with the underscores as spaces: how_much, need_this, in_stock.
Top emoji
The cleaning step threw the emoji away, so count them from the original text with the opposite pattern. \p{So} is the Unicode class "symbol, other", which holds nearly all emoji:
=QUERY(FLATTEN(ARRAYFORMULA(IFERROR(SPLIT(REGEXREPLACE(REGEXREPLACE(Comments!E2:E5000&"", "[^\p{So}]", ""), "(\p{So})", "$1 "), " ")))), "select Col1, count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc limit 20 label Col1 'emoji', count(Col1) 'times'", 0)
The inner REGEXREPLACE deletes everything that is not such a symbol. The outer one puts a space after each symbol so SPLIT can separate them. The rest is the same count as before.
Three things to expect in the result:
- Skin tones merge. 👍🏽 and 👍 are both counted as 👍, because the tone is a separate modifier character that the pattern drops. For a top-20 list that is what you want.
- Flags split. A flag is two regional letters, and each is counted on its own.
- Joined emoji split. Family and profession emoji are several symbols glued together and are counted as their parts.
To count comments that are emoji and nothing else, use the helper column: =SUMPRODUCT((Comments!E2:E5000<>"") * (Comments!N2:N5000="")). A comment with text in E and nothing left in clean had no letters or digits.
One network at a time
In a sheet that stacks several networks, wrap the comment range in a FILTER on the platform column. Replace Comments!E2:E5000&"" in any formula above with:
FILTER(Comments!E2:E5000, Comments!M2:M5000="tiktok")&""
Running the word count once per network and placing the lists side by side is one of the quickest comparisons there is. The stacking itself is described in compare comments across networks in one sheet. The same trick with Comments!H2:H5000="no" limits the count to top-level comments, which keeps long back-and-forth threads from dominating.
The Excel version
Excel has no FLATTEN or QUERY. In Microsoft 365, with the cleaned text in N (built with Excel's own REGEXREPLACE, or with LOWER, TRIM and SUBSTITUTE as in how to clean comments in a spreadsheet):
=LET(w, TEXTSPLIT(TEXTJOIN(" ", TRUE, Comments!N2:N1000), , " ", TRUE), u, UNIQUE(w), c, BYROW(u, LAMBDA(x, SUM(--(w=x)))), TAKE(SORTBY(HSTACK(u, c), c, -1), 50))
TEXTJOIN makes one long text of all comments, TEXTSPLIT cuts it into a column of words, and BYROW counts each unique word. TEXTJOIN stops at 32,767 characters, which is roughly 500 to 1,000 comments. Past that, use Power Query: load the table, select the clean column, Split Column, By Delimiter, Space, with Advanced options, Split into Rows, then Group By the column with Count Rows. That has no size limit and refreshes with one click.
Reading the list
- Product words ("green", "large", "refill") show what people noticed.
- Question words that survive your stop list ("when", "where", "much") mean people are asking. Follow up with find questions and buying intent with REGEXMATCH.
- One odd word with a very high count is often a giveaway keyword or a copy-paste campaign. Check it with detect bot and spam comments.
- Hashtags lose their # in cleaning and are counted as words. The workbook's Hashtags sheet counts them properly, with the number of different people using each.
A word list is a pointer, not a finding. Filter the comments to a word that surprised you and read twenty of them before writing anything down. For research use, exporting comments for research and sentiment analysis covers what to report about the data.
FAQ
What does FLATTEN do?
It takes a grid of cells and returns all values in one column. After SPLIT has put each word of each comment in its own cell, FLATTEN stacks them so they can be counted.
Why does QUERY use Col1 instead of a column letter?
The data comes from a formula, not from a range on the sheet, so it has no letters. QUERY names the columns of such an array Col1, Col2 and so on.
Why are numbers missing from my word list?
SPLIT turns pieces that look like numbers into numbers, and QUERY keeps one type per column, text here. Numbers are dropped. That rarely matters for a word count.
The formula returns an error on a large sheet. What now?
Narrow the range to the rows that hold data, or build the count in two steps: the cleaned text in a helper column first, then SPLIT and FLATTEN on that column.
Can I count emoji in Excel?
Not with one formula in most versions. The simplest route is to upload the workbook to Google Sheets for this step, or count specific emoji with COUNTIF and wildcards.