excelisfun - Excel Magic Trick 1281: TEXTJOIN & CONCAT New Excel 2016 functions (14 Examples of Joining Items)

Short Summary

Main recommendation: Use the new TEXTJOIN and CONCAT functions (available in Excel 2016 or later, or via the Office Insider program as shown in the video) instead of the older ampersand (&) and CONCATENATE approach for joining text. TEXTJOIN lets you set a delimiter and skip empty cells, while CONCAT joins a range or list of items without requiring a delimiter. The video demonstrates many examples of joining items with these functions.

Key Points

• CONCATENATE is an older function that the compatibility warning (yellow triangle) signals should be replaced by CONCAT.
• Unlike CONCATENATE, CONCAT can accept a whole range or list of text, not just individual items.
• TEXTJOIN automatically places a delimiter between items and, with "ignore empty cells" set to TRUE, skips blanks, removing the need for nested IF checks.
• TEXTJOIN can use multiple delimiters at once through array syntax, and the delimiters can come from cells rather than being typed in.

The 14 examples of joining items demonstrated in the video:

1. Joining first, middle, and last name with a space using the ampersand method (the old way).
2. Joining first, middle, and last name with CONCATENATE.
3. Joining first, middle, and last name with CONCAT, one item per argument.
4. Joining first, middle, and last name with TEXTJOIN using a space delimiter.
5. Joining names with blanks in the middle name column, using TEXTJOIN with ignore empty cells set to TRUE.
6. Joining a range into a product code with no delimiter, using CONCAT.
7. Joining a supplier value with a range using a delimiter in CONCAT, initially typed as a space then corrected to a dash.
8. Joining items with a dash and a slash as different delimiters, using the ampersand method.
9. Joining items with two different delimiters (dash and slash) in TEXTJOIN using array syntax.
10. Using TEXTJOIN with delimiters stored in cells off to the side, with the cell reference locked using F4.
11. Joining plywood ply count, size, and unit with a space and comma delimiter in TEXTJOIN.
12. Joining a column of emails into one semicolon-separated string for pasting into an email.
13. Using TEXTJOIN to build the email list directly, then copying the evaluated result.
14. Creating a single comma-delimited column from first name, last name, email, and phone.

Details Worth Noting

• Concrete instructions:
  • To use TEXTJOIN, type =TEXTJOIN(delimiter, TRUE, range), where the delimiter is in double quotes (for example " " for a space) and TRUE ignores empty cells.
  • For multiple delimiters, type the delimiter list in array syntax with curly brackets, for example {"-","/"}, then the ignore-empty value and the range.
  • To open the function's Function Arguments dialog box, press Shift+F3; typing a space and pressing Tab puts the double quotes in automatically.
  • To paste a column of emails separated by semicolons, build the TEXTJOIN formula, press F9 to evaluate it, then Ctrl+Z to undo the evaluation and press Enter; copy the result from the cell and paste it into the email editor.
  • To finish a formula across a column, press Ctrl+Enter after entering it, then double-click the fill handle to fill it down.
• Requirement: TEXTJOIN and CONCAT require Excel 2016 or later; the speaker notes that in 2016 they were available through the Insider program.