The Formula Gym
XP 0
Streak 0

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

AB
1Team
2Ana
3Ben
4Cy
5Di
6Eli
7EveryoneAna, Ben, Cy, Di, Eli
B7 =TEXTJOIN(", ",TRUE,A2:A6)

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

  1. 1
    Join with a commaFree practice below
    Practise
  2. 2
    Skip the blanksMembers lesson
  3. 3
    Join with a dashMembers lesson
  4. 4
    Build full names across columnsMembers lesson
  5. 5
    Join 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.

See membership