TEXTJOIN/CONCATENATE non-blanks with a limit on number of cells it returns

[TheTallBloke]

Hi all,

I'm looking to bring together the first 7 instances of non-blank columns - is there an easy non-VBA way to do this?

Example data below, I am looking to fill in column F with the first 7 non-blanks (in reality, I would be looking to do this across 50+ columns).

[TABLE="width: 1430"]
<tbody>[TR]
[TD][TABLE="width: 1430"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD][/TD]
[TD] [TABLE="class: grid, width: 1302"]
<tbody>[TR]
[TD]Spec1[/TD]
[TD]Spec2[/TD]
[TD]Spec3[/TD]
[TD]Spec4[/TD]
[TD]Spec5[/TD]
[TD]Spec6[/TD]
[TD]Spec7[/TD]
[TD]Spec8[/TD]
[TD]Spec9[/TD]
[TD]Spec10[/TD]
[TD]Spec11[/TD]
[TD]Spec12[/TD]
[TD]Spec13[/TD]
[TD]Spec14[/TD]
[TD]Spec15[/TD]
[TD]Spec16[/TD]
[TD]Spec17[/TD]
[TD]Spec18[/TD]
[TD]First 7 non-blank specs[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD][/TD]
[TD]d[/TD]
[TD]e[/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD]j[/TD]
[TD]n[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]r[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]x[/TD]
[TD][/TD]
[TD]+ a
+ d
+ e
+ h
+ j
+ n
+ r[/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[TD]f[/TD]
[TD]g[/TD]
[TD]h[/TD]
[TD]k[/TD]
[TD][/TD]
[TD]o[/TD]
[TD][/TD]
[TD]q[/TD]
[TD]r[/TD]
[TD][/TD]
[TD][/TD]
[TD]u[/TD]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]a[/TD]
[TD]c[/TD]
[TD]d[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]h[/TD]
[TD]l[/TD]
[TD]n[/TD]
[TD][/TD]
[TD]p[/TD]
[TD][/TD]
[TD]r[/TD]
[TD][/TD]
[TD][/TD]
[TD]v[/TD]
[TD]x[/TD]
[TD]y[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thank you.

 

[mikerickson]

Try something like =TRIM(LEFT(SUBSTITUTE(TEXTJOIN(",", TRUE, A1:BB1), ",", REPT(" ",255), 6), 255))

 

[TheTallBloke]

EDIT: Worked like a charm, thank you!