TEXTJOINARRAY Joins the text in an array by row or column, with delimiter
Excel Formula:
=LAMBDA(array,delimiter,byColumn,
LET(
a,IF(byColumn,TRANSPOSE(array),array),
d,delimiter,
s,SEQUENCE(ROWS(a),1,0),
tj,TEXTJOIN(d,TRUE,a),
l,LEN(d),seqL,(s*0)+(l*COLUMNS(a)),
end,SUMCUMULATIVE(SUMROWS(LEN(a)+l)),
start,IF(s=0,1,1+INDEX(seqL,s,1)+INDEX(end,s,1)),
md,MID(tj,start,end-start+1),
return,IF(byColumn,TRANSPOSE(md),md),
return)
)| Lambda - Last Cell and Split.xlsx | ||||||||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | J | K | L | M | N | O | |||
| 1 | Original Array | |||||||||||||||
| 2 | test this | t | e | s | t | t | h | i | s | |||||||
| 3 | this test | t | h | i | s | t | e | s | t | |||||||
| 4 | further test | f | u | r | t | h | e | r | t | e | s | t | ||||
| 5 | ||||||||||||||||
| 6 | ||||||||||||||||
| 7 | Text join | |||||||||||||||
| 8 | test this | |||||||||||||||
| 9 | this test | |||||||||||||||
| 10 | further test | |||||||||||||||
Sheet1 | ||||||||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C2:N4 | C2 | =TEXTTOARRAY(B2:B4) |
| B8:B10 | B8 | =TEXTJOINARRAY(C2#,"",) |
| Dynamic array formulas. | ||
Upvote
0
