excel: text string contains...

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

  1. macrumors newbie

    Joined:
    Nov 13, 2006
    #1
    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

    iSaint

    Joined:
    May 26, 2004
    Location:
    South Mississippi y'all, near the water!
    #2
    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

    Joined:
    Nov 13, 2006
    #3
    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

    Am3822

    Joined:
    Aug 16, 2006
    Location:
    Groningen, The Netherlands
    #4
    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

    Joined:
    Nov 13, 2006
    #5
    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

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

    For example:

    =IF(ISERROR(FIND("payroll",A3)),0,SUM(0,1,2,3,4))

    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

    Joined:
    Nov 13, 2006
    #7
    Like it! Thanks Chief.
     

Share This Page