A Step-by-Step Guide to Using TEXTJOIN in Excel for Combining Cell Values

The TEXTJOIN function is an incredibly useful tool for concatenating or combining text from multiple cells in Excel. In this comprehensive guide, I’ll explain what TEXTJOIN does, when to use it, and walk through a detailed tutorial with examples to demonstrate how to apply it in practice.

After reading you’ll know exactly how to use TEXTJOIN to efficiently merge and output text strings from ranges of cells in your worksheets. Let’s get started!

What is TEXTJOIN?

TEXTJOIN allows you to combine text from multiple cells while inserting a delimiter between each value, Some key things to know

• Introduced in Excel 2016, replaces Concatenate
• Merges text from a range of cells into one output
• Adds a separator or delimiter like a comma between each value
• Skips blank cells if desired
• More flexible than Concatenation or & operator

You define the cells to merge, the delimiter to insert between values, and options to skip blanks and control spacing.

TEXTJOIN then assembles one combined string from all the cells, nicely formatted with your specified delimiters in between each item.

When to Use TEXTJOIN

TEXTJOIN shines anytime you need to combine values from many cells. Examples include:

• Combining first and last names from separate columns into one cell
• Merging items from a list into a comma-separated list
• Assembling address parts (street, city, zip) into a single cell
• Concatenating category and product name into an item description
• Building a list of tags or keywords from individual cells
• Formatting multi-value cells for mail merge and other uses

It provides more control over delimiter handling and blank cells than Concatenate. The key is specifying the delimiter so Excel knows how to separate each value.

TEXTJOIN Syntax and Arguments

Here is the syntax structure:

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

• delimiter – Required. The text string to insert between values. Usually a character like comma or space.

• ignore_empty – Optional. Logical value (TRUE/FALSE) to omit blank cells.

• text1 – Required. First cell reference or text string to join.

• [text2] – Optional. Additional cell references or text to include.

Next let’s walk through practical examples to see it in action.

TEXTJOIN Example 1 – Combining First and Last Names

Say you have a dataset of first and last names in separate columns like this:

To combine the first and last name columns with a space delimiter, use this formula:

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

Where A2 and B2 contain the first and last names.

This outputs:

John Smith

It inserts a space between the values and skips blanks with TRUE.

To combine the full name for the entire column, enter this array formula:

=TEXTJOIN(" ",TRUE,A2:A100,B2:B100)

When finished this will build a list of merged names like:

John SmithJane DoeBob Lee

And so on down the column.

TEXTJOIN Example 2 – Comma-Separated List

For another common use case, let’s look at merging a list of items into a comma-separated list.

![Textjoin comma example]

The formula in D2 is:

=TEXTJOIN(", ",TRUE,B2:B6) 

This combine the items in B2 through B6, inserting a comma and space between each cell value.

The result is perfectly formatted as:

apples, oranges, grapes, bananas, blueberries

Use this approach anytime you need a comma-delimited list from a column of items.

TEXTJOIN Example 3 – Concatenating Address

You can also use TEXTJOIN to combine address parts into a full mailing address.

Given columns for street, city, state, and zip like this:

![Textjoin address example]

Use this formula to merge them with commas, spaces and line feeds:

=TEXTJOIN(CHAR(10),TRUE,A2,", ",B2," ",C2,"  ",D2)

CHAR(10) inserts a line break. The end result:

123 Main Street,Anytown, CA 12345

TEXTJOIN makes formatting addresses, names, lists, and other combined values a breeze!

Tips for TEXTJOIN

Here are some additional pointers for mastering TEXTJOIN in Excel:

• Use CHAR(10) to add line breaks between merged values
• Nest a TRIM function inside to remove extra spaces
• Merge numerical and text values together
• Update the delimiter parameter to customize the separators
• Reference a cell containing the delimiter text instead of typing it
• Start the formula with =CONCATENATE( for compatibility with older Excel versions
• Use IF functions inside TEXTJOIN to only include certain values conditionally

Take a bit of time to experiment with the formula syntax and arguments so you get comfortable combining values in different ways.

Applies to Other Languages

Excel’s TEXTJOIN function works in nearly all languages. The formula remains the same but you simply change the delimiter.

For example, in German use:

=TEXTVERBINDEN(",", WAHR, A2:A100)

In French:

=TEXTE.CONCATÉNER(",", VRAI, A2:A100) 

Most languages support the equivalent of TEXTJOIN. Look for it on the formula tab.

TEXTJOIN provides a powerful, flexible way to combine text cells in Excel. Key takeaways:

• Merges text from multiple cells into one output
• Adds custom delimiters between each value
• Skips blank cells with optional argument
• Improved version of the Concatenate function

You can now use TEXTJOIN to easily format merged values like names, addresses, lists, tags, and more!