The only change will be to replace FIND with the SEARCH function. You can follow steps 1 to 9, all in the same sequence from the previous example. I will directly show you the final formula for finding if a string is present or not in Excel using the combination of SEARCH, IF and ISNUMBER function. SEARCH function also searches the specific string inside the given text and returns the text’s position. You can use it on the same lines we have used the FIND function and its combination with IF and ISNUMBER. On similar lines to that of the FIND function, the SEARCH function in excel also allows you to search whether the given substring is present within a text or not. Step 9: Drag the formula across cell B2 to B4.Ĭlearly the #VALUE! error present in previous steps was nullified due to the ISNUMBER function. Press Enter Key after editing the formula under cell B2. Step 8: Use ISNUMBER in the formula we used above in step 6 and step 7. If the output is a number, it will give TRUE as a value if not a number, then it will give FALSE as a value if we use this function in combination with IF and FIND, IF function will give the output based on the values (either TRUE or FALSE) provided by ISNUMBER function. ISNUMBER function checks if the output is number or not. Let’s try to remove this error with the help of the ISNUMBER function. Now, we have used IF and FIND in combinations the cell with no string still gives #VALUE! error. Step 7: Drag the formula from cell B2 until cell B4. Press Enter after editing the formula with IF condition.Īfter using the above formula, the output is shown below. One of the condition is TRUE, the other if the condition is FALSE.
Once you use FIND under IF as a condition, you need to provide two possible outputs. Step 6: In order to get a customized result under column B, use the IF function and apply FIND under it. I mean, someone might not be good enough in excel to understand the fact that 1 appearing in cell B2 is nothing but the position of the word “Excel” in string occupied in cell A2. This, however, doesn’t always provide a clearer picture. In cell B3 the word is not present hence the formula is providing the #VALUE! error. You can see in the screenshot above that the mentioned string is present in two cells (A2 and A3). Step 5: Drag the formula to see the position where Excel belongs under cell A3 and A4.
If you can see, this function just returned the position where the word “Excel” is present in the current cell (i.e. Step 4: Close the parentheses to complete the formula and press Enter Key. We are going to ignore the start_num argument as it is an optional argument. Therefore, choose A2 as the next argument to FIND function. Step 3: We want to find whether “Excel” is present in cell A2 under a given worksheet.
0 kommentar(er)
