Excel for Microsoft 365: Concatenate a Range by Using TEXTJOIN

October 21, 2020 - by Bill Jelen

Concatenate a Range by Using TEXTJOIN. Photo Credit: David Hurley at Unsplash.com

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.

To get a list of all people in A10:A18 with commas in between, use =TEXTJOIN(", ",TRUE,$A$10:$A$18). To limit to only the people where RSVP=Yes in column B, use: =TEXTJOIN(", ",TRUE,IF(B10:B18="Yes",A10:A18,"")) with Ctrl+Shift+Enter.

Title Photo: David Hurley at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.