An Easy 5-Minute Excel “IF Contains Partial Text” Guide

Do you need to learn how to use the Excel IF contains partial text function? No problem, keep reading to learn how!

You can perform Excel IF contains partial text searches by combining the IF function with other functions. The most appropriate choices are using the SEARCH function and COUNTIF.

Reasons to Use the Excel IF Contains Partial Text Function

There are several reasons why I use the Excel IF contains partial text function:

• Validation: I often need to ensure that the data I enter in a cell follows a specific format or has certain keywords. I can effectively validate data and prevent errors by checking for partial text.
• Sorting: It’s crucial to filter and sort data efficiently when dealing with large datasets. For example, I frequently filter a list of products to find all items containing the word “Apple” or sort a list of names based on their last names.
• Cleanup: Dealing with messy data is a common task. I often need to extract specific information to clean it up. Checking for partial text helps me remove relevant details from a cell.
• Identifying Keywords: If I’m looking to pinpoint specific keywords or terms within a text, partial text is excellent for flagging or extracting these keywords.

Related: Your Guide to IF CONTAINS Google Sheets Functions

Ways To Check IF Cell Contains Partial Text in Excel

There are multiple ways to use the Excel IF contains partial text formula. Let me demonstrate some ways to check if a cell contains partial text.

To help you understand the formula better, I have also prepared an example sheet. It would be helpful for you to download a copy so you can follow this guide as we go. The file is read-only, so make sure you make a copy first to make edits.

Video Guide: Check for Partial Text in Excel

Here’s my video that shows how to check whether a cell contains partial text.

The key here is the IF function with an ISNUMBER function inside. My video is only about a minute long and shows how to write the formula to check for partial text is  cell.

How To Check Excel IF Cell Contains Specific Text Using SEARCH

The SEARCH formula nested in IF and ISNUMBER is a great way to search a cell for a given value and get the character’s position. Before I show you an example using the SEARCH formula, let me show you how the formulas work.

Here is the syntax for the IF formula:

=IF(test, if-true, if-false)

The formula requires at least two parameters to work. These are:

• test: This required parameter defines the condition that you want to test.
• if-true: This required parameter specifies the value you want to return if the test result is true.
• if-false: This optional parameter sets the value you want to return if the test result is false.

Here is the syntax for ISNUMBER:

=ISNUMBER(val)

The formula has only one parameter called val, which defines the value or expression you want to test. It is usually added as the cell address. The function will return a logical value, either FALSE or TRUE.

The syntax for SEARCH is:

=SEARCH(find, within, start)

The formula requires at least two parameters to work. These are:

• find: This is a required parameter that defines the text you want to find.
• within: This required parameter defines the text where you want to search for the value in the find argument.
• start: This optional parameter is the character from within the argument that you want to start searching.

Now that we know how the formulas work, let’s look at how the Excel IF contains partial text formula works.

In the example above, I’ve used the formula:

=IF(ISNUMBER(SEARCH(“PTT”,B3),“Yes”,”-”) 

Below is a step-by-step guide and a breakdown of how this formula works using the abovementioned syntax.

1. I opened the spreadsheet with the data I wanted to search using the partial text. Here, I click on an empty cell where I want to enter the nested formula to select it.
2. Now, I enter the starting part of the first IF formula: =IF(.
3. To nest the ISNUMBER formula in IF, I write the starting part of the second formula as the test parameter of IF: ISNUMBER(.
4. Now, I’m going to nest the SEARCH formula into ISNUMBER.
   • To do this, I will write the starting part of the third formula in the val parameter for ISNUMBER.
   • Therefore, I write it as: SEARCH(.
5. For the third formula’s parameters, I will write the find and within parameters separated by a comma ( , ).
   • In this case, they are “PTT” and B3.
6. I will add a closing bracket “ ) ” to complete the SEARCH formula.
7. The ISNUMBER formula needs one parameter only, so I add another closing bracket “ ) ”
8. For the IF formula, I need to add the second and third parameters.
   • Therefore, I must add a comma ( , ) as the SEARCH and ISNUMBER functions were the first parameters.
9. Finally, I will add the if-true and if-false parameters.
   • In this case, they are “Yes” and “-“.
10. To finish the formula, I will add a closing bracket “ ) ” and then press “Enter” to execute the formulas.

Related: 3 Easy Ways for Google Sheets to Count Cells with Text

How To Check Excel IF Cell Contains Partial Text Using COUNTIF

I like to check if a cell contains partial text by using COUNTIF nested into the IF function. Before I show you an example of the COUNTIF cell that contains partial text formula, let’s see how the formula works.

I have already discussed the syntax for IF above. Therefore, below is the Excel COUNTIF formula with partial text:

=COUNTIF(range, criteria)

The formula requires both parameters to work. These are:

• range: This parameter defines the cell group that you want to count. The parameter can contain numbers, named ranges, arrays, or cell references containing numbers.
• criteria: This parameter defines a number, cell reference, or text string that decides the cells to be counted.

Now that we know how the formulas work, let me demonstrate the nested formulas to count if a cell contains part of the text. For this example, I have used a data set that includes the ProductID for various products. Here, I want to check if the ProductID contains “PTT.” To do this, I use the COUNTIF with partial text, as seen below:

1. I opened the spreadsheet with the data I had to search for with the partial text.
   • Here, I click on an empty cell where I want to enter the nested formula to select it.
2. Now, I enter the starting part of the formula: =IF(.
3. To nest in the COUNTIF formula in IF, I write the starting part of the second formula as the test parameter of IF: COUNTIF(.
4. Now, I will enter the range parameter: B3.
   • I added a comma ( , ) and the criteria parameter of “PTT*.”
   • You may have noticed that I added the asterisk ( * ) after “PTT.” This is known as a wildcard that denotes the search can involve other characters. If you want to learn more about that, see the “Using Wildcards” section below.
5. I added a closing bracket “ ) ” to finish the COUNTIF formula.
6. Now, I will add the if-true and if-false parameters separated by a comma ( , ).
   • In this case, they are either “YES” or “NO.”
   • Add the quotation marks ( “ ) for strings to use the formula correctly.
7. Finally, I will add another closing bracket, “ ) ”, to finalize the formula and then press “Enter” to execute it.

Related: 11 Best Excel Courses Online in 2024

Using Wildcards

You have to use a wildcard character to trigger the searching part of the formula. They are helpful because they can locate multiple items with similar but not identical data. Wildcards can also be used when getting data based on a specified pattern match.

Some typical wildcard characters include:

• Asterisk ( * ): This character matches any number of characters in the place. For example, wh* will find white, why, and what. The placement of the character matters, which means:
  • *text* will find the text anywhere in the cell.
  • *text will find text at the end of the cell.
  • text* will find text at the start of the cell.

• Question mark ( ? ): This character will match a single alphabet in the specified position. For example, b?t will find bat, bet, bit, and so on.
• Bracket ( [] ): This character will match any characters within the brackets. For example, b[ae]t will only show bat and bet, not others.
• Exclamation mark ( ! ): This character will exclude any characters inside the brackets. For example, b[!ae]t will show bit, bot, but, and others, but not bat and bet.
• Dash ( – ): This character will match any range of characters.
  • When using the dash, specify the characters in ascending order. For example, b[a-d]t will show bat, bbt, bct and bdt.

Related: Google Sheets vs Excel – Which Is Better In 2024?

[adthrive-in-post-video-player video-id=”zuNqR8CK” upload-date=”2023-12-04T15:14:19.000Z” name=”IF Contains Partial Text: Logical Tests” description=”How do you use the IF function for logical tests based on partial text? I covered it in this video. We specifically discuss how, when IF contains partial text, to return a logical result.” player-type=”default” override-embed=”default”]

Wrapping Up

To summarize, you need to combine the IF function with others, such as using SEARCH. When applying the Excel IF contains partial text search function, you can apply all the methods above. Once you have grasped the knowledge and practice of using the formula, you can apply it to other Excel spreadsheets to improve the quality of your data.

If you want to learn more about partial text, check out my helpful tutorials on the Spreadsheet Point YouTube channel.

Related:

• How To Apply the Excel Accounting Number Format [Easy Guide + Video]
• 3 Ways To Check If a Value Exists in a Range in Excel
• How To Remove Dashes in Excel (3 Easy Ways)