Find some text in the column A

[royalB]

Hi, I am wondering. Is there any function that could find certain text in a whole column?

For example: I wanna find the word "apple" in the column A:A ... if there is such word, the result would be "yes" if not, the result would be "no"

Thank you for your insight.
Any help will be appreciated *.

[martindwilson]

depends what's in the cells
if its just a single word "apple"
if(countif(a:a,"apple"),"yes","no")
if there is other text
like
a red apple
if(countif(a:a,"*apple*"),"yes","no")

[mohamedJamsheer]

=IFERROR(IF(MATCH("*apple*",A:A,0)>0,"yes","no"),"No")

[martindwilson]

well thats a more complicated method

[Jacc]

Simplified version:

Please Login or Register  to view this content.

[martindwilson]

why match when countif suffices?

[Jacc]

Good morning Martin!
I think a similar question could be asked in a lot of threads? Why add another, different solution when a solid, good solution already exist?

One reason is the same reason (I guess) as we all hang out here on the forum in the first place, it's fun to solve these Excel formula puzzles and it's rewarding to help people.
I've been on a dry spell of suitable problems recently and I found the simplification of mohamedJamsheer's formula a fun little puzzle to solve.

While I technically don't help OP (who already has a good solution), I still feel like I am contributing by showing a better way to use MATCH in this context. OP may not be interested in this but mohamedJamsheer and anyone else who reads this thread in the future may be. One could of course argue that supplying an improved version of a previous formula in the thread AFTER a proper solution has already been supplied is off topic or something but I think it's ok.

Perhaps your question was of a more technical character and you were wondering if I think there is an advantage in using MATCH instead of COUNTIF? No, I don't think so. I imagine (haven't tested) the speed being the same. Your COUNTIF formula is very clean, compact and easy to understand. There is no rational, technical argument to justify using MATCH in this context, just irrational non-technical ones

[royalB]

Thank you guys, that helped a lot.

[mohamedJamsheer]

Hi Jacc
you are right, count if formula will work perfectly and i have not read your second formula before.
thanks,

[Jacc]

Thanks for the feedback mohamedJamsheer and Martin! This forum is a good place to hang out.

However, I just realized that I didn't mean some of the nice things I said about Martin's COUNTIF formula (sort of anyway). I'm talking about the part where I said that it is easy to understand. I don't think it is easy to understand for a beginner.

It may not be obvious to you, royalB and mohamedJamsheer, why the IF part in Martin's formula works since the COUNTIF part does not produce a TRUE or FALSE which is the normal input to an IF formula.

Common knowledge says that 0 corresponds to FALSE and 1 corresponds to TRUE. However, Excel interprets 0 as FALSE and ANY number larger or smaller than 0 as TRUE.

One would think that Martin's formula should really look like this:

Please Login or Register  to view this content.

but because of what I mentioned above the >0 part can simply be skipped.

There! I'm done ranting in this thread.