Filter a table with a list of parts of strings

[psevet]

Hello all,

As a new member, I have a tough excel question : in the Excel file ht tps://1drv.ms/x/s!Aqb3mLRtC0iI0SpjqzHclsSHxrh_?e=LvensJ, I want to use a FILTER function (not VBA ...) that would extract all the datas from the table where column Name contains part of names in J3:JXXX. For instance, for the string "ello", I want to get an extract of all lines where Name contains "ello", wich would give the following results :

id	first_name	Name	email	gender	ip_address
10​	Anderson	Tolumello	a t o l u m ello 9 @ g o o g l e . f r	Male	2 4 5 . 1 6 6 . 2 3 . 1 1 6
44​	Byrle	Tomaello	b t o m a ello 1 7 @ w h i t e h o u s e . g o v	Male	3 2 . 1 3 9 . 4 6 . 4 3
62​	Berenice	Flello	b f l ello 1 p @ o x . a c . u k	Female	1 0 7 . 2 5 4 . 1 9 9 . 1 7 5

Of course, I would like to do the same for all strings in J3:JXXX (last cell depending from the number of strings)

Thanks in advance.

Regards.

Pierre

 

[ETAF]

=FILTER(A2:E1000,ISNUMBER(SEARCH(I3,B2:B1000)))

BUT if there is more than 1 entry that will fill the rest of the rows
and so not something to copy down for the next value in I

 

[psevet]

=FILTER(A2:E1000,ISNUMBER(SEARCH(I3,B2:B1000)))

BUT if there is more than 1 entry that will fill the rest of the rows
and so not something to copy down for the next value in I

Hello Etaf,

Thank you for your efforts in trying to find a solution to this issue

It seems that this formula doesn’t quite produce the expected result.

Although I didn’t get much help from ChatGPT on this one, I’m sure we’ll find a solution together.

Best regards,

Pierre

 

[ETAF]

It seems that this formula doesn’t quite produce the expected result.

you will need to explain why not - what are your expected results , I dont know i'm afraid and the formula seems to work , based on First name
can you provide a sample spreadsheet and attach here like i have

I dont see a

Name contains part of names in J3:JXXX.

so had to guess on name

 

[psevet]

Hello Etaf,

Here's what I try to acheive : ht tps://1drv.ms/x/s!Aqb3mLRtC0iI0SpjqzHclsSHxrh_?e=SdqRtf

 

[ETAF]

why not just attach to th epost - a lot of people will not link on unknown links
anyway

=FILTER(A1:D1000,(ISNUMBER(SEARCH(H3,C1:C1000)))+(ISNUMBER(SEARCH(H4,C1:C1000))))
see N15 for comparrison

 

[psevet]

Oops did'n pay attention to below button ...

Actually, I would need a formula that would extract all data, no matter the number of partial strings is.

Here's my basic excel file for 2 strings, but it could be 20 or 100.

 

[ETAF]

this may work, not fully tested for more than the 2 examples you have provided - not 100% certain the results
=FILTER(A2:D1000,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(H3:H4),C2:C1000)),ROW(H3:H4)^0)>=1,"None")
BUT the range H3:H4 - needs to cover the list you have of 2 or 20 or 100
doesnt work with blnk cells in the list
not sure what version of excel you have , but there is a way to do the same with LAMDA - but i dont know how to do that
OR reference as tables

maybe have a watch of this video

 

[Debaser]

If you do have LAMBDA:

=FILTER(A2:D1001,BYROW(C2:C1001,LAMBDA(r,COUNT(SEARCH(H3:H4,r))>0)))

 

[AlanSidman]

A power query option

let
Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
#"Filtered Rows" = Table.SelectRows(Source, each Text.Contains([Name], "iel") or Text.Contains([Name], "lew"))
in
#"Filtered Rows"

 

[psevet]

this may work, not fully tested for more than the 2 examples you have provided - not 100% certain the results
=FILTER(A2:D1000,MMULT(--ISNUMBER(SEARCH(TRANSPOSE(H3:H4),C2:C1000)),ROW(H3:H4)^0)>=1,"None")
BUT the range H3:H4 - needs to cover the list you have of 2 or 20 or 100
doesnt work with blnk cells in the list
not sure what version of excel you have , but there is a way to do the same with LAMDA - but i dont know how to do that
OR reference as tables

maybe have a watch of this video

Hello Etaf,

Great job I should find a workaround for column H.

By the way, I use office 365 family editionat home and Enterprise at work.

Thanks a lot

Pierre

 

[psevet]

If you do have LAMBDA:

=FILTER(A2:D1001,BYROW(C2:C1001,LAMBDA(r,COUNT(SEARCH(H3:H4,r))>0)))

Hello Debaser, your solution works as Etaf's. I will work on both versions

Thanks a lot

Regards.

Pierre

 

[psevet]

A power query option

Hello AlanSidman,

Thanks a lot

I may work on this workaround (still a noob on PQ ...)

Regards.

Pierre

 

[ETAF]

you are welcome