using IF with LIKE 2

[CFNB]

Hello,

if column 1 contains the word 'snow', i would like column 3 to show the word 'snow'.

if column 1 were made of only one word, it would have been easy: IF(A1="SNOW", "SNOW","")

How do I do it when column 1 contains any string that might include the word 'snow'?

Thanks.

 

[KenWright]

=IF(SEARCH("Snow",A1),"Snow")

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

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

 

[CFNB]

thanks KenWright but it did not work!
I get #value! error

I even tried : =IF(SEARCH(A1,"Snow"),"Snow")
=IF(SEARCH("Snow",A1),"Snow","")

 

[GlennUK]

Ken's formula gives results of either Snow or #VALUE! error. If you want a formula to return either Snow or blank use this:

=IF(ISERROR(SEARCH("Snow",A1)),"","Snow")

( not case sensitive ... to make it case sensitive use Find instead of Search ) )


Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[CFNB]

Thanks a lot

 

[GlennUK]

My pleasure :-D

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

One less function call then

=IF(COUNTIF(A1,"*Snow*"),"Snow","")

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

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