Excel formula question--finding text.?

Question

Hi--

In Excel, is there a formula that would let me highlight a particular cell and then see if the text withing that cell is repeated anywhere in a specific column?

Example:

Cell B2 contains the text "Blue" and I want to see if the text "Blue" exists anywhere in Column A. I could use the find feature but it's a long column of text so I'd have to do it for each one...Know of another option?

THANK YOU A MILLION!!!

Answer 1

over in C1 type

=COUNTIF(A1:A1000, "*BLUE*")

this will give you a count of how many cells in A1 thru A10000 contain the word "BLUE" somewhere in text .

if you want to know which ones had "blue" in cell B1 type

=FIND("BLUE", A1)

then copy paste that formula all the way down.  If blue is in the text, the formula will show the starting postion of the text.  If it's not, you'll get #VALUE.

optionally you could then sort the results so that all the #VALUES are grouped together and all the cells with blue would be together as well.