Excel: Finding Text in Cells

Excel is a powerful tool for data analysis, and often, you'll need to locate specific pieces of information within your spreadsheets. This might involve checking if a cell contains a particular word, a sequence of characters, or even a specific pattern. Fortunately, Excel provides several robust functions to accomplish this, allowing you to filter, sort, and manipulate your data with precision. This guide will walk you through the most effective methods, including the use of the SEARCH, FIND, and COUNTIF functions, along with the versatile wildcard characters.

Identifying Text Within a Cell

There are multiple ways to determine if an Excel cell contains a specific word, text string, or even a single character. The primary functions you'll utilise for this are:

• SEARCH
• FIND (often used in conjunction with ISNUMBER)
• COUNTIF (used with specific criteria)

We will explore each of these methods in detail, demonstrating how they can be integrated into more complex formulas, such as the IF function, to create dynamic responses based on whether a cell meets your criteria. For instance, you can trigger an action 'if a cell contains text' or 'if a cell contains a specific word'. This article focuses on searching within a single cell. For searching across lists, columns, or rows, a separate approach is required.

Using the SEARCH Function

The SEARCH function in Excel is designed to return the starting position of a text string within another text string. It reads from left to right and, crucially, is case-insensitive. This means 'Apple' and 'apple' are treated as the same.

The SEARCH function has three arguments:

• Search_text: The text you want to find. This can be a single letter, a string of characters, or a wildcard character (* or ?). You can type this directly into the formula or reference a cell containing the text.
• Within_text: The text string in which you want to perform the search. This can also be entered directly or by referencing a cell.
• [Start_num]: This is an optional argument, defaulting to 1. It specifies the character position from where the search should begin, ignoring any characters before it.

The basic syntax is:

=SEARCH(Search_text, Within_text, [Start_num])

If the Search_text is found within the Within_text, the SEARCH function will return a number representing the starting position of the found text. If the text is not found, it will return a #VALUE! error.

To convert this result into a TRUE/FALSE value (indicating whether the text was found), we can combine SEARCH with the ISNUMBER function. ISNUMBER checks if a value is a number and returns TRUE or FALSE accordingly.

• If SEARCH finds the text, it returns a number, and ISNUMBER will return TRUE.
• If SEARCH does not find the text, it returns a #VALUE! error, and ISNUMBER will return FALSE.

The combined formula looks like this:

=ISNUMBER(SEARCH(Search_text, Within_text, [Start_num]))

Remember, SEARCH is ideal when you don't need to differentiate between uppercase and lowercase letters.

Using the FIND Function

Similar to SEARCH, the FIND function locates a text string within another. However, the key difference is that FIND is case-sensitive. It will distinguish between 'Apple' and 'apple'.

The FIND function also takes three arguments, identical to SEARCH, with the exception that it does not support wildcard characters:

• Find_text: The text to locate.
• Within_text: The text to search within.
• [Start_num]: The starting position for the search (optional, defaults to 1).

The syntax is:

=FIND(Find_text, Within_text, [Start_num])

Like SEARCH, FIND returns the starting position number if the text is found and a #VALUE! error if it's not. To get a TRUE/FALSE result, we again use ISNUMBER:

=ISNUMBER(FIND(Find_text, Within_text, [Start_num]))

Use FIND when the exact casing of the text you're looking for is important.

Key Differences Between SEARCH and FIND

Using the COUNTIF Function

The COUNTIF function is primarily used to count cells within a range that meet a given criterion. While not its primary purpose, it can be adapted to check if a cell contains specific text by using wildcard characters within the criteria.

COUNTIF takes two arguments:

• Range: The range of cells to count from. For checking a single cell, you'll specify that cell as the range.
• Criteria: The condition that defines which cells to count.

The syntax is:

=COUNTIF(Range, Criteria)

To use COUNTIF for finding text, you'll enclose your search text with asterisks (*), which act as wildcards representing any sequence of characters (including none).

• "*YourText*": Finds cells containing 'YourText' anywhere within the cell.
• "YourText*": Finds cells starting with 'YourText'.
• "*YourText": Finds cells ending with 'YourText'.

If the text is found, COUNTIF will return 1 (or more, if the text appears multiple times in different cells within the range). If not found, it returns 0. To get a TRUE/FALSE result, you can compare the output to 1:

=COUNTIF(Range, Criteria) = 1

Note that COUNTIF is generally case-insensitive when used with text criteria.

Practical Examples

Let's illustrate these functions with examples. Suppose we are checking ISIN codes (Column C) for specific text (Column D), and we want the result (Column E) to be TRUE or FALSE.

Example 1: Basic Match (Case Insensitive)

Cell C1: FR0000131104
Cell D1: FR

• SEARCH: =ISNUMBER(SEARCH(D1, C1)) => TRUE
• FIND: =ISNUMBER(FIND(D1, C1)) => TRUE
• COUNTIF: =COUNTIF(C1, "*"&D1&"*")=1 => TRUE

Example 2: Case Difference

Cell C2: FR0000131104
Cell D2: fr

• SEARCH: =ISNUMBER(SEARCH(D2, C2)) => TRUE (Case-insensitive)
• FIND: =ISNUMBER(FIND(D2, C2)) => FALSE (Case-sensitive)
• COUNTIF: =COUNTIF(C2, "*"&D2&"*")=1 => TRUE (Generally case-insensitive)

Example 3: Text Not Present

Cell C3: US1729674242
Cell D3: FR

• SEARCH: =ISNUMBER(SEARCH(D3, C3)) => FALSE
• FIND: =ISNUMBER(FIND(D3, C3)) => FALSE
• COUNTIF: =COUNTIF(C3, "*"&D3&"*")=1 => FALSE

Example 4: Single Character Match

Cell C4: US1729674242
Cell D4: 1

• SEARCH: =ISNUMBER(SEARCH(D4, C4)) => TRUE
• FIND: =ISNUMBER(FIND(D4, C4)) => TRUE
• COUNTIF: =COUNTIF(C4, "*"&D4&"*")=1 => TRUE

Example 5: Using Wildcards

Cell C5: US1729674242
Cell D5: *

• SEARCH: =ISNUMBER(SEARCH(D5, C5)) => TRUE (Searches for any character)
• FIND: =ISNUMBER(FIND(D5, C5)) => FALSE (Treats '*' as a literal character)
• COUNTIF: =COUNTIF(C5, "*"&D5&"*")=1 => TRUE (Searches for any character)

Summing Data Based on Text Content

Beyond simply identifying text, you can use these principles to sum data that meets specific text criteria. The SUMIF and SUMIFS functions are invaluable here, especially when combined with wildcards.

SUMIF/SUMIFS with Wildcards

The SUMIFS function allows you to sum cells based on multiple criteria. When combined with wildcards, it becomes incredibly powerful for conditional summing.

Sum if Text Contains

To sum values where a cell contains specific text, use the asterisk wildcard on both sides.

Scenario: Sum sales figures (Column C) for states whose names contain 'Dakota' (Column B).

=SUMIFS(C3:C9, B3:B9, "*Dakota*")

This formula is case-insensitive.

Sum if Text Starts With

To sum values where a cell's text begins with a specific string, place the asterisk after the text.

Scenario: Sum sales for states starting with 'New' (Column B).

=SUMIFS(C3:C9, B3:B9, "New*")

Sum if Text Ends With

To sum values where a cell's text ends with a specific string, place the asterisk before the text.

Scenario: Sum sales for states ending with 'o' (Column B).

=SUMIFS(C3:C9, B3:B9, "*o")

Using the Question Mark (?) Wildcard

The question mark wildcard (?) represents any single character. This is useful for matching specific positions within a text string.

Scenario: Sum sales for states starting with 'New' and having exactly 7 characters following (Column B).

=SUMIFS(C3:C9, B3:B9, "New??????")

This would include 'New Jersey' and 'New Mexico' but exclude 'New York' because it has only 5 characters after 'New'.

You can also combine wildcards for more complex criteria:

Scenario: Sum sales for states starting with 'N', containing an 'o' before the last character (Column B).

=SUMIFS(C3:C9, B3:B9, "N*o?*")

This excludes 'New Mexico' because the 'o' is not followed by at least one character before the end of the string.

Escaping Wildcard Characters with Tilde (~)

If you need to search for the literal characters * or ?, you must precede them with a tilde (~). This tells Excel to treat them as text, not as wildcards.

Scenario: Sum stock levels for products named exactly 'Product ?' (Column B).

=SUMIFS(C3:C8, B3:B8, "Product ~?")

Using Cell References with Wildcards

Hardcoding values into formulas is generally not recommended. It's more flexible to use cell references for your search criteria.

Scenario: Sum sales for states containing the text in cell E3 (Column B).

=SUMIFS(C3:C9, B3:B9, "*"&E3&"*")

Here, the wildcards are concatenated with the cell reference E3.

You can also combine multiple cell references and wildcards:

Scenario: Sum sales for states starting with text in E3 and containing text from F3 followed by at least one character (Column B).

=SUMIFS(C3:C9, B3:B9, E3&"*"&F3&"?*")

Locking Cell References

When copying formulas, ensure you use absolute references (e.g., $C$3:$C$9) to prevent the range from shifting. This ensures your formula works correctly when applied to different rows or columns.

=SUMIFS($C$3:$C$9, $B$3:$B$9, "*"&E3&"*")

Frequently Asked Questions

Q1: Can I find text that is case-sensitive in Excel?

Yes, use the FIND function combined with ISNUMBER: =ISNUMBER(FIND("Text", A1)). Remember that FIND is case-sensitive, unlike SEARCH.

Q2: How do I count cells containing a specific word?

You can use COUNTIF with wildcards: =COUNTIF(A1:A10, "*Word*"). This will return the number of cells in the range A1:A10 that contain the word 'Word'.

Q3: What's the difference between * and ? in Excel formulas?

The asterisk (*) represents any sequence of zero or more characters, while the question mark (?) represents any single character. Use them within criteria for functions like SUMIF, COUNTIF, SEARCH, etc.

Q4: How do I search for a literal asterisk or question mark?

Precede the wildcard character with a tilde (~). For example, to search for a literal asterisk, use ~* in your criteria.

Q5: Can I use these functions in Google Sheets?

Yes, the functions discussed (SEARCH, FIND, ISNUMBER, COUNTIF, SUMIF, SUMIFS) and the wildcard characters (*, ?) work identically in Google Sheets as they do in Microsoft Excel.

By mastering these functions and techniques, you can significantly enhance your ability to extract and analyse text-based data within Excel, making your spreadsheet tasks more efficient and insightful.