Have you ever struggled to find specific text within an Excel cell? For example, the cell contains multiple words, but you’re looking for one word. In this tutorial, I’ll show several methods including SEARCH and MATCH. Learn these techniques and transform your formulas since the Excel CONTAINS function doesn’t exist at the moment.
The Stinging String Problem
Innocently enough, this scenario started with Excel’s Transpose function article. I mentioned “transpose” was an interesting word choice, and I wasn’t sure if searchers might use it in a search query. My friend asked, “how to search an Excel cell for specific text”.
I started by downloading the questions that people type relating to that page. With the help of Two Octobers Search Explorer, I downloaded over a thousand unique questions. I then imported the question file into an Excel workbook. Each question became a cell in Column A.
There is No Excel Contains Text Function
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”.
I just wanted to know if the question contained the search string. Although there is no “contains function,” there are several ways to solve this using either 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 questions, 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 instead of using my “eyeball” method, I would’ve tried Oscar Cronquist’s custom function to find unique words within a cell range. It probably would’ve revealed more verbs that I could use for pattern matching. And I would’ve included search queries that weren’t questions, which is a much larger dataset.
Find Specific Text With Excel SEARCH Function
If you’ve been on this site before, you’ve seen me use the SEARCH function in the “How to Separate Names in Excel” article. This basic function locates one text string, my verb, within a second string, that is my question 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.
SEARCH Function Example
=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 question, 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 “8” or a #VALUE! error. That number of 8 represents the ” c ” starting position in convert. And yes, the formula does count spaces.
Adding ISNUMBER Function
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))
Find Specific Text Using MATCH Function
Another way to determine if an Excel reference cell contains our search string is to use MATCH. Some people like using this function in conjunction with INDEX instead of using the Excel VLOOKUP function. However, we’ll do something simpler because we’re not concerned with the position.
=MATCH("*swap*",A2,0)
In this formula, my cell reference is A2 and I want to search if the cell contains “*swap*“. 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 returned a #N/A error.
Why Wrap Asterisks Around Search Term?
At this point, you might be wondering why my search string is surrounded by asterisks. To start, the * is considered a wildcard character by Microsoft Excel. This allows me to find different word variations like “swap”, “swaps”, “swapping”, and so on in Column A. In addition, I don’t care where in the cell the word resides.
If I were to remove the asterisks, Excel would never find the word “swap” in the question, “how to swap rows and columns in Excel“. The only entry that would trigger this would be “swap” with no trailing spaces.
While glancing down the list, I didn’t see enough misspellings to worry about using the ? wildcard character. 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.
Prettying Up the Excel Formula
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("*swap*",A2,0)), "Yes", "")
Basically, I’m asking Excel to return a “Yes” for a true condition and a blank when false.
Making it Count
After adjusting my formulas, the spreadsheet resembled something like the one below.
I could use the filter control at the top of the column to get my counts, but I’m going to use the COUNTIF function. I like COUNTIF for its simplicity. I need to define the range of cells to look at and what to look for.
=COUNTIF(B2:B1314,"Yes")
Once I copied my formula across to the other columns, I found the answer to this question. But as with many journeys down the rabbit hole, more questions arise. Next time around, 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, there are a number of ways to approach the question.