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.
When the search string DOES exist in the given cell, SEARCH & FIND already perform the requested "Contains" function, and there is no need to use ISNUMBER or ISERROR.
The problem is these functions fail to return a zero value if the text is NOT found. If a zero-return is made available, the need to use ISNUMBER or ISERROR is eliminated, greatly simplifying the use of SEARCH/FIND.
As these behave today, there is no need to use ISNUMBER, since a TRUE result is imputed to the numeric result:
IF(SEARCH("Look","Look here",1),"OK","NOT OK")
This returns "OK", and no ISNUMBER needed.
Unfortunately, the converse condition (search string is NOT present) is a bother to deal with if we want the NOT OK result to be returned.
It is NOT helpful to add yet another function that does the same as SEARCH and FIND, but slightly differently...therefore, everyone on this thread should go vote FOR another request in the Suggestion Box:
"SEARCH functions to return 0 (instead of #VALUE!) if the text is not found"
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
@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.
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.
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)