Combine headers from array

[Morgzord]

I'm trying to combine a series of headers in an option array with a | delimiter between them.

I can get the delimiter in quite easy with a series of IF arguments, but I cannot figure out how to get rid of the leading delimiter.

The original forumala I used was:

=IF(ISTEXT(B2),$B$1,"")&IF(ISTEXT(C2),"|"&$C$1,"")&IF(ISTEXT(D2),"|"&$D$1,"")&IF(ISTEXT(E2),"|"&$E$1,"")

I got to:

=IF(ISTEXT(L6),$L$1,"")&IF(ISTEXT(M6),IF(ISBLANK(L6),"","|")&$M$1,"")&IF(ISTEXT(N6),IF(ISBLANK(L6:M6),"","|")&$N$1,"")&IF(ISTEXT(O6),IF(ISBLANK(L6:N6),"","|")&$O$1,"")

but the range wouldn't work. Obviously this is with the existing worksheet cell refs.

Table below, any help appreciated

Item Options	Matrix Colour	Matrix Size	Matrix Stone	Matrix Material
|Matrix Size|Matrix Material		U60		SIL
Matrix Colour|Matrix Size|Matrix Stone	RED	U90	TUQ
|Matrix Material				GOL
|Matrix Size|Matrix Material		U60		SIL

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Rick Rothstein]

I'm trying to combine a series of headers in an option array with a | delimiter between them.

I can get the delimiter in quite easy with a series of IF arguments, but I cannot figure out how to get rid of the leading delimiter.

The original forumala I used was:

=IF(ISTEXT(B2),$B$1,"")&IF(ISTEXT(C2),"|"&$C$1,"")&IF(ISTEXT(D2),"|"&$D$1,"")&IF(ISTEXT(E2),"|"&$E$1,"")

I am not sure what your second formula is doing, so let's work with your original formula (above). The key to making it work is to make the first item in the concatenation the same as all the rest, that way you are guaranteed to have a leading delimiter under all circumstance, then you can get rid of it using the MID function....

=MID(IF(ISTEXT(B2),"|"&$B$1,"")&IF(ISTEXT(C2),"|"&$C$1,"")&IF(ISTEXT(D2),"|"&$D$1,"")&IF(ISTEXT(E2),"|"&$E$1,""),2,99)

 

[Morgzord]

Worked a treat Rick.

Thanks for your help.