Excel TEXTJOIN function

Summary

The Excel TEXTJOIN function concatenates (joins) text strings using a specified delimiter. It is particularly useful when combining text from a range of cells, and you need to include a specific separator between each text value.
Syntax
				
					=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
				
			
  • delimiter: The string to use as a separator. Required.
  • ignore_empty: TRUE to ignore empty cells, FALSE to include them. Required.
  • text1: The first item to join. Required.
  • text2, …: [Optional] Additional text items to join.
Return value
A single text string made up of the joined text items, separated by the specified delimiter.

How to use

TEXTJOIN is versatile for combining text strings from multiple cells, especially when dealing with data sets where some cells might be empty. It’s often used in data compilation and presentation tasks.

Examples

Simple TEXTJOIN
Joining Names with a Comma Separator: To join a list of names with a comma:
				
					=TEXTJOIN(", ", TRUE, A1:A5)
				
			
If A1:A5 contains names, it returns them as “Name1, Name2, …, Name5”.
TEXTJOIN Ignoring Empty Cells
Concatenating Non-Empty Cells Only: Joining cells while ignoring any empty ones:
				
					=TEXTJOIN("; ", TRUE, B1:B10)
				
			
Concatenates non-empty cells in B1:B10, separated by “; “.
TEXTJOIN for Data Lists
Creating a Bullet List from Cells: Using TEXTJOIN to compile a bulleted list:
				
					=TEXTJOIN(CHAR(10), TRUE, "• " & C1:C10)
				
			
Combines C1:C10 into a bulleted list, with each item on a new line.
TEXTJOIN with Various Data Types
Combining Text and Numbers: Joining text strings and numeric values:
				
					=TEXTJOIN(" - ", FALSE, D1, E1, F1)
				
			
If D1 is text, E1 is a date, and F1 is a number, it joins them with ” – “.
TEXTJOIN for Dynamic Ranges
Joining a Dynamic Range of Cells: Concatenating a range that may change in size:
				
					=TEXTJOIN(", ", TRUE, INDIRECT("G1:G" & COUNTA(G:G)))
				
			
Joins cells in column G that are not empty, separated by “, “.

Additional Notes

  • TEXTJOIN is particularly useful in scenarios where CONCATENATE would be cumbersome, especially with ranges.
  • The delimiter can be any string, including special characters and line breaks.
  • TEXTJOIN can simplify data preparation tasks, such as creating lists or labels from spreadsheet data.

Related Functions

Excel CONCATENATE function

The Excel CONCATENATE function combines text items into one string, useful for creating labels or combining data (Deprecated in newer versions).

Excel CONCAT function

The Excel CONCAT function merges multiple text strings into one, perfect for data concatenation without using the ampersand (&).

Content Navigation