contains / conditional 1

[renartbrazil]

=LEFT(C5,LEN($C$2))=$C$2

what do i sub LEFT with if i want 'Contains'? instead of the conditional saying that if current cell begins with what ever is typed in C2 then condition. any ideas?

 

[cheerio]

Use the find function.

 

[KenWright]

=COUNTIF(C5,"*"&C2&"*") gives you a 1 if found

or perhaps

=ISNUMBER(SEARCH(C2,C5)) gives you TRUE if found

or perhaps

=--ISNUMBER(SEARCH(C2,C5)) gives you a 1 if found

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[KenWright]

Need to be careful if you use FIND() as it is case sensitive.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[cheerio]

Need to be careful if you use FIND() as it is case sensitive.

A good point - it depends what is required and SEARCH may well be the better bet.

It is perhaps worth adding that both FIND and SEARCH return a #VALUE! error if there is no match - hence the need for the ISNUMBER function in your suggested reply which was clearly more thought out than mine.

 

[renartbrazil]

thanks for those, i tried them and looks like they work but i have been trying myself to change it so it reads that cell C2 stays constant. is this possible with these examples. i actually copy C5 down the whole column. that is why the $C$2 appears. can we get this into some of those?

 

[KenWright]

Just do it

=COUNTIF(C5,"*"&$C$2&"*") gives you a 1 if found

or perhaps

=ISNUMBER(SEARCH($C$2,C5)) gives you TRUE if found

or perhaps

=--ISNUMBER(SEARCH($C$2,C5)) gives you a 1 if found




----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------