extract the header of sets of non empty cell

[ExcelNewbie2020]

Sirs,

I would like to extract the header of non empty cell. if there are more than 1 set empty cell, i need it textjoin.
also i need to textjoin the 1st and last non blank cell of each sets..please see column K for the result..

Book1
	A	B	C	D	E	F	G	H	I	J	K
2	A	B	C	E	F	G	H	I	J		RESULT
3	X	X	X		X	X		X	X		A-C,F-G,I-J
4	X	X		X	X						A-B,D-E
5			X	X							C-D
Sheet2

Many Thanks,

 

[DanteAmor]

Try in K2 and copy down:

=TEXTJOIN(",",,BYROW(TEXTSPLIT(TEXTJOIN("",,SUBSTITUTE(IF(A2:I2="",",",A2:I2),"X",A$1:I$1)),,","),LAMBDA(br,IF(LEN(br)>1,LEFT(br)&"-"&RIGHT(br),br))))

 

[DanteAmor]

I assume that the headers are not letters and can be any text, in such case:

=TEXTJOIN(",",1,SUBSTITUTE(BYROW(TEXTSPLIT("|"&TEXTJOIN("|",,IF(A2:I2="",",",A$1:I$1))&"|",,","),LAMBDA(br,IF(LEN(br)-LEN(SUBSTITUTE(br,"|",""))>2,MID(br,1,SEARCH("|",br,2)-1)&"-"&TRIM(RIGHT(SUBSTITUTE(br,"|",REPT(" ",99)),198)),br))),"|",""))

 

[ExcelNewbie2020]

I assume that the headers are not letters and can be any text, in such case:

=TEXTJOIN(",",1,SUBSTITUTE(BYROW(TEXTSPLIT("|"&TEXTJOIN("|",,IF(A2:I2="",",",A$1:I$1))&"|",,","),LAMBDA(br,IF(LEN(br)-LEN(SUBSTITUTE(br,"|",""))>2,MID(br,1,SEARCH("|",br,2)-1)&"-"&TRIM(RIGHT(SUBSTITUTE(br,"|",REPT(" ",99)),198)),br))),"|",""))

thanks man..