Issue with Advanved Filter Wildcard! please help!

[cocorico]

Hi all,
I am working on my advanced filter. I would like to find all date records that contain 2014. The issue is that some records contain Q1 2014, Q2 2014, etc and others have 2014 strictly as date.
If i type in "2014" in the crteria ecxel will return all 2014 records BUT NOT q1 2014 nor q2 2014.
If i type in *2014 excel returns q1 2014, q2 2014 BUT NOT 2014.
Can somebody help? It's quite urgent! Please do not come up with an autofilter solution, i am preparing a big advanced filter solution.
Cheers

 

[RoryA]

You'd need to use two rows - one with 2014 and one with "*2014". If you have other criteria required at the same time, they will need to be duplicated across both rows. You could also use a formula criteria range.

 

[cocorico]

You'd need to use two rows - one with 2014 and one with "*2014". If you have other criteria required at the same time, they will need to be duplicated across both rows. You could also use a formula criteria range.

Hi Rory,
Thank you very much for your help. I thought about the two rows option, in fact in my case it would be 2 times 8 rows as other crtieria would need to be duplicated too.
What is the criteria formula range?
Thank you.

 

[RoryA]

In your criteria range, instead of using a column titled "Date" (or whatever your field name is), leave the header cell blank, and use a formula like:
=ISNUMBER(SEARCH("2014",A6))
where A6 is the first row of value data (not the header) for the date field in your data table. So for the example below the criteria range is A1:C2.

	A​	B​	C​
1​		Field2	Field3
2​	=ISNUMBER(SEARCH("2014",A6))​	blah	test
3​
4​
5​	Date	Field2	Field3
6​	2014​	blah	test
7​	2013​
8​	Q4 2014
9​	Q4 2013	blah
10​	Q1 2013
11​	Q2 2012	blah	test
12​	Q3 2014	blah

 

[cocorico]

Great! That works! Thank you so much for your help!