Concatenate a Range by Using TEXTJOIN

January 02, 2020 Mr King Excel Tips,

Concatenate a Range by Using TEXTJOIN

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.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1