The TEXTJOIN formula in Excel combines text from multiple rows or columns into a single cell, separating each item with a specified delimiter. Unlike older methods, it can ignore empty cells and handle arrays directly, making it ideal for aggregating lists, names, or categories into one concise string.
Understanding the TEXTJOIN Syntax
To effectively merge data across rows, you must first understand the structure of theTEXTJOIN function. Introduced in Excel 2019 and Microsoft 365, this function is superior to concatenation operators because it offers built-in control over delimiters and blank values. The syntax requires three distinct arguments, each serving a specific purpose in the aggregation process.
The first argument is delimiter. This is the character or string that will appear between each joined item. Common choices include commas, spaces, hyphens, or line breaks. For example, if you want a list of items separated by commas, you would use "," as your delimiter. If you leave this as an empty string "", the items will be glued together without any separation.
The second argument is ignore_empty. This is a logical value (TRUE or FALSE) that determines how the function handles blank cells within your selected range. Setting this to TRUE ensures that empty cells are skipped entirely, preventing double delimiters or trailing punctuation. This is particularly useful when dealing with incomplete datasets where some rows may not yet have data entered.
The third argument is text1, followed by optional text2, etc. You can input individual cells, ranges, or even arrays here. For merging multiple rows, you typically select a vertical range such as A2:A10. The function processes these inputs sequentially, applying the delimiter between each non-empty value according to the rules set by the first two arguments.
Basic Example: Merging Names from Rows
Consider a scenario where you have a list of employee names spread across several rows in column A, starting from cell A2 down to A10. Your goal is to combine all these names into a single cell in B2, separated by commas. Assuming you want to skip any blank cells in case some employees are missing data, the formula would look like this:=TEXTJOIN(", ", TRUE, A2:A10)", " (a comma followed by a space), which creates a readable list. The TRUE parameter ensures that if A5 is empty, it does not insert an extra comma. The result might appear as "Alice, Bob, Charlie, Dave". This approach is significantly cleaner than using & operators, which require manual handling of every separator and do not inherently skip blanks.
For more complex text manipulation, such as subtracting specific substrings before joining, you might need to reference related techniques found in our guide on Excel: Subtract Cell Based Text from Another Cell. Combining these skills allows for precise data cleaning before aggregation.
Handling Line Breaks and Special Delimiters
Sometimes, a simple comma-separated list is not sufficient. You may need to format the output as a vertical list within a single cell, especially if the destination cell has wrapped text enabled. To achieve this, you can use the carriage return character as your delimiter. In Excel, this is represented byCHAR(10).
The formula for creating a vertical list would be:
=TEXTJOIN(CHAR(10), TRUE, A2:A10)Common Pitfalls and Best Practices
WhileTEXTJOIN is powerful, there are common mistakes users encounter. First, ensure your Excel version supports the function. It is unavailable in Excel 2016 and earlier versions. If you receive a #NAME? error, check your version compatibility. Second, avoid mixing different data types without conversion. If your range contains numbers, they will be converted to text automatically, but leading zeros may be lost. Use TEXT() formatting inside the formula if preserving number formats is critical.
Another best practice is to test with small ranges first. Verify that the ignore_empty flag behaves as expected by intentionally leaving a few cells blank. Additionally, be mindful of memory usage with very large ranges. While Excel handles most standard datasets well, extremely large arrays may slow down calculation times. In such cases, breaking the data into smaller chunks or using Power Query might be more efficient.
For scenarios where you need to add specific punctuation like commas to existing strings manually, refer to our step-by-step guide on Adding Commas to a Text String in Excel: A Step-by-Step Guide for alternative methods.
Frequently Asked Questions
What happens if I set ignore_empty to FALSE?
Settingignore_empty to FALSE means that blank cells will still trigger the insertion of a delimiter. This results in consecutive delimiters (e.g., "Alice,,Bob") or trailing delimiters if the last cell is empty, which often looks messy and requires additional cleanup.
Can TEXTJOIN work with non-contiguous ranges?
No,TEXTJOIN does not support direct non-contiguous ranges like (A2:A5, C2:C5) in its native syntax. You must either combine them into a contiguous block using helper columns or nest functions like CONCATENATE within it, though nesting limits flexibility.
Does TEXTJOIN preserve the original data type?
All outputs fromTEXTJOIN are treated as text strings. Numbers are converted to their textual representation, meaning mathematical operations cannot be performed on the result without re-parsing. Leading zeros in numeric codes may also be dropped unless formatted explicitly.
How do I add a space after the delimiter?
Simply include the space within the delimiter argument itself. For example, use" - " instead of just "-". The entire string provided in the first argument is used verbatim between each joined item.