Indexing value from non adjacent

[mart_mrexcel]

In the given table below, starting from I2 cell, i want to get the first Spvsr Position code starting from the right (A1, C1, E1,G1).
In I2 the expected value is 50087936 and in I3 the expected value 50087936 (excluding the cell value with "---")
In J2, i need to get the second value and
in K2, i need to get the third value.

	A	B	C	D	E	F	G	H	I
1	Spvsr 1 Code	Spvr 1 Position	Spvsr 2 Code	Spvsr 2 Position	Spvsr 3 Code	Spvsr 3 Position	Spvsr 4 Code	Spvsr 4 Position
2	50094562	S. Head	50086819	Mgr	50087936	GM	---	---
3	50086819	Mgr	50087936	GM	---

 

[Kaper]

In I2:

=CHOOSECOLS(SUBSTITUTE(A2:H3,"---",""),SEQUENCE(1,4,1,2))

of course instead of H3 use a real bottom cell address

 

[mart_mrexcel]

In I2:

=CHOOSECOLS(SUBSTITUTE(A2:H3,"---",""),SEQUENCE(1,4,1,2))

of course instead of H3 use a real bottom cell address

Thank you Kaper

 

[kvsrinivasamurthy]

Try. In I2. This will give only Sup Numbers.

=TEXTSPLIT(TEXTJOIN(",",0,BYROW(SUBSTITUTE(B2:H3,"---",""),LAMBDA(r,TEXTJOIN("_",0,IFERROR(INDEX(r,LARGE(IF(r<>"",COLUMN(B1:H1)-COLUMN($A$1),""),{2,4,6})),""))))),"_",",",0,1,"")

 

[Kaper]

Glad it worked for you and thanks for the feedback