Share via

Excluding Cells with Certain Text in Excel

Anonymous
2017-04-26T18:53:52+00:00

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?

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. Anonymous
    2017-04-27T07:25:50+00:00

    Refer below 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 answer helpful.
    0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 100.9K Reputation points Volunteer Moderator
    2017-04-26T23:56:49+00:00

    Hi,

    Share a screenshot of your data.  Do you want to filter the data such that you see exclude all rows which contain Trim/Shake in them?

    0 comments
  2. Anonymous
    2017-05-01T14:08:46+00:00

    Hi Chris,

    Have Amit's suggestions solved your questions?

    Regards,

    Yoga

    0 comments