excel: text string contains...

[heury]

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...

 

[iSaint]

I'm on the nursery slopes of the learning curve of Excel:mac. I want to simply exploy 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...

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.

 

[heury]

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.

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...

 

[Am3822]

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).

 

[heury]

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).

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.

 

[Caezar]

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.

 

[heury]

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.

Like it! Thanks Chief.