Capturing a specific string within another cell...

[kbishop94]

I'm trying to figure out a formula that will capture only specific parts of a cells string...

I need a cell in Q to capture specific parts of the adjacent cell to it in column R:


I need to capture only the following strings that may (or may not) reside as part of cell R: "CSR21U", "CSR22V", "CSR23W", "CSR24X" or "CSR25Y"

so in the example pics I have shown above, in this particular cell (R18), both CSR23W and CSR24X are present:



meaning CSR23W and CSR24X would be copied from the target cell and will be displayed in the formula row in cell Q18:


Any help is surely appreciated!!

 

[Sufiyan97]

When "TRANSPOSE(TEXTSPLIT(R1,CHAR(10)))" is applied, the result is an array of values, e.g. "CSR21U", "CSR22V", "CSR23W", "CSR24X", "CSR25Y"; "a" represents each value of this array and basically says to XLOOKUP search for the first one, then for the second one etc.

Thank you for the explanation, I am trying to learn LAMBDA, but I still do not get it, where does "a" come from and how it will work in this formula because we have never given any calculation to it.

 

[hagia_sofia]

Thank you for the explanation, I am trying to learn LAMBDA, but I still do not get it, where does "a" come from and how it will work in this formula because we have never given any calculation to it.

In this case it is not a calculation, "a" represents a row within an array.