The TEXTJOIN function is used in Excel to merge text from multiple cells into one, and supports specifying delimiters and ignoring empty cells. 1. Its basic structure is =TEXTJOIN (delimiter, whether to ignore null values, text 1, [text 2], ...); 2. It can merge single cells, ranges or even multiple ranges; 3. It is often used to merge addresses, tags, dynamic messages, URL paths, etc.; 4. It is recommended to set the null value to TRUE when using it to avoid unnecessary separators; 5. It is suitable for Excel 2016 and above. Older versions need to use the combination of CONCAT and SUBSTITUTE to achieve similar effects.

The TEXTJOIN function in Excel is super handy when you want to combine text from multiple cells into one, with a separator like a comma, space, or even a custom symbol. Unlike the older CONCATENATE or CONCAT functions, TEXTJOIN lets you specify a delimiter and skip any empty cells—no extra steps needed.

Basic structure of TEXTJOIN

TEXTJOIN follows a pretty simple setup. The syntax is:

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

• Delimiter is what shows up between each piece of text—like a space " " , a comma ", " , or anything you want.
• Ignore_empty is a TRUE/FALSE choice. If TRUE, Excel skips over blank cells.
• Text1, Text2, etc. can be cell references, ranges, or direct text strings.

For example, if you have first name in A1 and last name in B1, and you want them joined with a space, you'd use:

=TEXTJOIN(" ", TRUE, A1, B1)

That gives you a full name like "John Doe" without extra spaces if one of the cells is blank.

Combine multiple cells or ranges

TEXTJOIN isn't just for two cells—it can handle a whole list or range. Let's say you have a row of values in cells A1 through D1, and some are blank. You want to join them all with commas and skip the blanks.

You'd write:

=TEXTJOIN(", ", TRUE, A1:D1)

That's way easier than doing it manually or using CONCAT with SUBSTITUTE to clean up extra commas. This method works great for things like merging tags, notes, or lists into one cell without messy formatting.

Also, you're not limited to single rows or columns. You can include multiple ranges or mix ranges with individual cells. Just separate them in the formula like this:

=TEXTJOIN(" | ", TRUE, A1:A5, B1:B5)

This joins both ranges with " | " as the separator.

Common use cases and tips

Here are a few real-world ways people use TEXTJOIN:

• Combine address parts (street, city, state, zip) into one clean field.
• Merge tags or categories from a checklist or survey.
• Create custom messages or labels from dynamic data.
• Build URLs or file paths by plugging in variables.

A couple of quick tips:

• If you want no separator at all (just stick text together), use an empty string "" as the delimiter.
• Always set ignore_empty to TRUE unless you want separators showing up where there's no data.
• TEXTJOIN works in Excel 2016 and later—including Excel Online and Excel for Mac (as long as your version supports it).

If you're using an older version of Excel that doesn't support TEXTJOIN, you might have to fall back on a combo of CONCAT and SUBSTITUTE to get similar results, but it's a lot more work.

So yeah, TEXTJOIN is a solid upgrade over older text-joining tools in Excel. It's flexible, skips blanks by default, and keeps your formulas cleaner. Once you start using it, you'll wonder how you got by without it.

Basically that's it.

The above is the detailed content of How to use the TEXTJOIN function in Excel. For more information, please follow other related articles on the PHP Chinese website!