Excel for Microsoft 365: Concatenate a Range by Using TEXTJOIN
October 21, 2020 - by Bill Jelen
![Excel for Microsoft 365: Concatenate a Range by Using TEXTJOIN Concatenate a Range by Using TEXTJOIN. Photo Credit: David Hurley at Unsplash.com](/img/excel-tips/2020/10/excel-2020-concatenate-a-range-by-using-textjoin.jpg)
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.](/img/content/2020/10/LXFig-235.png)
Title Photo: David Hurley at Unsplash.com
This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.