wild character in IF

[22strider]

Hello Friends

Could you please see the following formula and tell why it won't work?

=IF(B2 = "SGS*", I2, B2)

basically it is checking if text in B2 starts with "SGS"; if yes then text from cell "I2" should be brought in otherwise B2.

There are other ways to do this but it not working has gotten me curious.

Thanks
Rajesh

 

[pgc01]

Hi Rajesh

You cannot use wildcards with the equal operator.

Try:

=IF(LEFT(B2,3) = "SGS", I2, B2)

 

[22strider]

Thanks PGC
How can I use wildcard in Excel then; I tried LIKE.

 

[pgc01]

Hi

The Like operator is a vba operator, does not work in formulas.

I don't see why you want to use the wildcard when the Left() function solves the problem.

If you really want to use the wildcard you could for ex. use the CountIf() function, but it is much less efficient, for ex.:

=IF(COUNTIF(B2,"SGS*")=1,I2,B2)

 

[22strider]

Thanks PGC
As I said, I just got curious about using wildcard. I've been using SQL and VBA quite a bit and took it for granted that the wildcards would work.
Thanks again for the explanation.