Free Excel workbook
Download the IF Practice File
Download the exact workbook from the tutorial.
No spam. Unsubscribe anytime.
Why Can’t You Use Wildcards in Excel’s IF Function?
In the dataset below, we want to write an Excel formula to find out if a cell contains specific text.
Our formula will search the column A text for the text sequence “AT” and if found display “AT” in column B.
It doesn’t matter where the letters “AT” occur in the column A text, we need to see “AT” in the adjacent cell of column B.
If the column A text does not contain “AT”, the formula in column B should display nothing.
How Do You Check If a Cell Contains Text Using IF and SEARCH?
Let’s begin by selecting cell B5 and entering the following IF formula.
=IF(A5="*AT*", "AT", "")
Notice the formula returns nothing, even though the text in cell A5 contains the letter sequence “AT”.
The reason it fails is that Excel doesn’t work well when using wildcards directly after an equals sign in a formula.
Any function that uses an equals sign in a logical test does not like to use wildcards in this manner.
“But what about the SUMIFS function?”, I hear you saying.
If you examine a SUMIFS function, the wildcards don’t come directly after the equals sign; they instead come after an argument. As an example:
=SUMIFS($C$4:$C$18, $A$4:$A$18, "*AT*")The wildcard usage does not appear directly after an equals sign.
What Is the SEARCH Function Syntax?
The first thing we need to understand is the syntax of the SEARCH function. The syntax is as follows:
SEARCH(find_text, within_text, [start_num])
- find_text – is a required argument that defines the text you are searching for.
- within_text – is a required argument that defines the text in which you want to search for the value defined in the find_text
- Start_num – is an option argument that defines the character number/position in the within_text argument you wish to start searching. If omitted, the default start character position is 1 (the first character on the left of the text.)
To see if the search function works properly on its own, lets perform a simple test with the following formula. We’re testing if the cell A5 contains the text “AT”.
=SEARCH("AT", A5)
We are returned a character position which the letters “AT” were discovered by the SEARCH function. The first SEARCH found the letters “AT” beginning in the 1st character position of the text. The next discovery was in the 5th character position, and the last discovery was in the 4th character position.
The “#VALUE!” responses are the SEARCH function’s way of letting us know that the letters “AT” were not found in the search text.
We can use this new information to determine if the text “AT” exists in the companion text strings. If we see any number as a response, we know “AT” exists in the text string. If we receive an error response, we know the text “AT” does not exist in the text string.
NOTE: The SEARCH function is NOT case-sensitive. A search for the letters “AT” would find “AT”, “At”, “aT”, and “at”.
If you wish to search for text and discriminate between different cases (case-sensitive), use the FIND function. The FIND function works the same as SEARCH, but with the added behavior of case-sensitivity.
How Does ISNUMBER Convert SEARCH Results to TRUE or FALSE?
An interesting function in Excel is the ISNUMBER function. The purpose of the ISNUMBER function is to return “True” if something is a number and “False” if something is not a number. The syntax is as follows:
ISNUMBER(value)
- value – is a required argument that defines the data you are examining. The value can refer to a cell, a formula, or a name that refers to a cell, formula, or value.
Let’s add the ISNUMBER function to the logic of our previous SEARCH function.
=ISNUMBER(SEARCH("AT", A5))
Any cell that contained a numeric response is now reading “True” and any cell that contained an error is now reading “False”.
We are now able to use the ISNUMBER/SEARCH functions as our wildcard statement in the original IF function.
=IF(ISNUMBER(SEARCH("AT", A5)), "AT", "")
This is a great way to perform logical tests in functions that do not allow for wildcards.
Featured Course
Excel Essentials for the Real World
How Do You Check for Multiple Partial Text Matches in One Formula?
Suppose we want to search for two different sets of text (“AT” or “DE”) and return the word “Europe” if either of these text strings are discovered in the searched text.
We will combine the original formula with an OR function to search for multiple text strings.
=IF(OR(ISNUMBER(SEARCH("AT", A5)), ISNUMBER(SEARCH("DE", A5))),
"Europe", "")
How Do You Use COUNTIF for a Partial Text Match in IF?
There is actually a simpler approach to partial text matching that skips the ISNUMBER and SEARCH combination entirely. Because COUNTIF accepts wildcard characters directly in its criteria argument, you can embed it inside IF for a cleaner formula.
Using the same dataset, enter this formula in cell B5:
=IF(COUNTIF(A5,"*AT*"),"AT","")
Here is how it works step by step:
- COUNTIF(A5,”AT“) checks whether the text in cell A5 contains the letter sequence “AT” anywhere. The asterisks (*) are wildcard characters that match any number of characters before and after “AT”. Because we are checking a single cell, COUNTIF returns either 1 (found) or 0 (not found).
- The IF function treats 1 as TRUE and 0 as FALSE. So when COUNTIF finds the match, IF returns “AT”. When it does not, IF returns an empty string.
Why Does COUNTIF Accept Wildcards but IF Does Not?
The difference comes down to how Excel processes each function internally. Functions like COUNTIF, SUMIFS, and AVERAGEIFS are designed to evaluate criteria strings, and Excel’s criteria engine supports the wildcard characters * (any sequence of characters), ? (any single character), and ~ (escape character for literal asterisks or question marks).
The IF function uses a different mechanism. Its logical test evaluates expressions using comparison operators (=, >, <, etc.), and those operators do not interpret wildcards. That is why =IF(A5="*AT*","AT","") fails. Excel reads “AT” as a literal text string, not a wildcard pattern.
How Do You Check for Multiple Partial Matches Using COUNTIF?
To check for “AT” or “DE” in the same cell and return “Europe” when either is found, combine COUNTIF with an OR function:
=IF(OR(COUNTIF(A5,"*AT*"),COUNTIF(A5,"*DE*")),"Europe","")
Each COUNTIF checks for one text pattern. The OR function returns TRUE if either COUNTIF returns 1. This approach scales well. You can add more COUNTIF checks inside the OR for three, four, or more partial text patterns.
Key point: COUNTIF is not case-sensitive. A search for “AT” will match “AT”, “at”, “At”, and “aT”. If you need case-sensitive matching, use the ISNUMBER + FIND method described earlier in this post.
How Do You Use REGEXTEST for Partial Text Matching in Excel 365?
If you are using Microsoft 365 (Excel for Windows, Mac, or Web), you now have access to REGEXTEST, a function that was introduced in late 2024 and became generally available to all Microsoft 365 subscribers in 2025. It is the most powerful option for partial text matching in Excel.
REGEXTEST checks whether any part of a text string matches a regular expression pattern and returns TRUE or FALSE. Unlike SEARCH, it does not return a position number or throw errors, so there is no need to wrap it in ISNUMBER.
It also handles multiple search terms in a single pattern, which eliminates the need for nested OR functions.
Using the same dataset, enter this formula in cell B5:
=IF(REGEXTEST(A5,"AT"),"AT","")
That is all you need. REGEXTEST automatically performs a “contains” type search, so the pattern “AT” matches anywhere inside the text. No wildcards, no nested functions, no error handling.
What Is the REGEXTEST Function Syntax?
REGEXTEST(text, pattern, [case_sensitivity])
- text – The cell or text string you want to search within.
- pattern – The regular expression pattern to match. For a simple substring check, just use the text you are looking for (e.g., “AT”). For advanced patterns, REGEXTEST supports the full PCRE2 regex standard.
- case_sensitivity – Optional. By default, REGEXTEST is case-sensitive (unlike SEARCH and COUNTIF). Set this to 1 to make it case-insensitive.
How Do You Make REGEXTEST Case-Insensitive?
Because REGEXTEST is case-sensitive by default, the formula =REGEXTEST(A5,"AT") matches “AT” but not “at” or “At”. To match regardless of case, add the third argument:
=IF(REGEXTEST(A5,"AT",1),"AT","")The 1 in the third argument tells REGEXTEST to ignore case. This makes it behave like SEARCH and COUNTIF for simple text matching.
How Do You Check for Multiple Text Patterns with REGEXTEST?
This is where REGEXTEST makes the biggest difference over every other method. To check whether a cell contains “AT” or “DE” and return “Europe” when either is found, use the pipe character (|) as a regex OR operator:
=IF(REGEXTEST(A5,"AT|DE"),"Europe","")
The pattern “AT|DE” means “match AT or match DE.” REGEXTEST returns TRUE if either text appears anywhere in the cell. That single pattern replaces what would otherwise require nested OR functions and multiple COUNTIF or ISNUMBER calls.
Compare this to the equivalent formulas using the other methods:
=IF(OR(ISNUMBER(SEARCH("AT",A5)),ISNUMBER(SEARCH("DE",A5))),"Europe","")=IF(OR(COUNTIF(A5,"*AT*"),COUNTIF(A5,"*DE*")),"Europe","")Both work, but the REGEXTEST version is shorter, easier to read, and scales better. Need to add a third country code? Just extend the pattern:
=IF(REGEXTEST(A5,"AT|DE|CH"),"Europe","")No extra functions. No extra nesting. Just add another pipe and the new text.
Can You Use REGEXTEST for Advanced Pattern Matching?
Regular expressions go far beyond simple text checks. For example:
=REGEXTEST(A5,"^AT")This checks if the text starts with “AT” (the ^ character anchors the match to the beginning). And this formula checks if the text ends with “AT”:
=REGEXTEST(A5,"AT$")You can also match digits, specific character ranges, and repeating patterns. If you are new to regular expressions, check out our full tutorial: Regex in Excel: Master New REGEXEXTRACT & REGEXREPLACE.
Important: REGEXTEST is only available in Microsoft 365 (Excel for Windows, Mac, and Web). It is not available in Excel 2021, Excel 2019, or earlier perpetual-license versions.
If you share workbooks with users on older versions, they will see a #NAME? error for any REGEXTEST formula. For maximum compatibility, use the COUNTIF or ISNUMBER + SEARCH methods described above.
Can Copilot in Excel Write Partial Text Match Formulas for You?
If you have a Microsoft 365 Copilot subscription, you do not need to memorize any of these formulas. Copilot can generate them from a plain-English description of what you need.
There are two ways Copilot helps with formula creation in 2026:
How Does Edit with Copilot Generate Formulas?
Edit with Copilot (previously called Agent Mode) can write formulas, insert them into your workbook, and apply them across your data in a single step. It became generally available on Windows and Mac in January 2026 and works on both cloud-stored and locally saved workbooks.
To use it for partial text matching:
- Format your data as a table (Ctrl + T).
- Open the Copilot pane from the Home ribbon.
- Select Edit with Copilot from the Tools menu.
- Type a prompt like: “Add a column that checks if the Company Code contains the text AT and returns AT if found, otherwise leave it blank.”
- Copilot generates the formula, explains how it works, and inserts it directly into your workbook.
Copilot typically produces an IF(ISNUMBER(SEARCH(...))) or IF(COUNTIF(...)) formula depending on the context. You can review the formula it generated and ask follow-up questions like “Make this case-sensitive” or “Also check for DE.”
What About the COPILOT Cell Function?
Microsoft also introduced a COPILOT function that you can type directly into a cell, like any other formula. It sends a natural-language prompt to an AI model and returns the result in the cell.
For example:
=COPILOT("Does this text contain AT?", A5)However, the COPILOT function is designed for semantic and generative tasks like classifying text, summarizing feedback, or generating content. It is not a replacement for deterministic formulas like IF, COUNTIF, or REGEXTEST. The output can vary between recalculations, and it requires an active internet connection.
For partial text matching, use a standard formula. The COPILOT function is better suited for tasks where you need AI judgment, like categorizing free-text entries or analyzing sentiment.
What Copilot Subscription Do You Need?
Edit with Copilot requires one of the following: Microsoft 365 Personal or Family (with AI credits), Microsoft 365 Premium, or a commercial Microsoft 365 Copilot license. The COPILOT cell function requires a Copilot license and is currently rolling out through the Microsoft 365 Insider and Frontier programs.
Note: Microsoft retired the separate “App Skills” entry point in Excel in late February 2026. Formula generation and workbook editing capabilities are now accessed through Edit with Copilot (for direct edits) and Copilot Chat (for conversational help and analysis).
Frequently Asked Questions
Why can’t you use wildcards in Excel’s IF function?
Excel’s IF function uses comparison operators (=, >, <) in its logical test, and those operators do not interpret wildcard characters like * and ?. Functions like COUNTIF and SUMIFS accept wildcards because they use a separate criteria engine that supports pattern matching. To work around this, embed a wildcard-compatible function such as COUNTIF or SEARCH inside the IF function.
What is the difference between SEARCH and FIND for partial text matching?
SEARCH is not case-sensitive and supports wildcard characters (* and ?) in its search pattern. FIND is case-sensitive and does not support wildcards. Both return the position of the found text as a number or a #VALUE! error if not found. Use SEARCH when case does not matter, and FIND when you need to distinguish between uppercase and lowercase letters.
Can you check for multiple partial text matches in one IF formula?
Yes. You can use the OR function to combine multiple checks. For example:
=IF(OR(ISNUMBER(SEARCH("AT",A5)),ISNUMBER(SEARCH("DE",A5))),"Europe","")Alternatively, use COUNTIF:
=IF(OR(COUNTIF(A5,"*AT*"),COUNTIF(A5,"*DE*")),"Europe","")In Excel 365, REGEXTEST is the simplest option:
=IF(REGEXTEST(A5,"AT|DE"),"Europe","")The pipe character (|) acts as an OR operator in regular expressions.
Does REGEXTEST work in Excel 2021 or only Microsoft 365?
REGEXTEST is only available in Microsoft 365 (Excel for Windows, Mac, and Web). It is not available in Excel 2021, Excel 2019, or earlier perpetual-license versions. If you share workbooks with users on older versions, they will see a #NAME? error. For maximum compatibility, use the COUNTIF or ISNUMBER + SEARCH methods instead.
How do you make a partial text match case-sensitive in Excel?
Use the FIND function instead of SEARCH. FIND is case-sensitive, so =IF(ISNUMBER(FIND("AT",A5)),"AT","") matches “AT” but not “at” or “At”. With REGEXTEST in Excel 365, matching is case-sensitive by default. Add 1 as the third argument to make it case-insensitive: =IF(REGEXTEST(A5,"AT",1),"AT","").
Which Excel formula is best for checking if a cell contains specific text?
It depends on your Excel version and needs. IF with COUNTIF is the simplest wildcard approach and works in all Excel versions. IF with ISNUMBER and SEARCH is the traditional method that also works everywhere. REGEXTEST is the most powerful option but requires Microsoft 365. For multiple search terms, REGEXTEST with the pipe operator (e.g., “AT|DE”) is the cleanest formula.
Download the Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Master Excel’s Essential Modern Functions
Leila Gharani
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
