Add a CONTAINS("find_text","within_text") Function
Excel's own documentation recommends pairing the ISNUMBER and SEARCH functions to accomplish this, but I still have team members asking on a regular basis for a function that returns whether a string is found within a given cell. An out of the box CONTAINS() function would go a long way for usability.
Kenneth Barber commented
One of the problems with FIND and SEARCH is that if you are using them in the condition of IF, you need to use ISNUMBER or ISERROR to detect if the substring was found. I agree that while the COUNTIF method works, it is a bit unintuitive and you need to worry about escaping any wildcards that may appear in the searched string.
What we really need is something like InStr from VBA. It also returns the position of a substring, but when the substring cannot be found, it returns 0 instead of an error. This would allow us to also use it like CONTAINS and write IF(INSTR(),"found","not found"), which is ultimately what we want. We can't go IF(FIND(),,) because the string is not found, the IF function will return the error returned by FIND.
The problem could also be solved by adding an additional argument to FIND and SEARCH that represents the value that should be returned if the substring cannot be found. The FIND and SEARCH functions in DAX (Power Pivot) are already like this. Then we can use FIND(,,,0) to simulate InStr.
Vincent Empain commented
The FIND (case sensitive) and SEARCH (case insensitive) exist and are both doing just that.
Sure, but I’d argue this functionality is not something specific to the needs of only hundreds, but likely hundreds of thousands. Look at the Stack Overflow post I linked to before. Nearly 700k views on that post alone. And that’s only one site. Doesn’t include all the views from Microsoft’s official documentation on the matter or all the other sites with similar tips. A quick check shows that queries related to this functionality are searched over 100k times PER MONTH on Google. So no, this isn’t a narrow, unique request. This is a basic ask that users have been asking for years.
Excel would be far less pleasant for hundreds of millions to use if it had thousands of built-in functions to address the needs of hundreds of users each.
I realize COUNTIF(ref_to_string,"*"&substring&"*") may not be obvious, but it does address the need without all that much mysterious syntactic baggage.
I hate to put it this way, but with only 18 votes in favor, don't get your hope up on seeing this in Excel before Excel gets regular expression functions.
@Roy, 'routine, and direct solutions to the general problems faced fairly often'
What if there are many (dozens if not hundreds) of variations on certain general problems? Text matching and manipulation are standard computing problems, but not financial arithmetic problems, so spreadsheets seldom address them.
Why not the most general solutions possible?
Anyway, blame Lotus 1-2-3 for this as well as MSFT's decision to copy 1-2-3's @-function semantics so completely. Had SEARCH or FIND returned a positive integer for substring matches or 0 for no match rather than #VALUE!, this particular issue would never have arisen. However, backwards compatibility requires FIND and SEARCH continue to return #VALUE! for no match.
If Excel's eventual REGEXMATCH works the same as Google Sheets's function of the same name, it'll take care of this since if substring contained no metacharacters, REGEXMATCH(string,substring) would return TRUE when substring appeared within string and FALSE otherwise.
Roy hits the nail on the head. The request for this function is not to suggest the workarounds don't exist. The request is to make an extremely common use of excel easier to implement without having to end up on a StackOverflow post that's been viewed 639k times: https://stackoverflow.com/questions/18617175/check-whether-a-cell-contains-a-substring
True, and my point said that was the reason for having to use wildcards with COUNTIF(). I'm not precisely arguing clever usages don't exist to solve each and every roadblock, but who needs that? One needs routine, and direct solutions to the general problems faced fairly often. This is one reason many perfectly clever folk decide they have other things they must do than learn some of the arcane usages of some functions. You know, "it's searching for a 2 and will never find one so it picks the last thing not a 2 and you have contents of the last cell used in a row" kind or arcane.
IFF (not a typo, if and only if) regular expressions became a part of non-VBA Excel, fully implemented, I wouldn't care much about this. But they aren't now and seriously, won't be in my useful lifetime. So I, and clearly, at least 17 others, find a straightforward function, one that ought've been around for a very long time if COUNTIF() is the wonderful workaround for the need, to be a desirable thing.
Clearly we do not make the impact the Python folk do (and should). So we shall not have it. Not gonna stop us from wishing and trying to be constructive about it.
Pertaining to that, I do love knowing the basics of a workaround ESPECIALLY one I never thought to use before. COUNTIF() could be the workaround I might use for the rest of my spreadsheet life and I thank you folks, you constructive folks who offer it to a fellow who never considered it before.
But given I've experienced quite a few times needing this ability, it just cannot substitute for a straightforward function that does this thing and would in almost every instance I've needed it rather than just 80-90%.
And I love to be clever with Excel, while being practical about it. I used to say computers could not win the world until they were essentially toasters: you're concerned greatly sometimes about the item dropped into the toaster, but other than handling your choices, you don't care the least about the toaster itself. It is simply to work and never really involve you in that process. (Not Apple's we will dictate to you for megadollars approach, but not DOS and ?Windows' concerns about interrupts and COM port addresses you can use (a couple out of thousands possible, what was up with that?) either. Now they are toasters.
Rightly or wrongly (wrongly, but it's their choice, the USSR lost) many people regard Excel that way. They don't wanna know a thing. How they expect to use it, I wish I didn't know. Remember when the guy who could do anything was a guru and valued? Now he's a must for any averagely functioning office. Remember when the only thing holding him back was geekhood? Now it''s that he does everyone else's work and gets no credit, rather gets dinged for his own suffering (even if only in quantity not its quality). I'm tired of being that guy and want more straightforward, but meaningful functions than just this one. Something even someone too good to learn much about Excel looks stupid for not being able to use. Those channel changers don't need to have me adjust their vertical roll (oh, and can you change it to channel 43 while you're at it?) if TV screens no longer can roll.
Selfish? D*mn skippy, and I admit it. I want it for my own use. I want it and other things like it to move the folks above along on their own, surprisingly to them, two feet. I don't sit here wishing for workarounds, especially odd ones, arcane ones, to be the only solutions.
And I might point out, when is the last time the average person used a statistical function in Excel? Average person. There's a constituency and a need, but not for the average person. Sine, Cosine? Complex numbers? Heck, I don't even try to work them into the quadratic sheet I give people, I just craft them in it when needed as strings and tear them apart to reuse the result if I have to. Any one using the sheet has too as well, eh? It's harder to mix complex and non-complex numbers in something learners are going to use.
But no one roars that those things should therefore be removed, just because the constituencies are small, comparitively. Yet that's often the battle cry about things like this function idea... "Who needs it? NO ONE! When do they need it? NEVER! Then it shouldn't be added or even desired! QED, go away boiy, you're bothering me."
Again, thank you for constructively offering something I'm sure I WILL use, even though I'd usuaully prefer this.
@Roy, wildcad searching usually stops once a match has been established, so COUNTIF(something,"*abc*") would usually note the presence of the initial *, so find the 1st 'a' in something, then check whether the next 2 characters match 'bc'. If so, end and return 1, otherwise find the next 'a' in something.
Excel's * and ? wildcards are the most basic form of regular expressions, and regular expressions implemented correctly are quire efficient. Thus, you can't really complain COUNTIF would be inefficient.
No, COUNTIF() returns nothing unless there's an exact match. So if searching to see if the substring "hors" is in a cell containing "horse", one MUST use wildcards. It rather stupidly cannot find a substring on its own, but instead matches an entirety vs. an entirety, hence the requirement for the wildcard usage.
So the person Phil describes, and whom we all know, has to consider wildcards before the string, wildcards after the string, and who knows, maybe wildcards all around volleying and thundering. But they don't ride into the Valley of Death, they call Phil.
Not to mention how allowing for these possiblities affect the (should be) simple task of writing the sub-formula that might be the input of the COUNTIF() in a more complicated situation, especially not accounting for the "helpful" things people might do to aid you.
Just need a simple function that does something at least one of MS's FIND() andSEARCH() functions should have done from the start. But no, they're essentially identical, right down to taking a start number rather than an instance number. Why couldn't one of them do each? But that's a different Suggestion I suppose. 'Tis a granular world. Identical grains, it seems, no variety in this nature preserve.
But no, COUNTIF() is not really simple for many places the idea might be used. Not at all.
Peter McAnena commented
There's a simple workaround: COUNTIF("within_text", "*find_text*")
This returns 1 if within_text contains find_text and 0 otherwise. Note that you need asterisks around find_text or it won't work (but that gives you extra flexibility around checking for the start or end of the text, checking for multiple substrings, using wildcards etc.) and you may get odd behaviour if your find_text contains asterisks or other wildcards.
If you want to use just 1 function call,
This returns TRUE when substring appears in big_string and FALSE otherwise.
A function for every possible use case leads to languages like PL/I, which were so bloated they died under their own excessive inefficiency.
@Anonymous: As applies to this Suggestion? Nothing is "wrong" with FIND(). Don't get me started on FIND() in general though.
a) Will not take a range which one would hope the proposed function here WOULD do. Nor does SEARCH() take a range, so that's not on either.
b) To use it in the manner you mention requires IF() handling, actually, WITH IFERROR() to handle both sides of its result.
I know I do, and I assume the other voters would, prefer both of these things taken care of. If it could take a range, that'd solve a) and it would, in either case, provide a clear result to use without IFERROR() at least.
Remember one thing about this "forum": it is about what we'd like, not about continuing to think any workaround, no matter how simple or convoluted, is preferrable.
And, as Phil mentions, consider the many users with a more limited skill set in Excel. (Yes, FIND() is pretty basic, but No, non-straightforward uses involving it (like wrapping it in IF() and/or IFERROR() to aschieve what seems like a simple goal are not basic to them.) Or people for whom Excel is A tool and nowhere near their most important one, and for whom nothing is going to change that. Yes, they can kludge something together on the table saw using the miter gauge, but just want to go over to the radial arm saw and cut the thing. Forever. Yes, that's too bad for them, mostly, but this is where they, or others on their behalf, wish for their needs.
And my own. I cannot use FIND() on a range and that will NEVER change, so my hope lies with a new function that can be "right" from the start. Because you may have noticed, functions never, ever change like this because of backwards capablity concerns. Even though MS could put the new functionality into arguments that come after current arguments sp that formulas written 30 years agp would not be affected.
In fact, I would hope that applied to a range, it would return the cell along with the location in the cell, in a form that could be directly (not INDIRECT()-ly... so not returned as text for the cell reference...) passed into other functions in a formula. Perhaps as an argument that indicated which to return.
This forum is our hope chest, not our nope chest.
What is wrong with the current "FIND" function? =FIND("text","within text","start_number").
Returns the position of the searched for text, or #VALUE! if it isn't present. For eg say cell A1 has the text "Apple Pie". You can search for "Pie" as FIND("Pie", A1, 1).
The answer is 7 (Pie starts at character 7 counting from character 1)
Or check length vs. length after substituting "" for the string.
Sigh... they know the problem enough to work out a formal suggestion for a workaround, but don't just simply program the function.
It could even just be a "last parameter" option in a function like FIND() (as in FIND("xx",a1,,"contains") or just true/false).
You wouldn't believe the workaround I have to use for our shop software reports that come out simulating their look on printed page instead of columnar Excel files, then have to be converted (there is NO PDF conversion software that really works reliably). Just a simple CONTAINS() applied to 6-10 possible cells that might contain the string and associated data would simplify things incredibly.