Find questions and buying intent in comments with REGEXMATCH
In a thousand comments, perhaps forty are someone asking whether you ship to their country, what it costs, or when it is back in stock. Those forty are worth more than the other 960 together, and they are easy to miss between the heart emoji. One formula column finds them.
Updated Nov 19, 2026 · 7 min read
The layout
Work on the Comments sheet of an export. The comment text is column E, likes is G, is_reply is H, reply_to_comment_id is I and platform is M, so the new columns start at N:
| Column | Header | Holds |
|---|---|---|
| N | question | TRUE when the comment asks something |
| O | intent | TRUE when it signals a purchase |
| P | topic | The word that matched |
| Q | answered | TRUE when your account replied |
| R | priority | A score to sort by |
Any way of getting the rows in works: the workbook, Copy for Sheets, or a live =IMPORTDATA link.
Questions
Google Sheets, in N2, filled down:
=REGEXMATCH(E2&"", "(?i)\?|^(how|what|when|where|which|who|why|can|could|do|does|is|are|will)\b")
Reading the pattern from left to right: (?i) ignores upper and lower case. \? is a real question mark (a bare ? means something else in a pattern). The | means "or". ^ anchors the list of question words to the start of the comment, and \b ends the word, so "do" matches "Do you ship" and not "dog". The &"" after E2 turns a comment that is only a number into text, which REGEXMATCH requires.
The second half catches the many people who ask without a question mark: "how much is the blue one".
Excel (Microsoft 365) has REGEXTEST, where a third argument of 1 ignores case:
=REGEXTEST(E2, "\?|^(how|what|when|where|which|who|why|can|could|do|does|is|are|will)\b", 1)
Any Excel, question mark only:
=ISNUMBER(SEARCH("~?", E2))
The tilde tells SEARCH to look for a real question mark instead of treating it as a wildcard.
Buying intent
Google Sheets, in O2:
=REGEXMATCH(E2&"", "(?i)\b(price|how much|cost|ship|shipping|deliver|delivery|in stock|restock|sold out|available|buy|order|link|size|sizes|discount|code|where can i)\b")
Excel (Microsoft 365):
=REGEXTEST(E2, "\b(price|how much|cost|ship|shipping|deliver|delivery|in stock|restock|sold out|available|buy|order|link|size|sizes|discount|code|where can i)\b", 1)
Any Excel, with a list in braces. It returns TRUE when any of the words is found:
=SUMPRODUCT(--ISNUMBER(SEARCH({"price","how much","cost","ship","deliver","in stock","restock","sold out","available","buy","order","size","discount"}, E2)))>0
SEARCH finds parts of words, so "ship" also matches "friendship". Read the matches for a minute and remove words that misfire on your audience. The regex versions avoid this with \b.
To see which word matched, in P2 (Google Sheets, and Excel with the regex functions if you drop the &""):
=IFERROR(REGEXEXTRACT(LOWER(E2&""), "price|how much|cost|shipping|ship|delivery|deliver|in stock|restock|sold out|available|buy|order|link|sizes|size|discount|code"), "")
Longer words are listed before the shorter ones they contain ("shipping" before "ship") because the first alternative that fits wins. A pivot table on the topic column then shows what people ask about most: see pivot tables for comment exports.
The same patterns in Spanish and Portuguese
If your audience writes in more than one language, add the words to the same pattern, separated by |. Useful starting lists:
| Language | Question words | Intent words |
|---|---|---|
| Spanish | cómo|como|cuánto|cuanto|cuándo|cuando|dónde|donde|qué|cuál|tienen|hacen | precio|cuesta|vale|envío|envio|envían|comprar|pedido|talla|disponible|stock|descuento |
| Portuguese | como|quanto|quando|onde|qual|tem|vocês|voces|fazem | preço|preco|valor|custa|frete|entrega|entregam|comprar|pedido|tamanho|disponível|estoque|desconto |
People leave accents out when typing on a phone, which is why both spellings are listed. Spanish also opens questions with ¿, so add ¿ next to \?:
=REGEXMATCH(E2&"", "(?i)[?¿]|^(cómo|como|cuánto|cuanto|cuándo|cuando|dónde|donde|qué|que|cuál|cual|tienen|hacen)(\s|$)")
Inside square brackets the question mark needs no backslash. The ending is (\s|$), a space or the end of the comment, instead of \b: in Google Sheets \b only recognises unaccented letters as part of a word, so it fails right after "qué".
Did anyone answer?
A question matters most when it is still open. A top-level comment is answered when at least one reply under it was written by your account. In Q2, with your handle in place of fernhill.market:
=COUNTIFS(I:I, B2, C:C, "fernhill.market")>0
It counts rows whose reply_to_comment_id is this comment's ID and whose author is you. The formula works in Excel and Google Sheets. On Facebook exports, use your Page's value from the username column, which is the name plus four characters, as explained in how to export Facebook Page post comments.
Two cautions. On a free TikTok or Facebook export of a busy post, some reply threads may not have been read; the export page and the Read me sheet say how many. A question can then look open when it was answered. And Excel's COUNTIFS compares long digit-only IDs as numbers, so for exact results on Instagram and TikTok use =SUMPRODUCT((I$2:I$5000=B2)*(C$2:C$5000="fernhill.market"))>0. The reason is in why Excel rounds long comment IDs.
A priority score and the to-do list
In R2:
=IF(H2="yes", 0, IF(O2, 2, 0) + IF(N2, 1, 0) + IF(G2>=5, 1, 0) - IF(Q2, 3, 0))
Replies score nothing. A top-level comment gets 2 for buying intent, 1 for being a question and 1 for having five or more likes, and loses 3 when it is already answered. A 4 is an unanswered buying question that other people liked: answer those first.
The list itself, on a new tab in Google Sheets:
=SORT(FILTER({Comments!C2:C, Comments!E2:E, Comments!P2:P, Comments!G2:G, Comments!R2:R}, Comments!R2:R>=2), 5, FALSE)
In Excel (Microsoft 365):
=SORT(FILTER(CHOOSECOLS(Comments!A2:R5000, 3, 5, 16, 7, 18), Comments!R2:R5000>=2, "none"), 5, -1)
Both show person, comment, topic, likes and score, highest score first. In older Excel, turn on Data, Filter and filter column R to 2 and above.
A worked example
| username | comment | question | intent | topic | answered | priority |
|---|---|---|---|---|---|---|
| priya.plans | Do you ship to Canada? | TRUE | TRUE | ship | FALSE | 3 |
| theo.bikes | how much for the large one | TRUE | TRUE | how much | TRUE | 0 |
| hana.ceramics | 😍😍 | FALSE | FALSE | FALSE | 0 | |
| olu.designs | Need this in a size 9, any left | FALSE | TRUE | size | FALSE | 2 |
| marta.reads | Who took the photo? | TRUE | FALSE | FALSE | 1 |
The row from olu.designs shows why two columns beat one. It has no question mark and no question word at the start, and it is the clearest purchase signal in the table.
Where the patterns go wrong
- Rhetorical questions. "How is this so pretty?" is praise. The intent column separates most of these out; the rest cost you a few seconds each.
- Words with two meanings. "Link" catches "link in bio?" and also "the link between". "Order" catches "in order to". Trim the list to your product.
- Tagged friends. "@marta.reads how much do you want this" is one friend teasing another. Removing @mentions before matching helps: wrap
E2&""inREGEXREPLACE(E2&"", "@[A-Za-z0-9._]+", ""). More cleaning steps are in how to clean comments in a spreadsheet. - Replies. Questions inside replies are usually aimed at another commenter. Keep them out of the list by leaving the
H2="yes"test in the score.
To run this every week across several posts and networks, put the columns into the Data tab of a weekly comment report. For how the mood of the section compares, see sentiment analysis in Google Sheets.
FAQ
Does REGEXMATCH exist in Excel?
Not under that name. Current Microsoft 365 versions of Excel have REGEXTEST, which does the same job. In older versions, use ISNUMBER with SEARCH, and a list of words in braces for several at once.
Why does my REGEXMATCH show an error on some rows?
The cell holds a number, not text, usually a comment that is only digits. Write E2&"" instead of E2 inside the formula.
How do I make the match ignore upper and lower case?
In Google Sheets start the pattern with (?i). In Excel pass 1 as the third argument of REGEXTEST. SEARCH ignores case already.
Can I match whole words only?
Yes. Put \b before and after the word or the group of words in a regex pattern. SEARCH cannot do this; it finds parts of words.