CONTAINS function (Excel 2002)

Topic

New Reply

Is there a formula that will produce a result for “contains”… for example, I have an “IF” formula that reads a cell containing multiple words. I’d like the formula to say “IF” cell A3 “CONTAINS” the word “ACCRUAL”, then the result in cell C3 is “YES”. I can’t use the LEFT or RIGHT or MID formulas as the word “ACCRUAL” can be in any position in the narrative.
Thanks!
Lana

Reply | Quote

Replies

You can use the FIND or SEARCH function for this. FIND is case-sensitive, SEARCH isn’t. FIND and SEARCH both return a number if the search text is found, and #VALUE if not. You can use IF and ISERROR to translate this to “Yes” and “No” (or whatever you want).

The formula to determine if ACCRUAL occurs in the value of cell A3 is

=IF(ISERROR(FIND("ACCRUAL",A3)),"No","Yes")

If ACCRUAL might be written as Accrual or accrual, use SEARCH instead of FIND.

Reply | Quote

Thank you Hans & Jerry… I appreciate the help!
Lana

Reply | Quote

Hi Lana

Use the Find function in conjunction with the iserror, assume the string value containing the substring is in A1

=IF(ISERROR(FIND(“Accrual”,A1,1))=FALSE,”True”,”False”)

Be aware that FIND is case sensitive

Reply | Quote