When you are compiling a massive email distribution list in Microsoft Excel, you often have a column containing 500 individual email addresses that you need to merge together into a single, massive string of text separated by semicolons. While you could technically use the old CONCATENATE function (or the & symbol) to glue them together, you would be forced to manually type hundreds of commas into the formula. To instantly merge a massive range of cells using a custom separator, you must use the incredibly powerful TEXTJOIN function.
How the TEXTJOIN Function Works
Introduced in Excel 2019 (and fully supported in Office 365), the TEXTJOIN function is specifically designed to concatenate arrays of data while automatically injecting a predefined “delimiter” (like a comma, a space, or a hyphen) between every single cell.
The syntax requires three arguments: =TEXTJOIN(delimiter, ignore_empty, range)
- delimiter: The specific character you want Excel to insert between the text strings (must be enclosed in quotation marks).
- ignore_empty: A True/False switch. If you type TRUE, Excel will completely ignore any blank cells in your range, preventing awkward double-commas from appearing in your final string.
- range: The highlighted block of cells you want to merge.
Merging Email Addresses with a Semicolon
Imagine you have a list of employee email addresses running vertically from cell A2 down to A50. You need to combine all 49 emails into a single cell, separated by semicolons (;) so you can paste the block directly into Outlook.
Click into an empty cell (e.g., C2) and type the following formula:
=TEXTJOIN(";", TRUE, A2:A50)Excel will instantly read all 49 cells, strip out any blank rows, glue the text together, and seamlessly inject a semicolon between every single address. The result is a perfect, continuous string of text ready for copying.
Using Spaces as Delimiters
The TEXTJOIN function is also the absolute best way to reconstruct human names. If cell A2 contains “John”, cell B2 contains “Fitzgerald”, and cell C2 contains “Kennedy”, you want to merge them with standard spaces.
Type the following formula:
=TEXTJOIN(" ", TRUE, A2:C2)By typing a physical spacebar hit between the quotation marks, Excel knows to insert a standard gap between the words, perfectly outputting John Fitzgerald Kennedy. If a person in the database does not have a middle name (meaning cell B2 is blank), the TRUE argument mathematically kicks in, completely ignoring the blank cell and ensuring there are no awkward double-spaces between the first and last name.