Excel Search For Text (Table of Contents)
Searching For Text in Excel
In excel you might have seen situations where you want to extract the text present at a specific position in an entire string using text formulae such as LEFT, RIGHT, MID, etc. You can also use SEARCH and FIND functions in combination to find the text substring from a given string. However, when you are not interested in finding out the substring but you want to find out whether a particular string is present in a given cell or not, not all of these formulae will work. In this article, we will go through some of the formulae and/or functions in Excel which will allow you to check whether or not a particular string is present in a given cell.
How to Search Text in Excel?
Search For Text in Excel is very simple and easy. Let’s understand how to Search Text in Excel with some examples.
Example #1 – Using Find Function
Let’s use the FIND function to find if a specific string is present in a cell or not. Suppose you have a data as shown below.
As we are trying to find whether a specific text is present in a given string or not, we have a function called FIND to deal with it on an initial level. This function returns a position of a substring in a text cell. Therefore, we can say that if the FIND function returns any numeric value, then the substring is present in the text else not.
Step 1: In cell B1, start typing =FIND, you will be able to access the function itself.
Step 2: FIND function needs at least two arguments, the string which you want to search and the cell within which you want to search. Let’s use “Excel” as the first argument for FIND function which specifies find_text from the formula.
4.9 (4,814 ratings)
View Course
Step 3: We want to find whether “Excel” is present in cell A2 under a given worksheet. Therefore, choose A2 as the next argument to FIND function.
We are going to ignore the start_num argument as it is an optional argument.
Step 4: Close the parentheses to complete the formula and press Enter Key.
If you can see, this function just returned the position where the word “Excel” is present in the current cell (i.e. cell A2).
Step 5: Drag the formula to see the position where Excel belongs under cell A3 and A4.
You can see in the screenshot above that the mentioned string is present in two cells (A2 and A3). In cell B3 the word is not present hence the formula is providing the #VALUE! error. This, however, doesn’t always provide a clearer picture. 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 word “Excel” in string occupied in cell A2.
Step 6: In order to get a customized result under column B, use IF function and apply FIND under it. Once you use FIND under IF as a condition, you need to provide two possible outputs. One if the condition is TRUE, the other if the condition is FALSE. Press Enter after editing the formula with IF condition.
After using the above formula output is shown below.
Step 7: Drag the formula from cell B2 until cell B4.
Now, we have used IF and FIND in combinations, the cell with no string still gives #VALUE! error. Let’s try to remove this error with the help of the ISNUMBER function.
ISNUMBER function checks if the output is number or not. If the output is a number, it will give TRUE as a value, if not 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.
Step 8: Use ISNUMBER in the formula we used above in step 6 and step 7. Press Enter Key after editing the formula under cell B2.
Step 9: Drag the formula across cell B2 to B4.
Clearly the #VALUE! error present in previous steps was nullified due to ISNUMBER function.
Example #2 – Using SEARCH Function
On similar lines to that of FIND function, SEARCH function in excel also allows you to search whether the given substring is present within a text or not. You can use it on the same lines we have used FIND function and it’s combination with IF and ISNUMBER.
SEARCH function also searches the specific string inside the given text and returns the position on which the text belongs to.
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. You can follow steps 1 to 9 all in the same sequence from the previous example. The only change will be to replace FIND with the SEARCH function.
Use the following formula in cell B2 of the sheet “Example 2” and press Enter Key to see the output (We have the same data as used in the previous example) =IF(ISNUMBER(SEARCH(“Excel”,A1)),”Text Present”, “Text Not Present”) Once you press Enter Key, you’ll see an output the same as in the previous example.
Drag the formula across the cells B2 to B4 to see the final output.
In cell A2 and A4, the word “Excel” is present hence it has output as “Text Present”. However, in cell A3, the word “Excel” is not present, therefore it has output as “Text Not Present”.
This is from this article. Let’s wrap the things with some things to be remembered.
Things to Remember About Search For Text in Excel
- These functions are used to check whether the given string is present in the text provided. In case you need to extract the substring from any string, you need to use the LEFT, RIGHT, MID functions altogether.
- ISNUMBER function is used in combination so that you are not getting any #VALUE! error if the string is not present in the text provided.
Recommended Articles
This is a guide to Search For Text in Excel. Here we discuss How to Search Text in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
