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

Question

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.

https://docs.google.com/spreadsheets/d/1nQxYcrj_jnv2xf-QX9hwKCjdWdso2QM7/edit?usp=sharing&ouid=101738555398870704584&rtpof=true&sd=true

JvdV · Accepted Answer · 2022-03-25 11:15:46Z

1

Right, if I do understand correctly now; the following might work:

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"))

edited Mar 25, 2022 at 11:15

answered Mar 25, 2022 at 9:57

JvdV

76.8k8 gold badges48 silver badges89 bronze badges

Sign up to request clarification or add additional context in comments.

14 Comments

MmVv Over a year ago

Good approacht, but you took Results (Column C) as a filter and that is wrong, it is just result what I wanna have. You need to filter whole string from column A into column B to get column C...in my Column C is what I need to have if I filter column B from column A

JvdV Over a year ago

@mirza, why does the results start with basis then? And why empty values? Strange sample

MmVv Over a year ago

I dont know how to explain it better. I think I wrote exactly, I have column A with main long string, and wanna to filter it by column B and to get column C, with all the matches that it finds in column B. Basis values are predefined in my table, ahh empty space was accidently, I just saw it - sorry.

JvdV Over a year ago

What about all the other rows with the word "basis", why are they not in your desired result?

MmVv Over a year ago

Hmm yes they are if you see better Column C, 2x basis rows at the beginning and then the code strings...

|

Collectives™ on Stack Overflow

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

1 Answer 1

14 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

14 Comments

Your Answer

Sign up or log in

Post as a guest

Related