Match value in pipe delimited string to a value in a list

[toussaint31]

Hi - I wanted to see what the best method is to find a value in a pipe-delimited string in one cell that matches a value from a list and extract that value. Example:

Metric	Pipe Delimited Values
3	112356|229531|229327|229533|229532
5	556136|546778|558302|521840|518638|525922|523660|549684|550052|519098|524200
5	525864|525928|556136|521574|522726|525866|525930|530730|536938|525922|525924|518634|524074|525894|521544|523030|518736|520478|543442

Desired Values

223559

525928

550052

229531

Desired Result:

Metric	Desired Value Extracted from Pipe Delimited Strings	Pipe Delimited Values
3	229531	112356|229531|229327|229533|229532
5	550052	556136|546778|558302|521840|518638|525922|523660|549684|550052|519098|524200
5	525928	525864|525928|556136|521574|522726|525866|525930|530730|536938|525922|525924|518634|524074|525894|521544|523030|518736|520478|543442

Note - the actual Desired Values list is a couple thousand values. There will be exactly one value in each pipe delimited cell that matches a value in the Desired Value list.

Thank you for your help!

 

[felixstraube]

I don't understand what you need. It is not very clear to me.
What are your inputs? what is your desired result? Why?

if for example for the first row your input is "3", and the string "112356|229531|229327|229533|229532"? Who do you get 229531 as a result? Or why that value?
What am I missing? What does the "3" represent?

 

[toussaint31]

I don't understand what you need. It is not very clear to me.
What are your inputs? what is your desired result? Why?

if for example for the first row your input is "3", and the string "112356|229531|229327|229533|229532"? Who do you get 229531 as a result? Or why that value?
What am I missing? What does the "3" represent?

Sorry - That column is really just a placeholder. That column is a metric that's associated with all of those pipe delimited values. But I only care about one of the values and need to separate that value from the rest of them. Does that make sense?

 

[felixstraube]

Separate the values, yes, not a problem. Something Like the formula below.
How to know which one to select is what I don't understand. On what criteria do you select a value? Do you have an index? The closest to another value? Pic randomly one of the values?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	112356|229531|229327|229533|229532	112356	229531	229327	229533	229532
2	556136|546778|558302|521840|518638|525922|523660|549684|550052|519098|524200	556136	546778	558302	521840	518638	525922	523660	549684	550052	519098	524200
3	525864|525928|556136|521574|522726|525866|525930|530730|536938|525922|525924|518634|524074|525894|521544|523030|518736|520478|543442	525864	525928	556136	521574	522726	525866	525930	530730	536938	525922	525924	518634	524074	525894	521544	523030	518736	520478	543442
Sheet3

Cell Formulas
Range	Formula
B1:F1,B3:T3,B2:L2	B1	=TEXTSPLIT(A1, "|")
Dynamic array formulas.

 

[Fluff]

Maybe

Fluff.xlsm
	A	B	C	D	E
1			Metric		Pipe Delimited Values
2	223559		3	229531	112356|229531|229327|229533|229532
3	525928		5	550052	556136|546778|558302|521840|518638|525922|523660|549684|550052|519098|524200
4	550052		5	525928	525864|525928|556136|521574|522726|525866|525930|530730|536938|525922|525924|518634|524074|525894|521544|523030|518736|520478|543442
5	229531
Sheet5

Cell Formulas
Range	Formula
D2:D4	D2	=LET(a,TEXTSPLIT(E2,"|"),TOCOL(XLOOKUP($A$2:$A$5&"",a,a),2))