View Full Version : excel: text string contains...

heury

Nov 13, 2006, 05:39 AM

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

Nov 13, 2006, 05:54 AM

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

Nov 13, 2006, 06:27 AM

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

Nov 13, 2006, 06:42 AM

I may be wrong, but according to Excel's online help, the function you're after is possibly SEARCH or SEARCHB (http://office.microsoft.com/en-us/excel/HP052092491033.aspx) (FIND/FINDB might also work -- see the previous link for further informaton).

heury

Nov 13, 2006, 11:39 AM

I may be wrong, but according to Excel's online help, the function you're after is possibly SEARCH or SEARCHB (http://office.microsoft.com/en-us/excel/HP052092491033.aspx) (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

Nov 13, 2006, 07:25 PM

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

Nov 14, 2006, 07:39 AM

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.