TEXTJOIN Function in Excel Tutorial

Excel Tutorial

Introduction

The TEXTJOIN function in Excel combines text from multiple cells or text values into a single text string using a separator such as a space, comma, hyphen, or slash.

It is useful for combining employee names, customer addresses, product details, department names, and business records. You can also control whether empty cells should be ignored or included.

TEXTJOIN Function Syntax

=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)

Arguments:

  • delimiter: The separator placed between the combined text values, such as a space, comma, or hyphen.
  • ignore_empty: Use TRUE to ignore empty cells and FALSE to include them.
  • text1: The first text value, cell, or range to combine.
  • text2: Optional additional text values, cells, or ranges.

Important Points:

  • TEXTJOIN combines multiple text values into one result.
  • You can use different separators.
  • TRUE ignores empty cells.
  • FALSE includes empty cells in the joining process.
  • TEXTJOIN can combine individual cells and complete ranges.
  • It is available in Microsoft 365, Excel 2019, and later versions.

Question 1: Combine First Name and Last Name

Problem Statement

An HR department stores employee first names and last names in separate columns. The department wants to display each employee’s full name in one cell.

First NameLast NameFull Name
AmitKumarAmit Kumar
PriyaSharmaPriya Sharma
RahulVermaRahul Verma
NehaGuptaNeha Gupta

Assume first names are in A2 and last names are in B2.

Excel Formula

=TEXTJOIN(" ",TRUE,A2,B2)

Expected Result: For Amit and Kumar, the result is Amit Kumar.

Explanation: The space inside the first argument is used as a separator between the first name and last name.

Student Task: Enter Rohit in A6 and Singh in B6. Combine both names with a space.

New Expected Result: Rohit Singh

Question 2: Combine Multiple Address Columns

Problem Statement

A company stores employee addresses in separate columns. The HR department wants to combine the address details into one cell, separated by commas.

House No.AreaCityPIN Code
A-66Sector 7New Delhi110077
B-25Sector 12Gurugram122001
C-18DwarkaNew Delhi110075
D-40RohiniNew Delhi110085

Assume address details are in A2.

Excel Formula

=TEXTJOIN(", ",TRUE,A2:D2)

Expected Result: For the first employee, the result is A-66, Sector 7, New Delhi, 110077.

Explanation: TEXTJOIN combines all four cells using a comma and space as separators.

Student Task: Add a new record in row 6:

House No.AreaCityPIN Code
E-15Sector 21Noida201301

Combine all four address details into one cell.

New Expected Result: E-15, Sector 21, Noida, 201301

Question 3: Combine Product Codes Using a Hyphen

Problem Statement

An inventory department stores product code components separately. It wants to create a complete product code using hyphens.

CategoryDepartmentProduct NumberProduct Code
LAPIT105LAP-IT-105
KEYHW205KEY-HW-205
MONDP310MON-DP-310
USBAC450USB-AC-450

Assume product details are in A2.

Excel Formula

=TEXTJOIN("-",TRUE,A2:C2)

Expected Result: For the first product, the result is LAP-IT-105.

Explanation: TEXTJOIN combines the three cells and places a hyphen between each value.

Student Task: Add a new record with MOU, WL, and 550. Create its complete product code.

New Expected Result: MOU-WL-550

Question 4: Combine Employee Names with Middle Names

Problem Statement

An HR department stores first names, middle names, and last names separately. Some employees do not have a middle name. The department wants to combine the available names without unnecessary extra spaces.

First NameMiddle NameLast Name
AmitRajKumar
PriyaSharma
RahulDevVerma
NehaGupta

Assume employee names are in A2.

Excel Formula

=TEXTJOIN(" ",TRUE,A2:C2)

Expected Result: For Priya, the result is Priya Sharma.

Explanation: The second argument is TRUE, so TEXTJOIN ignores the empty middle-name cell.

Student Task: Enter Rohit, leave the middle name empty, and enter Singh as the last name. Combine the available names.

New Expected Result: Rohit Singh

Question 5: Combine Subject Names into One Cell

Problem Statement

A training institute wants to display all subjects selected by a student in a single cell, separated by commas.

Subject 1Subject 2Subject 3Subject 4
ExcelSQLPower BIPython
HTMLCSSJavaScript
PHPMySQLWordPress
CC++JavaPython

Assume subject details are in A2.

Excel Formula

=TEXTJOIN(", ",TRUE,A2:D2)

Expected Result: For the first student, the result is Excel, SQL, Power BI, Python.

Explanation: TEXTJOIN combines all subject names using commas and spaces. Empty cells are ignored.

Student Task: Enter Excel, Power BI, Python, and leave the fourth subject cell empty. Combine the available subjects.

New Expected Result: Excel, Power BI, Python

Question 6: Combine Text with Empty Cells Included

Problem Statement

A company wants to combine product details while keeping the positions of empty cells visible. In this example, an empty cell should create an additional separator.

BrandModelColourProduct Code
DellInspironBlackD101
HPPavilionH202
LenovoThinkPadGreyL303
AcerAspireSilverA404

Assume product details are in A2.

Excel Formula

=TEXTJOIN("-",FALSE,A2:D2)

Expected Result: For the HP product, the result is HP-Pavilion–H202.

Explanation: The second argument is FALSE, so empty cells are not ignored. The empty colour cell creates two consecutive hyphens.

Student Task: Enter Samsung, Galaxy, leave the colour cell empty, and enter S505 as the product code. Combine all four cells.

New Expected Result: Samsung-Galaxy–S505

Question 7: Combine Department Names Using a Slash

Problem Statement

A company wants to display the departments involved in a project in one cell, separated by a slash.

Department 1Department 2Department 3Project Teams
HRFinanceITHR / Finance / IT
SalesMarketingOperationsSales / Marketing / Operations
ITSupportIT / Support
AccountsAdminLegalAccounts / Admin / Legal

Assume department names are in A2.

Excel Formula

=TEXTJOIN(" / ",TRUE,A2:C2)

Expected Result: For the first project, the result is HR / Finance / IT.

Explanation: TEXTJOIN combines the department names using / as the separator and ignores empty cells.

Student Task: Enter Sales, HR, and Training in A6. Combine all three department names.

New Expected Result: Sales / HR / Training

Student Tasks

Solve these exercises independently using only the TEXTJOIN function.

Task 1: Combine Employee Names

First NameLast NameFull Name
AnkitVerma?
SimranKaur?
MohitSharma?
PoojaSingh?

Your Task: Combine first and last names using a space as the separator.

Expected Results: Ankit Verma, Simran Kaur, Mohit Sharma, Pooja Singh

Task 2: Combine Product Codes

CategoryDepartmentNumberProduct Code
LAPIT501?
KEYHW502?
MONDP503?
USBAC504?

Your Task: Combine all three values using a hyphen.

Expected Results: LAP-IT-501, KEY-HW-502, MON-DP-503, USB-AC-504

Task 3: Combine Course Names

Course 1Course 2Course 3Combined Courses
ExcelSQLPower BI?
PythonJavaPHP?
HTMLCSSJavaScript?
CC++Java?

Your Task: Combine all course names using a comma and space.

Expected Results:

  • Excel, SQL, Power BI
  • Python, Java, PHP
  • HTML, CSS, JavaScript
  • C, C++, Java

Final Student Challenge: Build an Employee Information Summary

Problem Statement

An HR department stores employee details in separate columns. The department wants to create a single summary containing each employee’s name, department, city, and employee ID.

Given Data

First NameLast NameDepartmentCityEmployee ID
AmitKumarHRDelhiEMP-101
PriyaSharmaITGurugramEMP-102
RahulVermaFinanceNoidaEMP-103
NehaGuptaMarketingDelhiEMP-104
RohitSinghOperationsFaridabadEMP-105

Your Task

Add a column named Employee Summary.

Combine all five details using a separator of |.

Excel Formula

=TEXTJOIN(" | ",TRUE,A2:E2)

Copy the formula down for all five employees.

Expected Result

EmployeeEmployee Summary
Amit KumarAmit | Kumar | HR | Delhi | EMP-101
Priya SharmaPriya | Sharma | IT | Gurugram | EMP-102
Rahul VermaRahul | Verma | Finance | Noida | EMP-103
Neha GuptaNeha | Gupta | Marketing | Delhi | EMP-104
Rohit SinghRohit | Singh | Operations | Faridabad | EMP-105

Key Takeaways

  • TEXTJOIN combines multiple text values into one cell.
  • Its syntax is =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).
  • You can use spaces, commas, hyphens, and other separators.
  • TRUE ignores empty cells.
  • FALSE includes empty cells in the joining process.
  • TEXTJOIN can combine individual cells or complete ranges.
  • It is useful for employee names, addresses, product codes, and business summaries.

FAQs

1. What is the TEXTJOIN function in Excel?

TEXTJOIN combines text from multiple cells or values into one text string using a specified separator.

2. What is the syntax of the TEXTJOIN function?

The syntax is =TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...).

3. What is the use of the delimiter argument?

The delimiter specifies what should appear between combined values. Examples include a space, comma, hyphen, or slash.

4. What is the difference between TRUE and FALSE in TEXTJOIN?

TRUE ignores empty cells, while FALSE includes empty cells in the joining process.

5. Can TEXTJOIN in Excel combine multiple columns?

Yes. TEXTJOIN can combine values from multiple columns by selecting a range such as A2:D2.

6. Can TEXTJOIN in Excel combine employee first and last names?

Yes. You can use a space as the delimiter to combine first names and last names into one full name.

7. Can TEXTJOIN combine values with a comma?

Yes. Use ", " as the delimiter to separate combined values with commas and spaces.

8. What happens when an empty cell is included in TEXTJOIN?

If ignore_empty is TRUE, empty cells are skipped. If it is FALSE, empty cells are included in the joining process, which can create consecutive separators.

9. Which Excel versions support TEXTJOIN?

TEXTJOIN is available in Microsoft 365, Excel 2019, and later versions. It is not available in many older Excel versions.