Changing Qualitative Data to Quantitative Data

Is it possible to create a formula that looks at a cell and returns a value of 1 if it contains a combination of letters in a specific order, anywhere within the cell.

So for instance if someone wrote: I like Red as an answer, and I want to find all cells with "Red" in them and give them a 1 how would I do that,

afaik when using countif, to compare two lists, if you looks to see howmany times someone with the username *red* is in another list, you will be returned the frequency of that combination of letters. if the username was red without the ** you would get just the instances of red on its own..

If I can get a #1 to return if the cell contains a specific combination of letters, that would inturn allow me possibility of applying all those with a 1 to a specific group.

Thanks

If the string is in a2

=IF(ISERROR(SEARCH("red",a2)),0,1)

It can be extended with an array to count the number of reds

=sum(IF(ISERROR(SEARCH("red",a2:a100)),0,1))

entered with Shft Ctrl enter

It will run quicker than the last array I sent you Sam!

regards

Dav

Cheers Dav, absolute diamond!