CODEWITHSUNDEEP
excelsearchfilterexcel-formula
Wisp
Wisp

Reputation: 308

=FILTER an inverse result of a partial string match in Excel

Summary

I am attempting to filter out a dataset within Excel using =FILTER. I would like to search the dataset for a partial string, and filter these results out.

Current Progress

I am aware of the use of <> within =FILTER to omit results. My current method to find the string is to use ISNUMBER(SEARCH("postmaster",A:A)) to find all results that have "postmaster" within the cell contents. I have attempted to combine both uses, into the following:

=FILTER(A:A,(A:A<>"[email protected]")*(A:A<>0)*(ISNUMBER(SEARCH("postmaster",A:A)))) Currently, this removes all results with "[email protected]" in column A, but then only shows those that contain "postmaster". I am trying to invert this, so it instead filters OUT any that contain postmaster. I want to combine the ISNUMBER / SEARCH logic with the <> exclude logic. Is this possible?

Conclusion I am aware of the premise, but am unable to put them together. I'd appreciate any help in doing this. I am using MSO 365, version 2108. Thanks!

Upvotes: 0

Views: 866

Answers (1)

Wisp
Wisp

Reputation: 308

After further tinkering, I found the solution. This was to wrap the ISNUMBER SEARCH within a =NOT statement, to invert the output.

The new working formula is:

=FILTER(A:A,(A:A<>"[email protected]")*(A:A<>0)*(NOT(ISNUMBER(SEARCH("postmaster",A:A)))))

If anyone wants to exclude results from a partial string match, they can use the following example:

=FILTER(DATA:DATA,NOT(ISNUMBER(SEARCH("STRINGTOSEARCH",DATA:DATA))))

Hopefully this helps someone else in the future!

Upvotes: 2

Related Questions