How To Search For Text In Excel?
The Search for Text in Excel is the most commonly used method, and the “Find and Replace” feature is used for it. However, it is important to note that while Ctrl + F can locate the desired text, it cannot extend beyond that point. If a cell contains certain words, you may require the result to appear in the adjacent cell as either “TRUE” or “FALSE.” Unfortunately, Ctrl + F cannot provide this functionality and will stop at the located text.
For example, we apply the FIND function to Search for Text in Excel.

The FIND formula is entered in cell B2; the complete formula is =FIND(“B”, A2), and the result is returned as 3.

Table of contents
Key Takeaways
- The SEARCH Function in Excel uses special characters, i.e., “?”, “*,” and “~” tilde.
- “?” is used to find a single character.
- “*” is used for match sequences.
- If we want to search the “*” or”? “, we need to insert the “~” before the character.
- The SEARCH function is not sensitive to the case of the text being searched.
- The FIND function returns the #VALUE! error if the find_text value does not exist in within_text, if the start_num contains more characters than within_text, and if the start_num is 0 “zero” or a negative number.
Which Formula Can Tell Us A Cell Contains Specific Text?
FIND function in Excel – The FIND function in Excel is an incredibly useful tool that allows users to locate a specific string of text within a cell or range of cells. This function can be particularly helpful when working with large data sets or when identifying specific information pieces within complex spreadsheets. By specifying the text to search for and the cell or range of cells to search within, users can quickly and easily locate the information they need without having to sift through hundreds or even thousands of cells manually.
The syntax of the FIND function is; =FIND(find_text, within_text, [start_num]).
The following example depicts the string, and we will use the FIND function to Search for Text in Excel.
In the table,
- Columns A and B shows the string and output.

The steps to evaluate the values by calculating Excel Search for Text are as follows:
- Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B2.
- Next, we will enter the FIND Formula in cell B2.
- The value entered to find the text is Tide.
- The value entered as within the text is A2.
- The complete formula is =FIND (“Tide”, A2) in cell B2.
- Next, press Enter key. The result is shown in cell B2.
- Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell B3.
- Next, we will enter the IF Formula in cell B3.
- : The complete formula is =IF(FIND (“Tide”, A2), “Found Text,” “Not Found Text”) in cell B3.
- Press Enter key. The result is obtained as Found Text in cell B3.
Alternatives To FIND Function
Alternative #1 – Excel SEARCH Function
The SEARCH function is an essential Excel tool, enabling users to quickly and efficiently locate data within large spreadsheets. The function has a straightforward syntax, where the user selects a target cell or range and specifies the string of characters or values that needs to be found. The search function then returns the position of the first instance of the target within the chosen cell or range, enabling users to identify relevant data points immediately.
The syntax of the SEARCH Excel Function is

- find_text=This is the mandatory argument. This is the target of searching for a specific character or sub-string.
- within_text=This is the mandatory argument. This is the text string that we need to search.
- start_num=This is the optional argument. The “within_text” string position is where the search should commence. If this argument is not included, it will default to 1, initiating the search at the beginning of the “within_text” string.
The succeeding example depicts the names, and we will calculate the output using the Excel SEARCH function.
In the table,
- Columns A, B and C shows the string, search text and output.

The steps to evaluate the values by the SEARCH Function in Excel are as follows:
- Step 1: Select the cell where we will enter the formula and calculate the result. The selected cell, in this case, is cell C2.
- Step 2: Next, we will enter the SEARCH formula in cell C2.
- Step 3: Enter the value of ‘find_text’ as B2, i.e., the text value we need to find.
- Step 4: Enter the value of ‘within_text’ as A2, i.e., the string in which the text value is searched.
- Step 5: The complete formula is =SEARCH(B2, A2) in cell C2.
- Step 6: Next, press the Enter key. The result is obtained in cell C2.

- Step 7: Press Enter and drag the cursor to cell C3, as shown in the following image.

Alternative #2 – Excel COUNTIF Function
The COUNTIF function in Excel is a powerful tool that allows users to count cells within a selected range that meets specific criteria. This function is valuable for analyzing large data sets and helps researchers make informed decisions based on their findings. It searches a range of cells for specified conditions and returns the total number of cells that meet those criteria. We can use the COUNTIF function to search for various types of data, including text, numbers, or dates, making it extremely versatile.
The syntax of the COUNTIF Excel Function is

- range=This is a range on which the criteria argument is applied. This is a mandatory argument.
- criteria=This is a condition applied to the range of values argument. This is a mandatory argument.
The succeeding image depicts the fruit’s name. Here, we will try to search for the fruit from the table by entering the name in the formula using the COUNTIF function in Excel.
In the table,
- Column A shows the Fruits Name

The steps to count the number of cells according to the criteria are as follows:
- Step 1: First, we will choose the cell where we want the result to appear. Cell C2 would be the cell in this case.

- Step 2: Input the formula required to count the items based on the condition specified by the COUNTIF function in the table. Select the range from the starting cell address to the ending cell address of the table, i.e., “A2:A6”.
- Step 3: Set the criteria we want to count, i.e., ‘Apple’ in the below image.
- Step 4: The complete formula is: =COUNTIF(A2:A6,“Apple”). To proceed, input each value from the previous step and hit Enter. As illustrated in the image below, the outcome will be displayed in cell C2, indicating the Apples count. This will serve as the output of the process.

How To Highlight The Cell With A Particular Text Value
To emphasize a specific word within a cell, conditional formatting in Excel is required.
For example, if we want to highlight the cell containing the word “best,” we can utilize this feature.

- Step 1: Select the cell containing String and click the Conditional Formatting drop-down arrow.
- Step 2: Select New Rules option from the list.

- Step 3: The New Formatting Rule window opens.
- Step 4: Select Format only cells that contain option from the Rule Type list. Set the Specific Text from the drop-down list and =“best” in the text box. Set the color of the cell. Click OK.

- Step 5: The cell that meets the condition is highlighted, as shown in the below image.

Important Things To Note
- Arguments in written form; enclosing them within double quotation marks is important.
- When referencing cells in the arguments, avoiding enclosing them in double quotes is important.
- The “#VALUE!” error occurs if the SEARCH function finds no match.
- The SEARCH function uses wildcards.
Frequently Asked Questions (FAQs)
For search results, select specific criteria such as file type, date range, language, location, and content type. Boolean operators such as AND, OR, and NOT can refine the search by requiring certain keywords or excluding others. It is important to carefully consider which criteria will produce the most relevant results and adjust as necessary. Utilizing these advanced search techniques can greatly increase research efficiency and accuracy.
The practicality of a search depends on various factors, such as the speed and capabilities of the search engine or software used. A large amount of text may take longer to process and return results, requiring more computing resources and potentially causing performance issues.
It is important to consider whether the text may be case-sensitive. This means that certain letters within the text may be capitalized and require an exact match in search terms. Depending on the context of the search, this can have a significant impact on the results. So, paying attention to case sensitivity and adjusting accordingly is essential.
Download Template
This article must help understand the Excel Search for Text formula and examples. You can download the template here to use it instantly.
Recommended Articles
This has been a guide to Search for Text in Excel. Here we learn how to search for text using FIND, SEARCH, etc. with examples & downloadable excel template. You can learn more from the following articles –
Leave a Reply