excel: text string contains...

Discussion in 'Mac Apps and Mac App Store' started by heury, Nov 13, 2006.

  1. macrumors newbie

    Nov 13, 2006
    I'm on the nursery slopes of the learning curve of Excel:mac. I want to simply employ this logic: IF (text string) CONTAINS (text string) then SUM such and such. I can't find any relevent on-board help. Thanks in advance...
  2. macrumors 603


    May 26, 2004
    South Mississippi y'all, near the water!
    There is an IF:THEN function on Excel. Start with the function button on the toolbar, then use the Help function and it will walk you through the process.
  3. thread starter macrumors newbie

    Nov 13, 2006
    Yes, I can quote the whole text string and then use the operator '=' but I'm looking for a way to just determine whether the text string contains a key word, and if so, sum certain cells. I suppose I'm wanting to use an operator that doesn't exist, as in IF(B2contains"PAYROLL",C2). Maybe I need to turn the approach around and use ISIN or something...
  4. macrumors 6502


    Aug 16, 2006
    Groningen, The Netherlands
    I may be wrong, but according to Excel's online help, the function you're after is possibly SEARCH or SEARCHB (FIND/FINDB might also work -- see the previous link for further informaton).
  5. thread starter macrumors newbie

    Nov 13, 2006
    The SEARCH and FIND functions return the number of the character, reading left to right, at which the text string first appears within another text string.
  6. macrumors 6502

    Jun 9, 2004
    Between a rock and a hard place
    You can combine the functions IF, ISERROR and FIND to get the result you want.

    For example:


    The formula will look for the sub-string in the text. If it does not find it, it will return an error message. The ISERROR will detect this, in which case the sum will not be calculated and the formula will return 0 instead.

    Note that the FIND function is case-sensitive. Of course, you need to replace the argument of the SUM function with what you want to sum.

    Let me know if it does not help.
  7. thread starter macrumors newbie

    Nov 13, 2006
    Like it! Thanks Chief.

Share This Page