Ask a new question

Excluding Cells with Certain Text in Excel

I have a sales report that includes similar products. The similar products include either "14g" or "14g Trim/Shake" in the cell I am referencing. I can use the search function to pull everything that includes "14g" in the cell, but I want to exclude the ones that also include "Trim/Shake" in them. How would I do this?
Answer
Answer

Refer below image:

Image

Array formula in cell C4:

=IF(NOT(ISNUMBER(SEARCH("*Trim/Shake*",A2:A10))),A2:A10)

This excludes "Trim/Shake" while accessing cells in the range A2:A10. The formula returns the values: {"14g", FALSE, FALSE, "14g", "24g", "Trim 14g", "15g Shake", FALSE, "Shake 14g"}

In case you wish to use the formula to count the number of cells in A2:A10 which include "14g" but EXCLUDE "Trim/Shake", use below array formula in cell C2:

=SUM(--ISNUMBER(SEARCH("*14g*",IF(NOT(ISNUMBER(SEARCH("*Trim/Shake*",A2:A10))),A2:A10))))

Note: both formulas are array formulas (CTRL+SHIFT+ENTER): 

Regards,

Amit Tandon

www.globaliconnect.com

http://twitter.com/AmitTandonExcel

9 people found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 13, 2025 Views 38,241 Applies to: