When organizing data in Microsoft Excel, you often need to combine text from multiple different cells into one single cell. For example, you might have a column for “First Name” and a column for “Last Name,” and you want to merge them into a single “Full Name” column. For years, Excel users relied on the CONCATENATE function (or the ampersand & symbol) to achieve this.
However, CONCATENATE is notoriously tedious if you have a long list of items, because you must manually insert commas and spaces between every single cell reference. Furthermore, if one of those cells is completely empty, CONCATENATE will leave an ugly double-comma in your final result. To solve these exact problems, Microsoft introduced the incredibly powerful TEXTJOIN function.
Understanding the TEXTJOIN Syntax
The TEXTJOIN function is specifically designed to merge large ranges of cells while automatically inserting a delimiter (like a comma, space, or dash) between each item. More importantly, it can intelligently ignore empty cells.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], ...)
- delimiter: The character (or string of characters) you want to insert between each piece of text. It must be enclosed in double quotes (e.g.,
", "for a comma and a space). - ignore_empty: A logical value. Type
TRUEto ignore empty cells in your range, orFALSEto include them. - text1: The range of cells you want to combine.
Example: Merging a Range of Data
Imagine you have an address spread across five columns (A through E): Street, City, State, Zip, and Country. You want to combine them all into a single, beautifully formatted address in Column F.
Using the old method, your formula would look like this nightmare:
=A2 & ", " & B2 & ", " & C2 & ", " & D2 & ", " & E2
With TEXTJOIN, the process is effortless. Click in an empty cell and type:
=TEXTJOIN(", ", TRUE, A2:E2)
How Excel processes this: It looks at the range A2 through E2. It combines the text from every cell in that range, automatically separating them with a comma and a space (", "). If one of the columns (like “State”) happens to be blank, the TRUE argument ensures Excel skips it, preventing a double-comma from appearing in the final address.
Advanced Uses for TEXTJOIN
The true power of TEXTJOIN is unleashed when you combine it with arrays or conditional logic.
For example, if you want to combine cells with a simple line break instead of a comma, you can use the CHAR(10) function as your delimiter. This is perfect for formatting mailing labels where the Street, City, and Zip code need to be on separate lines within the same cell.
=TEXTJOIN(CHAR(10), TRUE, A2:E2)
Note: If you use a line break delimiter, you must ensure that “Wrap Text” is enabled on the cell, otherwise, the text will appear in a single, unformatted block.