Filter Function using character in a string

[fddekker]

What is the correct syntax to use with the new 365 FILTER function to filter on a character within a string? In the example below, I would like to search for all ADIDs (column E) that has an "R" as the second character.

Excel365.xlsx
	A	B	C	D	E
1	Account	Contract	Installation	Division	ADID
2	21004134736	300001311	70400431	GS	CO
3	21004146698	300002530	70397646	EL	CR
4	21000572400	300046510	70009029	EL	MN
5	21000435038	300060331	70019883	EL	MR
6	21001286646	300080379	70136473	EL	RS
7	21001602651	300080459	70136396	EL	RR
8	21001288568	300080556	70140024	EL	IN
9	21001322649	300080853	70140843	EL	IR
10	21001323449	300080929	70144111	EL	CR
11	21001609755	300080933	70140900	EL	RS
12	21001251715	300084018	70144153	EL	RR
13	21001326046	300084019	70144357	EL	RS
14	21001251871	300084034	70144197	EL	RS
Sheet4

 

[Joe4]

Try this:

=FILTER(A1:E14,MID(E1:E14,2,1)="R")

 

[Fluff]

How about

=FILTER(A2:E14,MID(E2:E14,2,1)="R")

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[fddekker]

Thanks both. I had the table defined, and that made me mess up the syntax. The full syntax I used was:
=FILTER(EVER,MID(EVER[ADID],2,1)="R"), where EVER is my table and ADID my column to filter on.
Such a great tool. Thanks again.
I am having trouble getting Excel to correctly filter dates, it seems the date must be in 'general' format for Excel to do the calculation, but I will post a different thread for it.

 

[Fluff]

Glad we could help & thanks for the feedback.