Determining whether a cell contains specified content in Excel is already a commonplace topic. I believe everyone has encountered many similar problems at work. Today I’m going to tell you three common ways to solve this kind of problem, and I’m sure they will work!
Determining whether a cell contains specific content is a type of problem that is often encountered in daily work. It is common in tables containing remark information. For example, in the attendance summary table below, it is necessary to determine whether the employee works overtime based on the content in the remarks, which falls into this type of problem.
#How to deal with this kind of problem? There are three commonly used formula methods. Let’s introduce them one by one below.
Method 1: IF COUNTIF
Formula: =IF(COUNTIF(D2,"*overtime*")=0, "","Yes")
Formula analysis:
COUNTIF(D2,"*overtime *") is the core part of this formula. This function mainly implements the conditional counting function. The basic format is COUNTIF (conditional area, condition).
In this example, the condition area is a cell "D2", and the condition is the value obtained by adding the wildcard character * on both sides of the content to be judged. The effect is to target cells that meet the condition. The cells are counted. If the content to be judged is included, the result is 1. If it is not included, the result is 0.
After having this result, use the IF function to get the final result, the formula =IF(COUNTIF(D2,"*overtime*")=0,"", "Yes") is easy to understand. If cell D2 does not contain "overtime", the result of COUNTIF is 0, and IF returns the corresponding null value, otherwise it returns "yes".
Let’s look at the second method.
Method 2: IF ISERR FIND
Formula: =IF(ISERR(FIND("Overtime",D2)), "","Yes")
Formula analysis:
The core part of this formula is FIND("Overtime ", D2), the basic format of the FIND function is FIND (what you are looking for, where to find it, and which character to start looking for). If the third parameter is not written, it means starting from the first character. The meaning of this formula is to find the word "overtime" in cell D2. If it can be found, FIND will return the location of the content you are looking for in the cell. If it cannot find it, it will return an error value.
The effect of the FIND function can be seen from the above figure. Next, you need to determine whether the result is an error value. If it is an error value, it means that there is no content you are looking for, so you need to use the ISERR function. The ISERR function is very simple, it just determines whether a value is an error value other than #N/A. In this example, the error value is #VALUE!, so there is no problem using this function.
Finally, add the IF function to form a complete formula =IF(ISERR(FIND("Overtime",D2)),"","Yes") .
Compared with the first method, this formula is slightly more difficult, but it is a good thing to practice the two functions more.
Finally let’s look at the third method.
Method 3: IF SUBSTITUTE
Formula: =IF(SUBSTITUTE(D2,"Overtime","")=D2 ,"","Yes")
Formula analysis:
This formula uses a relatively long Function SUBSTITUTE. The function of this function is to replace characters. The format is SUBSTITUTE (where to replace, what to replace, what to replace, and what number to replace). If the last parameter is not written, it means all replacements.
FormulaSUBSTITUTE(D2,"overtime","")means to replace the word "overtime" in cell D2 with nothing. The result after replacement is as shown in the figure Show.
The next step is more interesting. Compare the replaced content with the original content, that is, SUBSTITUTE(D2,"overtime","")=D2. If overtime is included, the replaced content is definitely not equal to The original cell is replaced. On the contrary, if the replaced content is the same as the original content, it means that it does not contain the content you are looking for. In the end, IF is used to output the result.
Compared with the first two methods, the formula =IF(SUBSTITUTE(D2,"overtime","")=D2,"","Yes") is very clever and also Let us re-understand the SUBSTITUTE function from another aspect.
Summary: From the perspective of solving the problem, the first method is sufficient, easy to understand, and relatively simple. But from a learning perspective, when we encounter a problem, we might as well try a few more ideas. On the one hand, we can expand our thinking, and on the other hand, we can become more familiar with some functions. With different solutions, we can also You can experience the fun of studying formula functions. Many so-called masters actually practiced this way.
Related learning recommendations: excel tutorial
The above is the detailed content of Practical Excel skills sharing: Determine whether a cell contains specific content. For more information, please follow other related articles on the PHP Chinese website!
