KajoData / Excel
Does a cell contain text? SEARCH and REGEXTEST in Excel
I separate three questions about text in a cell, explain the formulas and show why a TRUE flag can still answer the wrong business question.

You search a column of comments for complaints. The formula returns TRUE and the task looks finished. Then you notice a comment saying “this is not a complaint”. It also returns TRUE. Excel followed the instruction. The problem started earlier, when the instruction was defined.
I separate three tasks: finding a substring, finding a separate word and checking a complete format. They solve different problems and have different failure cases. I will use small synthetic examples so that you can follow each expected result without guessing. These rules inspect written characters; they do not automatically understand what a customer means.
Define the matching rule first
Suppose A2:A6 contain Batman, batman returns, Batmans, Robin and Not Batman, in that order. My first requirement is to find the substring Batman, ignoring case. That is a precise instruction. It says nothing about the name appearing on its own, or the sentence confirming that somebody is the character.
Four cells match that rule: the first, second, third and fifth. Batmans contains the requested substring. Not Batman contains it too, despite the negation. Only an additional decision about word boundaries would exclude Batmans. Replacing an older function with a newer one will not, by itself, change what the instruction means.
In a working spreadsheet I write the rule above the helper column. For example: “TRUE means that the comment contains the substring Batman, ignoring case.” Someone receiving the workbook a month later should be able to reconstruct that decision. A heading such as IsBatman leaves too much room for interpretation if another person starts using it as a classification of statements.
This also differs from checking a cell’s type. ISTEXT establishes whether a value is text, not whether it contains a phrase. Robin is text and does not contain Batman. Both checks may belong in a report, but they need separate names so that a count of readable comments is not confused with a count of matching comments.
SEARCH returns a position
According to Microsoft’s SEARCH documentation, the function locates the start of one string within another, counting from one. The first argument is the text to find, the second is the text to search, and the optional third argument sets where the search starts. That argument order matters when you move between functions.
=SEARCH("Batman",A2)
If A2 contains Batman, the result is 1 because the name starts at the first character. If it contains Hi Batman, the result is 4: H is first, i is second and the space is third. SEARCH ignores case, so a lower-case spelling is found as well. That is useful when comments have been entered freely by different people.
A missing match produces #VALUE!, rather than zero. This becomes important when the result feeds a filter or a summary. A character position is useful when extracting part of a string, but for the question “does it contain this?” I prefer a logical result. A column with one clearly defined purpose is easier to reuse and explain.
ISNUMBER turns the search into a flag
I wrap SEARCH in ISNUMBER. The IS functions documentation explains that ISNUMBER checks the type of its argument. A numeric position produces TRUE, while a search error produces FALSE. The formula is short enough to read from the inside out: first find the position, then check whether that position is a number.
=ISNUMBER(SEARCH("Batman",A2))
For the five starting cells the results are TRUE, TRUE, TRUE, FALSE and TRUE. This agrees with the substring rule. It includes the longer word and the negative statement on purpose. If those results are unsuitable, the rule needs to change before the spreadsheet becomes an input to a business decision.
In Polish Excel the equivalent formula is =CZY.LICZBA(SZUKAJ.TEKST("Batman";A2)). I use commas in the English examples and semicolons in the Polish ones; the actual argument separator depends on regional settings. The language of the comment does not select the formula language. When sharing a workbook or copying instructions, check function names and separators separately.
If a later calculation needs a numeric indicator, a double unary minus can convert the logical result: =--ISNUMBER(SEARCH("Batman",A2)). I keep the logical version while reviewing the rule because it is easier to inspect. Converting the representation should happen after the meaning of the flag has been agreed, rather than becoming a substitute for that decision.
Put the search term in a separate cell
For recurring work I avoid hard-coding the same phrase in hundreds of rows. I put Batman in H1 and use an absolute reference. Copying the formula down changes the comment row while preserving the address of the search criterion. The workbook becomes easier to adjust without editing every formula individually.
=ISNUMBER(SEARCH($H$1,A2))
That convenience also makes H1 an important input. An empty criterion is not a useful classification instruction. In a simple workbook I add a visible condition that returns FALSE when the criterion is empty. This expresses a deliberate choice: without a supplied search term, the sheet should not label any row as a match.
=IF($H$1="",FALSE,ISNUMBER(SEARCH($H$1,A2)))
For a larger report I might prefer a message asking the user to fill in the criterion. The right choice depends on who operates the workbook. What matters is that an empty parameter has a defined outcome. If the criterion changes during a reporting period, I also preserve which version was used to prepare the final figures.
A search field is part of the analysis, even when it occupies only one cell. I label it, explain whether spaces are significant and avoid placing unrelated notes next to it that could be copied into the field accidentally. Small interface decisions make the formula much easier to use consistently, especially when the original author is no longer available to explain the sheet.
SEARCH also interprets wildcard characters
SEARCH supports wildcards. A question mark stands for one character and an asterisk stands for any sequence of characters. Consequently, Bat?an can find Batman, while Bat* is considerably broader. These characters are instructions inside the search criterion. They are not always treated as the literal punctuation that a user may think they entered.
To find a literal question mark or asterisk, precede it with a tilde. For example, =ISNUMBER(SEARCH("~?",A2)) checks for an actual question mark. Without the tilde, ? is a pattern. Microsoft describes this distinction on the SEARCH page. It matters when somebody pastes a fragment of a comment into H1 and expects a literal search.
I therefore avoid promising that a reference to H1 always searches arbitrary text exactly as typed. First I decide whether the input field supports patterns. If it does, I explain the syntax. If it is intended only for ordinary phrases, I document that scope and review special characters separately. Hidden matching rules tend to become visible at the least convenient point in a reporting process.
REGEXTEST: name, availability and argument order
Microsoft documents the function as REGEXTEST, without a dot. The older version of this article used REGEX.TEST, which was incorrect. REGEXTEST returns TRUE or FALSE directly and does not need ISNUMBER around it. Its argument order also differs from SEARCH: the text comes first, followed by the pattern.
=REGEXTEST(A2,"Batman",1)
The third argument, 1, makes matching case-insensitive. Omitting that argument, or setting it to 0, makes matching case-sensitive. That differs from SEARCH’s normal behaviour. When comparing the two methods, I explicitly use 1 so that both formulas apply the same rule rather than silently differing in their treatment of upper-case and lower-case letters.
I checked the documentation on 22 November 2026. The English page lists Excel for Microsoft 365, Microsoft 365 for Mac and Excel for the web. I would not use the vague description “new Excel” as a promise that every perpetual edition supports the function. Before sharing the workbook, check the recipient’s edition and update environment. SEARCH remains useful when the requirement is simple and compatibility matters.

A separate word needs defined boundaries
For a controlled example, I define a separate word as a name at the beginning or end of the cell, or surrounded by ordinary spaces. The pattern is (^| )Batman( |$). The beginning and end of the string are alternatives to a space. Parentheses group those alternatives and the vertical bar means that either alternative can match.
=REGEXTEST(A2,"(^| )Batman( |$)",1)
This excludes Batmans but still finds Not Batman. It does not match Batman, returns, because the comma is outside our explicitly allowed boundary. That limitation is deliberate and visible. For a different dataset I would extend the permitted separators after looking at the actual input and agreeing which cases should count as separate words.
Common regex word-boundary shortcuts introduce their own rules about letters, digits, underscores and Unicode. I do not want to hide those details behind a promise to “always find a complete word”. Microsoft identifies the regex engine as PCRE2, but the author of the report still needs to define what a word means for this particular task. A narrow space-based example makes the limitation easy to see.
Check a complete code from beginning to end
Another use for a regex is validating the shape of an identifier. My synthetic rule requires two upper-case ASCII letters, a hyphen and three digits. Valid examples include AB-123 and XY-007. The fragment [A-Z]{2}-[0-9]{3} could appear inside a longer description, so I add anchors at the beginning and end.
=REGEXTEST(A2,"^[A-Z]{2}-[0-9]{3}$",0)
For an ordinary single-line field, this rejects AB-12, AB-1234 and ab-123. Inputs containing newline characters require a separate decision; this example assumes one line without them. The quantifier {2} requires two occurrences and {3} requires three. The hyphen, outside the square brackets in this position, represents a literal hyphen.
A valid shape does not prove that the code exists in a product catalogue. ZZ-999 can match the pattern and still be absent from the system. To establish existence, compare it with the reference list. I keep that distinction in the column names too. Separate flags called ValidFormat and InCatalogue communicate much more than one general-purpose OK field.
Separate source errors from a missing match
ISNUMBER around SEARCH helps turn a missing match into FALSE, but it can also reduce other erroneous inputs to FALSE. If A2 contains an error from an earlier calculation, the final flag alone does not explain why there was no match. Before classifying comments, I inspect the source column and decide whether errors need a separate status.
I avoid translating every problem into “does not contain”. In a customer-service report, that shortcut could reduce the apparent number of relevant comments while hiding the missing information. I preserve the comment, its read status and its match flag. This makes it possible to distinguish a successfully checked non-match from a row that could not be assessed reliably.
Empty cells, extra spaces and line breaks deserve the same attention. I first decide whether they are allowed and whether they will be changed before searching. I retain the original value. When someone later asks why a row was classified, I need both the input and the exact criterion. A well-described helper column can save a surprisingly long discussion about an apparently simple answer.
Matching rows and counting occurrences are different measures
A flag answers a question about one cell. If a comment says “Batman and Batman”, it still returns one TRUE value. That is not a count of two occurrences. A summary of flagged rows should therefore be labelled as the number of comments containing the phrase. Calling it the number of times the word was used would change the measure without changing the formula.
Two comments can also refer to the same support case. Before reporting, I establish what one row represents and whether a case identifier is available. A report about cases needs an agreed way to combine several comments into one outcome. Filtering the text column alone does not resolve that issue, and it can easily overstate the number of distinct cases.
Several criteria introduce overlap. One comment may contain both “refund” and “complaint”. Adding the counts of both flags includes that comment twice. This may be appropriate for a breakdown of topics, but unsuitable for a total of comments requiring attention. I explicitly decide whether categories are exclusive or whether one row can belong to several categories.
I avoid removing overlaps mechanically. A comment can describe two separate needs that both deserve attention. I show a few examples to the person using the report and then document the aggregation rule. Otherwise precise string matching can feed a surprisingly imprecise summary. The formula is one step between the source comment and a number on a chart.
Choose a method that the workbook can maintain
For a case-insensitive substring requirement I start with SEARCH and ISNUMBER. The expression is short and widely available. If case matters, Microsoft points to FIND. For alternatives, explicit separators or a structured character pattern, REGEXTEST may express the requirement more directly. In each case I also explain the rule in ordinary language so that the reader can assess it without knowing every symbol.
Before handing over the file, I gather a small reference set: an obvious match, an obvious non-match, different letter case, a longer word, a special character and an empty entry. In a recurring workbook, that list belongs in its operating instructions. A newly encountered comment then becomes a concrete discussion about data or requirements, instead of a vague claim that the formula has stopped working.
I finish by checking who maintains the workbook. Does the recipient have the necessary Excel version? Can they change the criterion? Do they understand the difference between a literal phrase and a pattern? Can the result be reconstructed after a dictionary update? These questions are more useful than declaring one function universally more readable. The best method is one whose output can be explained in this particular process.
Exercise: predict the result before copying down
Enter the five starting examples and add Batman, returns, AB-123 and AB-1234. Create separate columns for a Batman substring, a word separated by spaces and a complete code format. Write down your expected results first, then compare them with the rule character by character. Pay particular attention to the comma, the final extra digit and the negative statement.
The substring rule includes Batmans and the negative statement. The space-based word rule excludes Batmans and the comma example. The code rule accepts AB-123 and rejects the longer string. If an outcome feels wrong, return to the requirement. A formula may be correctly answering a question that nobody intended to ask.
Next, collect a small sample of your own comments with personal information removed. Include different spellings and decide which should match before expanding the pattern. Avoid changing the rule merely to make the number of matches look more convincing. For preparation, see my guide to ISNUMBER and the limits of data validation. Text matching becomes useful when you can explain both what it finds and what it leaves out.
Key takeaways
Is the function called REGEX.TEST?
Microsoft documents REGEXTEST without a dot, including in Polish Excel. The English documentation lists Microsoft 365, Mac and Excel for the web.
Is SEARCH case-sensitive?
No. Use FIND for a case-sensitive search, or REGEXTEST with its third argument set to 0.
Does finding the word complaint identify a customer complaint?
It confirms that a string matches a rule. It does not interpret negation, word forms or the intention of the person who wrote the comment.
AI was used in an automated way while working on this article. If you want to learn how to do it too, check out this project.
