The TEXTJOIN function
Joining cells into one, with a separator between each: first and last name, an address, a customer's list of orders.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
In a French Excel: =JOINDRE.TEXTE(délimiteur; ignorer_vide; texte1; [texte2]; …).
Arguments
- delimiter: the separator, in quotes:
" ",", ". - ignore_empty: TRUE to skip empty cells.
- text1…: the cells or ranges to join.
An example
=TEXTJOIN(" ",TRUE,A2:C2)
First name, last name and city separated by a space; an empty cell leaves no double space.
Pitfalls
- Missing from Excel 2016 and earlier: CONCATENATE or
&, with no automatic separator. - Dates join as numbers (45567): pass them through TEXT.
Going further
- TEXT.
- Split or merge columns: join columns across a whole file.
Frequently asked questions
- What is the difference with CONCATENATE?
- CONCATENATE takes no separator and no range: every cell has to be named and the spaces added by hand.