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!!

 

[hagia_sofia]

Could you test this:

=TEXTJOIN(CHAR(10),TRUE,BYROW(TRANSPOSE(TEXTSPLIT(R1,CHAR(10))),LAMBDA(a,XLOOKUP(a,F1:F5,F1:F5,""))))

where F1:F5 is your list of values to search, i.e. "CSR21U", "CSR22V", "CSR23W", "CSR24X" or "CSR25Y".

 

[Sufiyan97]

Hi @hagia_sofia
How this LAMBDA(a, works can you please explain.

 

[hagia_sofia]

Hi @hagia_sofia
How this LAMBDA(a, works can you please explain.

The TRANSPOSE(TEXTSPLIT(R1,CHAR(10))) part returns an array of individual values; BYROW - LAMBDA(a) provides XLOOKUP with each part of the array (each row) to look for against the list of values.

 

[Cubist]

The merged cells might be problematic. Not sure what you're expecting to return for the sequent rows, but here's an option that returns blanks.

Book1
	Q	R
18	CSR23W CSR24X	CSR0o CSR23W CSR24X CSR10J CSR12L CSR13M CSR35II ALL
19
20
21
22
23
24
25	CSR25Y	CSR0Oo CSR25Y
Sheet1

Cell Formulas
Range	Formula
Q18:Q25	Q18	=LET(x,{"CSR21U","CSR22V","CSR23W","CSR24X","CSR25Y"},TEXTJOIN(CHAR(10),,IF(ISNUMBER(SEARCH(x,R18)),x,"")))

 

[kbishop94]

Could you test this:

=TEXTJOIN(CHAR(10),TRUE,BYROW(TRANSPOSE(TEXTSPLIT(R1,CHAR(10))),LAMBDA(a,XLOOKUP(a,F1:F5,F1:F5,""))))

where F1:F5 is your list of values to search, i.e. "CSR21U", "CSR22V", "CSR23W", "CSR24X" or "CSR25Y".

Hello hagia_sofia, thank you for your suggestion. However,



I get the dreaded #REF!
Would it have anything to do with how the cell (Q18) is formatted? (I tried some different kinds formats, but to no avail)


But I did have some luck with the suggestion that Cubist offered (shown in cell Q20)

 

[kbishop94]

The merged cells might be problematic. Not sure what you're expecting to return for the sequent rows, but here's an option that returns blanks.

Book1
	Q	R
18	CSR23W CSR24X	CSR0o CSR23W CSR24X CSR10J CSR12L CSR13M CSR35II ALL
19
20
21
22
23
24
25	CSR25Y	CSR0Oo CSR25Y
Sheet1

Cell Formulas
Range	Formula
Q18:Q25	Q18	=LET(x,{"CSR21U","CSR22V","CSR23W","CSR24X","CSR25Y"},TEXTJOIN(CHAR(10),,IF(ISNUMBER(SEARCH(x,R18)),x,"")))

Thank you Cubist.... your formula and suggestion worked! (see the screen shot shown in my previous reply to hagia_sofia)
also, there are no merged cells involved here. the screen shot just made it look that way

 

[Sufiyan97]

The TRANSPOSE(TEXTSPLIT(R1,CHAR(10))) part returns an array of individual values; BYROW - LAMBDA(a) provides XLOOKUP with each part of the array (each row) to look for against the list of values.

Thanks, but what does "a" does?

 

[Cubist]

Would it have anything to do with how the cell (Q18) is formatted? (I tried some different kinds formats, but to no avail)

You'd need to replace the #REF inside the formula with R18. You must've deleted row 1 at some point.

Thank you Cubist.... your formula and suggestion worked!

You're welcome.

 

[hagia_sofia]

Thanks, but what does "a" does?

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.