Squashing a column of values into a single comma-separated string used to mean TEXTJOIN, or a helper column, or both. REDUCE does it in one formula, and because every value passes through a LAMBDA on the way in, you can transform each item before it lands in the string. Three lines, no helper columns.
How to do it
You've got names in B2:B20 and you want them in one cell as Ada, Grace, Linus, ....
- Click an empty cell.
- Type:
=REDUCE("", B2:B20, LAMBDA(acc, val, IF(acc="", val, acc & ", " & val))) - Press Enter.
The whole column collapses into one string. The IF(acc="", ...) is the only fiddly part: it stops you getting a stray , at the very front, because the first value has nothing to be joined to yet.
Want to transform each item on the way in? Do it to val. Wrap every name in quotes for a SQL IN list:
=REDUCE("", B2:B20, LAMBDA(acc, val, IF(acc="", "'" & val & "'", acc & ", '" & val & "'")))That returns 'Ada', 'Grace', 'Linus', ready to paste straight into a query.
Why it works
REDUCE folds an array down to a single value. It starts with the initial value (here, an empty string), then walks the range, and at each step runs your LAMBDA with the result so far (acc) and the current value (val). Whatever the LAMBDA returns becomes the new acc. By the last row, acc is the finished string.
The reason this beats TEXTJOIN is the val slot. TEXTJOIN joins values as they are. REDUCE hands you each value individually, so you can quote it, trim it, upper-case it, or skip it with a conditional before it ever gets appended.
When not to use it
If you just need a plain join with no per-item transformation and you're on a version with TEXTJOIN, use TEXTJOIN: =TEXTJOIN(", ", TRUE, B2:B20) is shorter and its TRUE flag skips blanks for free. REDUCE earns its place the moment you need to do something to each value first, not before.
