Not long ago, a friend challenged a comment I made. It had to do with word choices and if people use one verb more than others. In this article, I’ll show you how to tell if an Excel cell contains specific text. We’ll test this out using a series of questions people posed to Google.
The 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 Google query. My friend asked if we could use Microsoft Excel to find out which verb was used most often as a search criterion.
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 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”.
Alas, 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 sort of 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 Using 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 is a basic function that locates one text string, my verb, within a second string that is my question cell. However, it doesn’t provide a “yes” or “no” answer as a cell value by default. 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 and it 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 starting position of the “c” 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 is 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 VLOOKUP. 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 for “*swap*”. The last function argument of “0” indicates an exact match type.
Again, we see the cell that contains 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.
Aesthetics – Changing 1 to Yes
In the above screenshot, you can see 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 just 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 add 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.