The TEXTJOIN function in Excel
TEXTJOIN combines text from several cells into one, with a separator of your choice.
What TEXTJOIN does
You give it the separator, then say whether to skip empty cells, then the cells or ranges to join. Because it accepts whole ranges, a single formula can turn a column of names into one tidy list. Setting the second argument to TRUE leaves out empty cells so you never get a double separator.
Syntax
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
- delimiter
- The text placed between the items, in quotes, such as ", ".
- ignore_empty
- TRUE to skip empty cells, FALSE to include them.
- text1, text2, ...
- The cells or ranges to join.
Worked example
| A | B | |
|---|---|---|
| 1 | Team | |
| 2 | Ana | |
| 3 | Ben | |
| 4 | Cy | |
| 5 | Di | |
| 6 | Eli | |
| 7 | Everyone | Ana, Ben, Cy, Di, Eli |
The range A2:A6 is joined into one cell, with a comma and a space between each name.
Now you try
A fresh exercise every time. Press Another exercise as often as you like.
Where you will use it
Data cleanup
Build one list of values from a column.
Sales & Ops
List all the products on an order in one cell.
HR
Build full names from first, middle and last name columns.
Common mistakes
- Putting the separator in without quotes. The delimiter is text, so it needs quotation marks.
- Forgetting TRUE for ignore_empty. Empty cells then leave doubled commas in the result.
- Expecting the result to be a list of separate cells. TEXTJOIN returns one piece of text.
The TEXTJOIN course
- 1Join with a commaFree practice belowPractise
- 2Skip the blanksMembers lesson
- 3Join with a dashMembers lesson
- 4Build full names across columnsMembers lesson
- 5Join two listsMembers lesson
Common questions
How is TEXTJOIN different from CONCAT?
TEXTJOIN adds a separator between items and can skip empty cells. CONCAT just sticks the text together.
Does it work in older versions of Excel?
TEXTJOIN was added in Excel 2019 and Microsoft 365. Earlier versions do not have it.
Related functions
Unlock every lesson
Every function has a free lesson. Members get all the lessons, fresh exercises every time and the weekly leaderboard. £7 a month or £49 a year, and you can cancel at any time.