Excel for Microsoft 365: Concatenate a Range by Using TEXTJOIN
October 21, 2020 - by Bill Jelen

My favorite new calculation function in Office 365 is TEXTJOIN. What if you needed to concatenate all of the names in A1:A10? The formula =A1&A2&A3&A4&A5&A6&A7&A8&A9&A10
would jam everyone together like AndyBobCaroleDaleEdFloGloriaHelenIkeJill. By using TEXTJOIN, you can specify a delimiter such as ", ". The second argument lets you specify if blank cells should be ignored. =TEXTJOIN(", ",True,A1:A10)
would produce Andy, Bob, Carole, and so on.
Tip
TEXTJOIN works with arrays. The array formula shown in A7 uses a criterion to find only the people who answered Yes. Make sure to hold down Ctrl + Shift while pressing Enter to accept this formula. The alternate formula shown in A8 uses the Dynamic Array FILTER function and does not require Ctrl+Shift+Enter.

Title Photo: David Hurley at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.