Home > Blockchain >  Filter out parts of text strings using whole String in Excel Table
Filter out parts of text strings using whole String in Excel Table

Time:03-26

I would like to filter out Column B (Code strings combinations separately in rows in a long list) using whole text string from Column A.

What I did is filter out table manually in Column B for couple of strings, but in future I will be getting lots of strings and I need to do that swiftly and efficiently.

In Column C are the results of all the combinations from Column B per row, and there I need values as they are, so no unique values. Because if you look at Column B and combinations, there some of them that have more than single code combined (and that is fine), exact combinations I need to have in results column.

In Column B and C you can see "basis values" in rows, they are predifined and should be there.

I tweaked a bit using this function:

=FILTER(Table1;ISNUMBER(SEARCH("CSxxxFX_CE1,CE2_Dxx_Exx_FB0_FMx_GP3_IT0_JH0_LB0_MV1_MW0_NZ0_OZ0_QS0";Table1[Code String])))

But it cant feed whole string inside actually...only parts. Similar I did in Power Query....

I would accept VBA solution too..

The Data and workbook are on the link below.

enter image description here

enter image description here

Formula in E2:

=VSTACK(B2:B3,UNIQUE(FILTER(B4:B374,BYROW(IFERROR(SEARCH(","&TEXTSPLIT(A2,{"_",","})&",",","&B4:B374&","),0),LAMBDA(a,SUM(a))),"None Found")))

TEXTSPLIT() and VSTACK() are rather new functions currently available to users in the ms365 insiders BETA-channels.


Without access one could use the following in C4 (assuming you will bring over B2:B3 statically:

=UNIQUE(FILTER(B4:B374,LET(X,TRANSPOSE(FILTERXML("<t><s>"&SUBSTITUTE(SUBSTITUTE(A2,",","_"),"_","</s><s>")&"</s></t>","//s")),MMULT(IFERROR(FIND(","&X&",",","&B4:B374&","),0),SEQUENCE(COUNTA(X),,,0))),"None Found"))
  • Related