Wildcard in new filter function

[Razzy]

FILTRER((F46:F100^2+G46:G100^2)^2*H46:H100;(I46:I100="O1")

I was hoping to write O* og O? Wildcards not possible in this function?

The possible entries are: O1, O2, O3.

 

[jamiemarie]

It does not appear so, apparently the workaround is to use SEARCH.

FILTER - With wildcard

FILTER using * Problem we want to solve We have data in a Range (or in Table1) and want to filter them with dynamic array FILTER function, according to criteria in B1. But we want the result to

answers.microsoft.com

 

[Fluff]

No, filter does not take wildcards, but you can use
isnumber(search("O",I46:I100))

 

[jamiemarie]

@Fluff From the article in post #2 it appears that SEARCH is for non case sensitive, FIND is for case sensitive, and so by your reply is ISNUMBER required for numbers? What about dates?

 

[Fluff]

Both search & find return either a number, or a #value! error, so the isnumber function is used to convert the array into true or false.

 

[jamiemarie]

Thank you for the explanation. I'm still waiting on the function, I believe IT hasn't updated us yet, but I'm trying to learn as much as I can about it in the mean time!

 

[Razzy]

Gracias as always!

 

[Razzy]

No, filter does not take wildcards, but you can use
isnumber(search("O",I46:I100))

TK - X.xlsm
	R	S
46	0	USANN
47	0	SANN
48	0	USANN
49	0	USANN
50	0	USANN
51	0	USANN
X-S

Cell Formulas
Range	Formula
R46:R94	R46	=FILTER((F46:F100^2+G46:G100^2)^2*H46:H100,(I46:I100=ISNUMBER(SEARCH("O?",I46:I100))))
S46:S100	S46	=ISNUMBER(SEARCH("O?",I46:I100))
Dynamic array formulas.

TK - X.xlsm
	E	F	G	H	I
46	1	0,3	0,2	3,34	R
47	15	0,3	0,1	2,19	O1
48	4	0,3	0,15	3,34	F
49	5	0,3	0,8	3,34	F
X-S

Looks like it works, but can you see why the formula in R46 doesen't return my value?

It should return: 0,0219, because it is true.

 

[Fluff]

The filter criteria should just be the isnumber... formula

 

[Razzy]

The filter criteria should just be the isnumber... formula

TK - Amalienborg Brygge BT1.xlsm
	R
46	0,0219
1000-S

Cell Formulas
Range	Formula
R46	R46	=FILTER((F46:F100^2+G46:G100^2)^2*H46:H100,(ISNUMBER(SEARCH("O?",I46:I100))))

There I go, thx m8!