This guide will discuss how to check if a cell contains partial text in Excel using four simple and easy methods.
Table of Contents
Excel is an excellent tool for inputting, organizing, and analyzing data. Since it contains several built-in functions and tools, it becomes easier to manipulate our data. For example, we can check if any cell in our data set contains the partial text.
Although we would usually use the IF function to look for partial text or perform fuzzy matching, there are times the function we need does not support wildcard characters.
In this case, we would need to combine the IF function with other functions to correctly perform the task we need. Specifically, we will combine the IF function with the COUNTIF function to check if a cell contains partial text in Excel.
Let’s take a sample scenario wherein we would use this formula.
Suppose you have a list containing the employee IDs of the company. And you were tasked to find the employee ID of the marketing department. Instead of manually checking each employee ID, you opted to use the IF function combined with the COUNTIF function.
Thus, you were able to check for cells containing the characters of the marketing department to locate the employee IDs.
Amazing! Before we explain the four simple methods, let’s discuss the syntax of the IF function in Excel.
The Anatomy of the IF Function
The syntax or the way we write the IF function is as follows:
=IF(logical_test, [value_if_true], [value_if_false])
Let’s take apart this formula and understand what each term means:
- = the equal sign is how we begin any function in Excel.
- IF() is our
IFfunction. And this function is used to check whether a condition is met. If TRUE, the function will return one value. If FALSE, the function will return another value. - logical_test is a required argument. And this refers to any value or expression that can be evaluated as TRUE or FALSE.
- value_if_true is an optional argument. So this refers to the specific value which will be returned if the logical test is TRUE. If left blank, the function will simply return TRUE.
- value_if_false is an optional argument. And this refers to a specific value that will be returned if the logical test is FALSE. If left blank, the function will simply return FALSE.
Great! Now we can explain how to check if cell contains partial text in Excel using four easy and simple methods.
How to Check if Cell Contains Partial Text at the Beginning in Excel
Firstly, we can use this method to check if a cell contains partial text in the beginning. So the partial text we are looking for is found at the beginning of the text. For example, we have a dataset containing the list of employee IDs.
And the employee ID starts with an acronym of the department the employee belongs in. So we can use this method to look for the employee ID of the marketing department.
In this case, the formula will combine the IF function and the COUNTIF function. So the COUNTIF function will look for the specific acronym or text we input. Then, the IF function will return a yes or no if the text was found at the beginning of the text.
To use this method, we can follow the steps below.
1. Firstly, we will create a new column to input the results of the formula. Then, we can input “=IF(COUNTIF(B3,”MKT*”), “TRUE”, “FALSE”)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to apply the formula to the other cells.
3. And tada! We have successfully checked if a cell contains partial text in the beginning in Excel.
How to Check if Cell Contains Partial Text at the End in Excel
Secondly, we also have another formula to check if a cell has partial text at the end in Excel. In this scenario, the acronym or partial text we are looking for is found at the end of the text.
Furthermore, we will use the same formula, a combination of the IF function and the COUNTIF function, to perform this task.
To use this method, follow the steps below.
1. Firstly, we will create a new column to input the results. Then, we can type in the formula “=IF(COUNTIF(B3,”*MKT”), “TRUE”, “FALSE”)”. Lastly, we will press the Enter key to return the result.
2. Secondly, we will drag down the Fill Handle tool to copy the formula to the other cells.
3. And tada! We have successfully used a formula to check if a cell has a partial text at the end.
How to Check if Cell Contains Partial Text in Excel at Any Position
Thirdly, we can also check if a cell has partial text found at any position in Excel. So the acronoym or partial text we are looking for can be found at any position within the text.
Similarly, the COUNTIF function finds the acronym or partial text at any position. Then, the IF function will return a TRUE or FALSE value depending on whether the partial text was found.
To use this method, we can follow the step-by-step process below.
1. Firstly, we will create another column to input the result. Next, we can input the formula “=IF(COUNTIF(B3,”*RM*”), “TRUE”, “FALSE”)”. Finally, we will press the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to apply the formula to the other cells.
3. And tada! We have successfully checked if a cell has partial text at any position.
How to Check if Cell Contains Partial Text with Specific Character in Excel
Fourthly, we can also check if a cell has a partial text with a specific character found at the beginning in Excel. In this case, we want to find a cell that contains a partial text followed by a specific character. And we will be using the same formula combining the IF and COUNTIF functions.
To apply this method, we can simply follow the steps mentioned below.
1. Firstly, we will make a new column to input the result. Next, we will input the formula “=IF(COUNTIF(B3, “?LS106AD”), “TRUE”, “FALSE”)”. Finally, we will press the Enter key to return the result.
2. Secondly, we will drag the Fill Handle tool down to apply the formula to other cells.
3. And tada! We have successfully checked for any cells with partial text with a specific character in Excel.
You can make your own copy of the spreadsheet above using the link attached below.
And that’s pretty much it! We have discussed how to check if cell contains partial text in Excel using four easy and simple methods. Now you can choose any of the appropriate methods for your situation and use them in your work to identify cells with the partial text you are looking for.
Are you interested in learning more about what Excel can do? You can now use the IF function and the various other Microsoft Excel formulas available to create great worksheets that work for you. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
