data...filter...advance

[still learning]

Hi
Is there a way to extract and query using only part of the criteria
I have a list of addresses and want to extract all the cells that have the same street name BUT the house numbers are different
As an example:
1234 Abc street
568 Abc street
111056 Abc street
123523 Def street
42561 Sef street
I want to extract all the Abc streets.
would i have to first copy the column and delete the numbers, then use the query.
hope there is an easier way

I already have the ranges set up and use them to find other things in the data


mike

 

[maabadi]

Split it by this method, then Sort all based Street Name:

Book1
	A	B	C	D	E	F	G
1			1	2	3
2	1234 Abc street		1234	Abc	street
3	568 Abc street		568	Abc	street
4	111056 Abc street		111056	Abc	street
5	123523 Def street		123523	Def	street
6	42561 Sef street		42561	Sef	street
7
Sheet1

Cell Formulas
Range	Formula
C2:G6	C2	=TRIM(MID(SUBSTITUTE(TRIM($A2)," ",REPT(" ",100)),((COLUMNS($C:C)-1)*100)+1,100))

 

[Peter_SSs]

Try

21 09 05.xlsm
	A	B	C
1		Street->	Abc street
2	1234 Abc street		1234 Abc street
3	568 Abc street		568 Abc street
4	111056 Abc street		111056 Abc street
5	123523 Def street
6	42561 Sef street
Street List

Cell Formulas
Range	Formula
C2:C4	C2	=FILTER(A2:A6,RIGHT(A2:A6,LEN(C1)+1)=" "&C1,"")
Dynamic array formulas.