Getting IF Function to Work for Partial Text Match
In the dataset below, we want to write a formula in column B that will search the text in column A. 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 letters “AT” do not occur in the column A text, the formula in column B should display nothing.
Featured Course
Black Belt Excel Package
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 the Rescue
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.
=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
Let’s see another scenario
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 NEW Excel Functions in Office 365 & Office 2021
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
