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 Course

Black Belt Excel Package

What would your life look like if you became an Excel Black Belt? You’ll get the best deal with this (cost savings) package. It will take you from Excel Newbie to Grand Master.
Learn More
Excel Black Belt course cover

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

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

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.

Excel Download Practice file

Featured Course

Master NEW Excel Functions in Office 365 & Office 2021

Ready to supercharge your Excel skills? This course will help you master the new set of functions introduced to Excel for Office 365. You’ll create professional-grade reports in a fraction of the time it used to take you.
Learn More
Excel new functions course cover

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.