How to Search For Text in Excel?

When working with Excel, we see so many peculiar situations. One of those situations is searching for the particular text in the cell. The first thing that comes to mind when we say we want to search for a specific text in the worksheet is the “Find and Replace” method in Excel, which is the most popular one. But Ctrl + F can find the text you are looking for but cannot go beyond that. So, for example, if the cell contains certain words, you may want the result in the next cell as “TRUE” or “FALSE.” So, Ctrl + F stops there.

You are free to use this image on you website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be HyperlinkedFor eg:Source: Search For Text in Excel (wallstreetmojo.com)

Here, we will take you through the formulas to search for the particular text in the cell value and arrive at the result.

Which Formula Can Tell Us A Cell Contains Specific Text?

It is a question we have seen many times in Excel forums. The first formula that came to mind was the “FIND” function.

So, in the IF condition, we have supplied the result as “Text Found” if the value “best” is found. Otherwise, we have provided the result as “Not Found.”

  • For example, look at the below data. In the above data, we have three sentences in three different rows. Now in each cell, we need to search for the text “Best.” So, apply the FIND function. The “find_text” argument mentions the text we need to find. For the “within_text,” select the full sentence, i.e., cell reference. The last parameter is not required to close the bracket and press the “Enter” key. So, in two sentences, we have the word “best.” We can see the error value of #VALUE! in cell B2, which shows that cell A2 does not have the text value “best.” Instead of numbers, we can also enter the result in our own words. For this, we need to use the IF condition.

So, in two sentences, we have the word “best.” We can see the error value of #VALUE! in cell B2, which shows that cell A2 does not have the text value “best.”

But, here we have a problem, even though we have supplied the result as “Not Found,” if the text is still not found, we are getting the error value as #VALUE!.

So, nobody wants to have an error value in their Excel sheet. Therefore, we must enclose the formula with the ISNUMERIC function to overcome this error value.

The ISNUMERIC function evaluates whether the FIND function returns the number or not. If the FIND function returns the number, it will supply TRUE to the IF condition or else FALSE condition. Based on the result provided by the ISNUMERIC function, the IF condition will return the result accordingly.

We can also use the IFERROR function in excelIFERROR Function In ExcelThe IFERROR function in Excel checks a formula (or a cell) for errors and returns a specified value in place of the error.read more to deal with error values instead of ISNUMERIC. For example, the below formula will also return “Not Found” if the FIND function returns the error value.

Alternatives to FIND Function

Alternative #1 – Excel Search Function

Instead of the FIND function, we can also use the SEARCH function in excelSEARCH Function In ExcelSearch function gives the position of a substring in a given string when we give a parameter of the position to search from. As a result, this formula requires three arguments. The first is the substring, the second is the string itself, and the last is the position to start the search.read more to search the particular text in the string. The syntax of the SEARCH function is the same as the FIND function.

Supply the “find_text” as “Best.”

The “within_text” is our cell reference.

Even the SEARCH function returns an error value as #VALUE! If the finding text “best” is not found. As we have seen above, we need to enclose the formula with ISNUMERIC or IFERROR functions.

Alternative #2 – Excel Countif Function

Another way to search for a particular text is using the COUNTIF functionUsing The COUNTIF FunctionThe COUNTIF function in Excel counts the number of cells within a range based on pre-defined criteria. It is used to count cells that include dates, numbers, or text. For example, COUNTIF(A1:A10,”Trump”) will count the number of cells within the range A1:A10 that contain the text “Trump” read more. This function works without any error.

In the range, the argument selects the cell reference.

In the criteria column, we need to use a wildcard in excelWildcard In ExcelIn Excel, wildcards are the three special characters asterisk, question mark, and tilde. Asterisk denotes multiple characters, a question mark denotes a single character, and a tilde denotes the identification of a wild card character.read more because we are just finding the part of the string value, so enclose the word “best” with an asterisk (*) wildcard.

This formula will return the word “best” count in the selected cell value. Since we have only one “best” value, we will get only 1 as the count.

We can apply only the IF condition to get the result without error.

Highlight the Cell which has a Particular Text Value

If you are not a fan of formulas, you can highlight the cell with a particular word. For example, to highlight the cell with the word “best,” we need to use conditional formatting in excelConditional Formatting In ExcelConditional formatting is a technique in Excel that allows us to format cells in a worksheet based on certain conditions. It can be found in the styles section of the Home tab.read more.

First, select the data cells and click “Conditional Formatting” > “New Rule.”

Under “New Rule,” select the “Format only cells that contain” option.

From the first dropdown, select “Specific Text.”

The formula section enters the text we search for in double quotes with the equal sign. =’best.’

Then, click on “FORMAT” and choose the formatting style.

Click on “OK.” It will highlight all the cells which have the word “best.”

Using various techniques, we can search the particular text in Excel.

This article is a guide to Search For Text in Excel. Here, we discuss the top three methods to search the cell value for a specific text and arrive at the result with practical examples and a downloadable Excel template. You may learn more about Excel from the following articles: –

  • Find Links in ExcelUsing Find and Select in ExcelSearch Box in Excel