Getting IF Function to Work for Partial Text Match
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.
Featured Bundle
Black Belt Excel Bundle
Searching for Text with the IF Function
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.
SEARCH Function to Find Text in Cell
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.
Turning Numbers into Decisions
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
IF Cell Contains Text – Multiple Conditions
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", "")
Practice Workbook
Feel free to Download the Workbook HERE.
Featured Course
Master Excel’s Essential Modern Functions
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
