IF formulas and partial text

[brunette]

Hi,

I'm pretty sure this is an easy thing but for the life of me I can't figure out how to get this to work...

I am trying the following formula in Excel 2010 to find a part of text in a cell and then, depending on whether it finds the text, return the corresponding values:

=IF(A1="*Total*";B1;0)

It should be pretty simple, right?

The problem is that even though cell A1's value is:- Item 1 Total, the formula consistently returns 0. However, If I do a straight up
=IF(A1="Item 1 Total";B1;0) it returns the value in cell B1 correctly.

What am I doing wrong?! I have thousands of lines with different item names, so doing it the latter way isn't feasible.

Ps:-just in case you're wondering, my regional settings are configured to require a semi-colon as separator, instead of just a comma.

Thanks in advance for your help!
Ana

 

[brunette]

I found a suitable workaround:-

=IF(ISNUMBER(SEARCH("Total";A1));B1;0) works. It's, IMHO, not as quick and easy and simple as what I originally wanted, but, hey, it works - I'm happy!

 

[Rick Rothstein]

I found a suitable workaround:-

=IF(ISNUMBER(SEARCH("Total";A1));B1;0) works. It's, IMHO, not as quick and easy and simple as what I originally wanted, but, hey, it works - I'm happy!

You could try this formula as well...

=IF(COUNTIF(A1;"*Total*");B1;0)

 

[Peter_SSs]

What am I doing wrong?!

What you are doing wrong is thinking that an IF() function works with wildcards. It doesn't. The only way that your original formula would return the B1 value is if A1 conatined the exact text: "*Total*"

 

[JazzSP8]

I don't know why using the wildcard doesn't work with IF like that, but this how I do similar things - Just an FYI for next time

=IF(FIND("Total",A1,1),B1,0)

 

[RoryA]

I don't know why using the wildcard doesn't work with IF like that, but this how I do similar things - Just an FYI for next time

=IF(FIND("Total",A1,1),B1,0)

That won't ever return 0 - if the FIND fails, it returns a #VALUE ! error, not FALSE. Also, just to note, FIND is case sensitive, unlike SEARCH.