Ravindra BagaleCourses & study guides Track your progress

Excel TEXTJOIN Function

By · Last updated:

Chala mitrano — today TEXTJOIN. Many cells, one sentence. Put a comma or pipe between names, and skip the blanks so the list stays clean.

Fictional sample data for learning. HR onboarding lists (Pune / London). Mixed Indian and foreign names. Fictional.

Purpose

Combine text from many cells into one string, with a delimiter you choose, and an option to skip blank cells.

Return value

One text string. Numbers in the range are converted to text in the result.

Syntax

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Arguments

ArgumentRequired?Description
delimiterRequiredCharacter(s) placed between pieces (comma, space, " | ", CHAR(10) for new line, etc.).
ignore_emptyRequiredTRUE to skip blanks; FALSE to keep empty slots (extra delimiters).
text1RequiredFirst text, cell, or range.
text2, …OptionalMore text values or ranges (up to Excel’s argument limit).

Version

Excel 2019, Excel 2021, Microsoft 365. Not in Excel 2016 and older. LibreOffice Calc: TEXTJOIN is available in recent versions.

How to use — Why → What → How

Why: CONCAT and & become messy for long ranges and blank cells. Teams need one clean address line or attendee list.

What: TEXTJOIN inserts your delimiter between each value and can ignore empty cells in a range.

How — numbered steps:

  1. Select the output cell.
  2. Type =TEXTJOIN(", ", TRUE, then select the range of names or parts.
  3. Close with ) and press Enter.
  4. Adjust the delimiter (space, comma+space, " | ").
  5. If you use CHAR(10), enable Wrap Text on the result cell.

Ravindra Bagale's Tip

Friends, set ignore_empty to TRUE for almost every list. FALSE leaves ugly double commas when a middle name is blank.

Ravindra Bagale's Tip

Need a line break inside one cell? Use CHAR(10) as the delimiter and turn on Wrap Text.

Download practice file: practice-textjoin.xlsx (includes sheet About this data, practice sheets, and Answers).

Worked examples

1) Full name from three cells

First, middle, last — middle may be blank.

FirstMiddleLast
AshaPatil
JamesR.Cole
Formula: =TEXTJOIN(" ", TRUE, A2:C2)

Result: Asha Patil

What it means: Blank middle is skipped, so there is no double space.

2) Comma-separated city list

Join unique cities for a footer.

City
Pune
London
Dubai
Formula: =TEXTJOIN(", ", TRUE, A2:A4)

Result: Pune, London, Dubai

What it means: Comma+space sits between each city.

3) Keep empty slots (ignore_empty FALSE)

Show where a phone extension is missing.

DeptExt
HR104
IT
Formula: =TEXTJOIN("-", FALSE, A3:B3)

Result: IT-

What it means: FALSE keeps the empty extension, so the trailing hyphen remains.

4) Address line

Flat, street, city for an offer letter.

FlatStreetCity
12-BFC RoadPune
Formula: =TEXTJOIN(", ", TRUE, A2:C2)

Result: 12-B, FC Road, Pune

What it means: One readable address string for mail merge.

5) Pipe-separated skills

CV skills row for Anita Desai.

S1S2S3
ExcelPower BISQL
Formula: =TEXTJOIN(" | ", TRUE, A2:C2)

Result: Excel | Power BI | SQL

What it means: A clear delimiter helps humans scan the list.

Common errors and fixes

#VALUE! — Delimiter is wrong type, or the result exceeds Excel’s cell character limit (~32,767). Fix: Shorten the range or split across cells.
Double commas / spaces — ignore_empty is FALSE and blanks exist. Fix: Set ignore_empty to TRUE.
Numbers look odd — Leading zeros in text IDs may be lost if stored as numbers. Fix: Store IDs as text before joining.
Name not found in older Excel — File opened in Excel 2016 or earlier. Fix: Use CONCAT or a helper column with & on old builds.

Notes and tips

  • TEXTJOIN can accept full ranges — you do not need one argument per cell.
  • Delimiter can be empty "" to smash pieces together with no gap.
  • Combine with UNIQUE or FILTER in Microsoft 365 for distinct joined lists.

CONCAT, TEXT, TEXTSPLIT, TRIM, SUBSTITUTE

Practice tasks (10)

  1. Task 1. Join A2:C2 with a space and skip blanks (Asha Patil row).
    Hint: delimiter " ", ignore_empty TRUE.
  2. Task 2. Join the City list with comma+space.
    Hint: select the whole city column range.
  3. Task 3. Recreate the IT- result using ignore_empty FALSE.
    Hint: second argument is FALSE.
  4. Task 4. Build the address line for flat 12-B.
    Hint: delimiter ", ".
  5. Task 5. Join skills with " | ".
    Hint: include spaces around the pipe if you want matching text.
  6. Task 6. Join First and Last only (two arguments after ignore_empty).
    Hint: you can pass single cells, not only ranges.
  7. Task 7. What changes if ignore_empty is FALSE on Asha’s row?
    Hint: look at the blank middle name.
  8. Task 8. Join three phone parts with "-" and skip blanks.
    Hint: same pattern as names.
  9. Task 9. Write one sentence on when to prefer TEXTJOIN over CONCAT.
    Hint: think about blanks and delimiters.
  10. Task 10. Bonus: use CHAR(10) between City and Country and enable Wrap Text.
    Hint: delimiter is CHAR(10).

Quick quiz

Q1. ignore_empty TRUE means blank cells are skipped.
Q2. TEXTJOIN is available in Excel 2013 by default.
Q3. A delimiter of CHAR(10) creates:
Q4. Which argument comes first?

Answers: Q1=True, Q2=False, Q3=A line break, Q4=delimiter (check after you try).

Revision card

Standalone card (no tip or photo strip). QR opens this lesson. Download the PNG and revise offline.

Revision card: TEXTJOIN
TEXTJOIN revision card