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.
@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.