How to check if Excel cells contain partial text

To check if a cell includes a particular piece of partial text, use the Excel formula below:

=IF(COUNTIF(A1,*abc*),Yes,No).

In this example, cell A1 will return a Yes if the string abc appears anywhere in the cell, and a No otherwise.

To illustrate all the strategies, we will use a sample product pricing list as the data set in this post. Let's take a quick look at the data set now

So let's jump right into each strategy one at a time without more debate.

Check to see whether the Beginning of the Text Is Included in excel online

The steps listed below can be used to find a partial match at the start of your texts:

1.To store the output of the formula, choose cell E5.Now, type the formula.=IF(COUNTIF(B5,MTT*),Yes,No)

2.Then press the Enter button.

3.The Fill Handle indicator should now be moved to the last row of the Partial Text column.

4.When you have completed all of the aforementioned procedures, the formula's output will look like the image below:

Check to See whether the End of a Partial Text Contains in excel

1.If you want to explore the partial text that is there at the end of the text, you can follow the instructions below.To store the output of the formula, choose cell E5.Add the formula:=IF(COUNTIF(B5,*NPP),Yes,No)

2.Press the Enter button.

3.The Fill Handle indicator should now be moved to the last row of the Partial Text column.When you have completed all of the aforementioned procedures, the formula's output will look like the image below:

Trustpilot4.8

WPS Office- Free All-in-One Office Suite

• Use Word, Excel, and PPT for FREE, No Ads.

• Edit PDF files with the powerful PDF toolkit.

• Microsoft-like interface. Easy to learn. 100% Compatibility.

• Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Free Download

5,820,008 User

Algirdas Jasaitis

logo

Verify if any position in the partial text contains in excel

1.You can follow these steps to conduct a blind search across the entire dataset, looking for a partial match at any position:To store the output of the formula, choose cell E5.Now type the formula:=IF(COUNTIF(B5,*NQ*),Yes,No)

2.Press the Enter button.

3.The Fill Handle indicator should now be moved to the last row of the Partial Text column.

Using IF ISNUMBER SEARCH Formula 

The Excel formula using IF, ISNUMBER, and SEARCH (or FIND) functions is a versatile method to detect partial text matches within cells. It allows you to check if a specific substring or character exists in a given cell and return custom outputs based on the presence or absence of the partial text. This formula is useful in various scenarios, such as searching for specific keywords, categorizing data, or flagging records that meet specific criteria.

Example: Detecting IDs Containing "A" or "a"

Let's consider a scenario where you have a list of IDs in column A, and you want to check if any of these IDs contain the letter "A" or "a." If the ID contains "A" or "a," you want to label it as "Yes," and if not, label it as "No."

Step-by-Step Guide:

Step 1: Open your Excel spreadsheet containing the list of IDs. In this example, we have a sample dataset with IDs in Column A.

Step 2: In cell B2, enter the following formula:

=IF(ISNUMBER(SEARCH("A", A2)), "Yes", "No")

Explanation of the Formula:

• The SEARCH function is used to find the position of "A" (case-insensitive) within the cell value in cell A2.

• The ISNUMBER function checks if SEARCH returns a valid number (i.e., the letter "A" is found in the cell).

• The IF function returns "Yes" if "A" is found (ISNUMBER returns TRUE), and "No" if "A" is not found (ISNUMBER returns FALSE).

Step 3: Press the Enter button to apply the formula to cell B2.

Step 4: Drag the Fill Handle indicator down to apply the formula to all rows in Column B.

Step 5: The formula will now check each ID in Column A and display "Yes" in Column B if the ID contains "A" or "a," and "No" if it doesn't.

Advanced Techniques with Wildcards

Excel IF OR Statement with Wildcards:

Example 1: Checking for "b" OR "2" in Column A

Suppose you have a dataset in Column A, and you want to check if any cell in that column contains either "b" or "2" (or both). You can use the IF OR statement with wildcards to achieve this.

Formula:

=IF(OR(ISNUMBER(SEARCH("b", A2)), ISNUMBER(SEARCH("2", A2))), "Yes", "")

• The SEARCH function is used to find the position of "b" and "2" (case-insensitive) within the cell value in cell A2.

• The ISNUMBER function checks if SEARCH returns a valid number (i.e., "b" or "2" is found in the cell).

• The OR function checks if either "b" or "2" (or both) are found (ISNUMBER returns TRUE for at least one of them).

• The IF function returns "Yes" if either "b" or "2" (or both) are found, and an empty string ("") if none of them are found.

Excel IF AND Formula with Wildcards:

Example 2: Checking for "b" AND "2" in Column A

Now, let's assume you want to check if a cell in Column A contains both "b" and "2". To achieve this, you can use the IF AND formula with wildcards.

Formula:

=IF(AND(ISNUMBER(SEARCH("b", A2)), ISNUMBER(SEARCH("2", A2))), "Yes", "")

• The SEARCH function is used to find the position of "b" and "2" (case-insensitive) within the cell value in cell A2.

• The ISNUMBER function checks if SEARCH returns a valid number (i.e., "b" and "2" are both found in the cell).

• The AND function checks if both "b" and "2" are found (ISNUMBER returns TRUE for both of them).

• The IF function returns "Yes" if both "b" and "2" are found, and an empty string ("") if either of them is not found.

Example 3: Using Array Constant Approach

As a more compact alternative, you can use the array constant approach to check for both "b" and "2" in a cell.

Formula:

=IF(COUNT(SEARCH({"b","2"}, A2))=2, "Yes", "")

• The formula uses the SEARCH function with an array constant {"b","2"} to find the positions of "b" and "2" in the cell value in cell A2.

• The COUNT function counts how many times both "b" and "2" are found in the cell.

• The IF function returns "Yes" if both "b" and "2" are found (the count is equal to 2), and an empty string ("") if either of them is not found.

These advanced techniques with wildcards allow you to perform more complex searches and conditionally return results based on the presence or absence of multiple substrings within a cell.

Why Use WPS Office?

WPS Office is a comprehensive office suite that offers a range of applications, including Writer (word processing), Presentation (slides), and Spreadsheet (Excel alternative). While both Microsoft Office and WPS Office are powerful productivity suites, there are several compelling reasons to consider using WPS Office:

• Price: WPS Office provides an excellent free version that offers most of the essential features for everyday users.

• Features: From basic document editing to advanced spreadsheet calculations and presentation animations, WPS Office has you covered.

• User-Friendly Interface: WPS Office boasts an intuitive and user-friendly interface, making it easy for both beginners and experienced users to navigate and utilize its functionalities.

FAQs about Excel IF Contains Partial Text:

How can I extract partial text using IF Contains?

To extract partial text using IF Contains in Excel, you can use functions like IF, SEARCH, and ISNUMBER together. For example: =IF(ISNUMBER(SEARCH("partial", A1)), MID(A1, SEARCH("partial", A1), LEN("partial")), "")

Are there any limitations or considerations when using IF Contains in Excel?

Yes, there are some limitations and considerations when using IF Contains in Excel:

1. Case Sensitivity: By default, Excel's SEARCH function is not case-sensitive.

2. Partial Word Matches: The IF Contains method will detect partial text matches, even if the search term is only part of a word within the cell.

3. Wildcards: Using wildcards like asterisks (*) with the SEARCH function can affect the search behavior.

Can the methods mentioned above be used in WPS Office?

Yes, the methods mentioned above can also be used in WPS Office Spreadsheet. WPS Office offers similar functions to Excel, making it compatible and user-friendly. We recommend WPS Office for its cost-effectiveness, compatibility, features, and user-friendly interface.

Summary

This above stated method of explaining you how excel cells contain partial text, can be applied in windows and mac both, so if you have any of these, you can apply this method on both of them.You just need to have a little understanding of how and which way things work and you are good to go with your work. 

With having this basic knowledge or information of how to use it, you can also access and use different other options on excel or spreadsheet. Also, it is very similar to Word or Document. 

So, in a way, if you learn one thing, like Excel, you can automatically learn how to use Word as well because both of them are very similar in so many ways. If you want to know more about WPS Office, you can download WPS Office to access, Word, Excel, PowerPoint for free.