Allow for filter to accommodate multiple partial text conditions

Is there a way to change this formula to allow for two more conditions to be in the filter

=FILTER(CQ16:CR117,ISNUMBER(SEARCH("*-*",CR16:CR117)),"")

those conditions being or "*SICK*" or "*TRAINING*" within the same range.

You could try this (untested):

=FILTER(CQ16:CR117,(ISNUMBER(SEARCH("*-*",CR16:CR117))+(ISNUMBER(SEARCH("*SICK*",CR16:CR117))+(ISNUMBER(SEARCH("*TRAINING*",CR16:CR117))),"")

There are instructions at the top of the page explaining how to attach your sample workbook (yellow banner: HOW TO ATTACH YOUR SAMPLE WORKBOOK). Screenshots are of little practical use as we cannot manipulate them.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Unfortunately that didn't work it says formula in this cell contains an error.

Then please provide a sample workbook. And please stop quoting unnecessarily.

No workbook yet, so one further guess:

=FILTER(CQ16:CR117,OR(ISNUMBER(SEARCH({"*-*","*SICK*","*TRAINING*"},CR16:CR117))),"")

OK - no wonder the formulae weren't working. This is why a sample workbook at the outset would have been a good idea.

Can you explain how I would know which of the rows would be SICK or TRAINING? Those words don't appear anywhere in that range (the one that's already filtered), so you can't filter using them. I think we need some more detail.

EDIT: Oh, wait - I think I see what you mean. Hang on ...

Try this:

=FILTER(A16:B117,(ISNUMBER(SEARCH("*-*",B16:B117))+(B16:B117="SICK")+(B16:B117="TRAINING")),"")

See post #8.

It shouldn't!

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	D	E
16	1	430-1300
17	2	430-1300
18	3	430-1300
19	5	500-1330
20	6	500-1330
21	7	500-1330
22	8	500-1330
23	9	530-1200
24	10	530-1400
25	11	545-1415
26	13	600-1000
27	14	600-1000
28	15	600-1230
29	16	600-1330
30	17	600-1330
31	18	600-1330
32	19	600-1330
33	20	600-1330
34	21	600-1330
35	22	600-1430
36	23	600-1430
37	24	600-1430
38	25	600-1430
39	26	600-1430
40	27	600-1430
41	28	600-1430
42	29	600-1430
43	30	600-1430
44	31	600-1430
45	32	600-1430
46	33	600-1430
47	34	630-1330
48	37	700-1100
49	38	700-1530
50	39	7-1530
51	40	800-1200
52	51	TRAINING
53	70	SICK
54	71	SICK
55	72	SICK

Sheet: Sheet2

OK, well once again, without seeing the actual data, it's impossible to guess, but either there's something different about the real data or you've done something wrong.

Unfortunately you can't diagnose an engine problem without having the car in front of you and the bonnet open.

So can you produce a sample workbook where the error manifests itself? Because without one, I have nothing to go on.

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

Yes, I noticed. See post #18.