Need to know if an Excel cell contains a specific string or phrase? While Excel lacks a CONTAINS function, you can achieve the same results using formulas like SEARCH and MATCH. Learn how to quickly pinpoint cells containing your target text, improving your workflow. This tutorial provides clear examples of these techniques, showing you how to find specific words within longer data strings. (Includes Excel practice file.)
Knowledge You’ll Gain:
- Use the SEARCH function to locate specific text within cells, regardless of its position.
- Discover how to use the MATCH function to identify cells with your target text, even with partial matches.
- Learn how to combine SEARCH and ISNUMBER to get a clear TRUE/FALSE answer for whether a cell includes specific text.
- Unlock the power of wildcards to find variations of your target text, including different tenses and plural forms.
- Learn how to use COUNTIF to quantify the occurrences of specific text strings within your data.
The Challenge of Finding Specific Text String in Excel
This scenario started with Excel’s Transpose function article. I mentioned “transpose” was an interesting word choice. I wasn’t sure if searchers might use it in a search query. It’s not a common verb but that is what Microsoft uses. It’s not one I think of.
I started by downloading the queries that people typed relating to that page. With the help of Google Search Console, I downloaded a thousand queries. I then imported the file into an Excel workbook. Each query became a cell in Column A. My goal was to see which terms people preferred.
Simulating Excel CONTAINS Function with Formulas
Being the optimist, I thought there was a built-in Excel function. I was wrong. After all, if you filter a text column, there is a menu option for “Contains“.
But if you look at the master Excel list, there is no entry for “CONTAINS”. It goes from CONFIDENCE.T to COVERT. I just wanted to know if the user’s query contained a specific search string.
Although there is no “contains” text function, there are several ways to solve this including the SEARCH function or MATCH function. I didn’t pursue using XMATCH or FILTER because they are only available in Microsoft 365.
The next step was determining which verbs to search, including “transpose,” which started me down this rabbit hole. After eyeballing the Queries column, I settled on these verbs:
- convert
- swap
- flip
- change
- switch
- transpose
- move
- make
- turn
If this were some academic paper where I needed to find all verbs, I would’ve tried Oscar Cronquist’s custom function to find unique words in a cell range. It probably would’ve revealed more verbs I could use for pattern matching.
Use the SEARCH Function to Find Specific Text
If you’ve been on this site before, you’ve seen me use the SEARCH function in the “How to Separate First and Last Names in Excel” article. This basic function locates one text string, my verb, within a second string, that is my Queries cell. However, by default, it doesn’t provide a “yes” or “no” answer as a cell value. And unlike Excel’s FIND function, it’s case insensitive. That’s important if people use capital letters.
Practical Excel SEARCH Function
=SEARCH("convert", A2)
In plain speak, I’m asking Excel to look in reference cell A2 for the word “convert“. It’s not concerned about case sensitivity or letters or spaces on either side of the word. A searcher could’ve used “converted” in the query, which would still trigger as “convert” is a partial match.
As you can see from the results, I either have a numeric value such as “1” or a #VALUE! error. That number of “1” represents the ”c” starting position in the string “convert”. And yes, the formula does count spaces. In this instance, the #VALUE error in Column B is Excel’s way of saying the Queries cell doesn’t contain the text “convert”.
Use ISNUMBER Function to Get a TRUE/FALSE Result
There is nothing wrong with the number returned by using SEARCH. The problem is people may not grasp it as a position locator. By wrapping the previous formula with ISNUMBER, we can convert the numeric values and #VALUE! errors to something logical such as “TRUE” or “FALSE”.
=ISNUMBER(SEARCH("convert",A2))
Finding Partial Matches with the MATCH Function
Another way to determine if an Excel cell includes specific text is to use the MATCH function. Some people like using this function with INDEX instead of the Excel VLOOKUP function. However, we’ll do something simpler because we’re not concerned with the position.
=MATCH("*convert*",A2,0)
In this formula, my cell reference is A2 and I want to search if the cell contains “*convert*“. The last function argument of “0” indicates an exact match type.
Again, the cell containing the text string returns a “1”. If the specific text wasn’t found, Excel returns a #N/A error.
Understanding Wildcard Characters in Excel Formulas
At this point, you might be wondering why asterisks surround my search string. To start, the * is considered a wildcard character by Microsoft Excel. This allows me to find different word variations like “convert”, “converts”, “converting”, and so on in Column A. Moreover, I don’t care where the word resides within the cell.
If I were to remove the asterisks, Excel would not find the word “converting” in the query, “converting column to row in excel“.
While glancing down the Queries column, I didn’t see enough misspellings to worry about using the ? wildcard character. A wildcard would allow for any character to be substituted for the question mark. For example, you could use “*fl?p*” and that would pick up both “flip” or “flop”. The ? allows you to substitute 0 or 1 characters.
Improving Readability: Displaying “Yes” Instead of “1”
In the above screenshot, you can see how my formula works. However, I prefer having a “YES” appear as the cell contents instead of “1”. I’d also prefer to have blank cells if my search term didn’t produce any results. We can do this by wrapping our previous formula with ISNUMBER and the IF function.
=IF(ISNUMBER(MATCH("*convert*",A2,0)), "Yes", "")
In this formula, I’m asking Excel to return a “Yes” for a true condition and a blank when false.
Counting Occurrences of Specific Text with COUNTIF Function
Above I’ve shown various ways to see if a certain text string was contained in my Queries column. However, if you remember my initial scenario, I wanted to know the frequency of each verb. My hypothesis was people didn’t use “transpose” as much as other verbs.
I could use the filter control at the top of the column and count the entries, but I’ll use the COUNTIF function. I like COUNTIF for its simplicity. I need to define the range of cells to search and what to look for.
=COUNTIF(B2:B1001,"Yes")
As with many journeys down the rabbit hole, more questions arise. Next time, I’ll probably use Excel Named Ranges to make the formulas more consistent. That’s one of the benefits of playing around with Microsoft Excel. While there may not be a dedicated Excel function for contains, several ways exist to approach the question.
Ready to put these techniques and functions into action and boost your Excel efficiency? Download the accompanying practice file below to experiment with the examples. By working through the exercises, you’ll gain confidence in using SEARCH, MATCH, and COUNTIF to quickly analyze and manipulate text data within your spreadsheets.
The first worksheet is the queries I downloaded from Google Search Console. The second worksheet shows the formulas. And play around with different verbs to find out if my hypothesis was correct.