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.

Table of Contents

**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.

Or watch this one-minute video!

**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.

- 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.
- Now, I enter the starting part of the first IF formula:
**=IF(**. - To nest the
**ISNUMBER**formula in IF, I write the starting part of the second formula as the**test**parameter of IF:**ISNUMBER(**. - 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(**.

- To do this, I will write the starting part of the third formula in the
- 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**.

- In this case, they are
- I will add a
**closing bracket “ ) ”**to complete the SEARCH formula. - The ISNUMBER formula needs one parameter only, so I add another
**closing bracket “ ) ”** - 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.

- Therefore, I must add a
- Finally, I will add the
**if-true**and**if-false**parameters.- In this case, they are
**“Yes”**and**“-“**.

- In this case, they are
- 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:

- 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.

- Now, I enter the starting part of the formula:
**=IF(**. - To nest in the COUNTIF formula in IF, I write the starting part of the second formula as the
**test**parameter of IF:**COUNTIF(**. - 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.

- I added a
- I added a
**closing bracket “ ) ”**to finish the COUNTIF formula. - 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.

- In this case, they are either “
- 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**.

- When using the dash, specify the characters in ascending order. For example,

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

**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.

If you’re looking for Excel templates, please check out some of mine at the **Spreadsheet Point Store****. Feel free to use code “SSP” to get 50% off all templates.**

**Related: **