Concatenate a Range by Using TEXTJOIN - KING OF EXCEL


KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Thursday, January 2, 2020

Concatenate a Range by Using TEXTJOIN

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.
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 installed directly on the latest phone here :

No comments:

Post a Comment