I can't remember how to do this.

I have an enormous spreadsheet with loads of columns and rows.

I want a formula that will check text in a specific column and if it finds the word AUTO among the text, to return a value in the next column.

So if Column A is Description and Column B is Value
If Cell A2 says something like Focus 1.6 AUTO, or Focus AUTO 1.6 Cell B2 will return 1,

but if Cell A3 says Focus 1.6, Cell B3 will say 0 or nothing at all.

 

is that not just requiring a basic logical "IF" formula?

 

it's something like

countif='auto' or soemthing along those lines

not at work so i can't check the logs to see how it works

 

I'd be trying to use something like:

=IF(SEARCH("Auto",A1),1,0)

The problem is that SEARCH returns #VALUE if the search string isn't found

Neil.

 

www.mrexcel.com/forums awesome site and fast replys

 

you can send it to me if you like and i'll have a crack but its been a while since i've used such formulas

 

Thanks guys, especially Neil S - his solution did everything I needed - ie putting in 1s, so that a data, sort could separate out the autos from the others.

Excellent!!!

 

LeeCavTurbo

I've bookmarked Mr Excel - it looks like a useful site.