You need to combine text from multiple cells into one cell, but you want each piece on a new line. Using the ampersand or CONCAT function results in a messy, unreadable string. The TEXTJOIN function is designed for this exact task, offering a modern way to merge text with custom separators. This article explains how to use TEXTJOIN with a line break character to format your combined text cleanly.
Key Takeaways: Using TEXTJOIN for Line Breaks
- TEXTJOIN function with CHAR(10): Combines text from a range and inserts a line break between each item for clean vertical lists.
- Wrap Text formatting: Makes the line break character visible in the cell by enabling multi-line display.
- Ignore empty cells: The TEXTJOIN function’s second argument lets you skip blanks automatically, preventing extra line breaks.
What the TEXTJOIN Function Does
The TEXTJOIN function is a powerful text-combining tool introduced in Excel 2016 and available in Microsoft 365. Its primary purpose is to join text from multiple ranges or strings, using a delimiter you specify. Unlike the older CONCATENATE function, TEXTJOIN can handle entire ranges of cells and includes a built-in option to ignore empty cells. This makes it ideal for creating lists, addresses, or reports from dynamic data where some cells might be blank.
To create a line break within the result, you use a specific character code as the delimiter. In Windows, the line feed character is represented by CHAR(10). When you use CHAR(10) as the delimiter in TEXTJOIN, it tells Excel to insert a line break between each joined text item. However, for the break to appear visually in the cell, you must also apply the Wrap Text format to that cell. Without this formatting, Excel will display a small square or other placeholder instead of moving text to a new line.
Steps to Combine Text with Line Breaks
Follow these steps to create a vertical list or combined text with line breaks in a single cell.
- Structure your data
Ensure the text you want to combine is in a contiguous range. For example, have first name, last name, and department in cells A2, B2, and C2 respectively. - Select the result cell
Click on the cell where you want the combined text with line breaks to appear. - Enter the TEXTJOIN formula
Type the following formula, adjusting the range and cell references to match your data:=TEXTJOIN(CHAR(10), TRUE, A2, B2, C2). The CHAR(10) argument is the line break delimiter. The TRUE argument tells Excel to ignore any empty cells in the range. - Press Enter
After typing the formula, press Enter. The cell will display the combined text, but the line breaks may not be visible yet. - Apply Wrap Text formatting
With the formula cell selected, go to the Home tab on the ribbon. In the Alignment group, click the Wrap Text button. The cell height will adjust, and the text will appear on multiple lines. - Adjust the row height
If the text is cut off, double-click the bottom border of the row header to auto-fit the height, or manually drag it to show all lines.
Using TEXTJOIN with a Cell Range
You can also use a single range reference instead of individual cells. This method is efficient for joining a column of items into a list.
- Enter the range-based formula
In your result cell, type:=TEXTJOIN(CHAR(10), TRUE, A2:A10). This will join all values from cells A2 through A10, each on a new line, and skip any blanks. - Apply Wrap Text
Remember to click the Wrap Text button on the Home tab to see the line breaks.
Common Mistakes and Limitations
Line Breaks Appear as a Square Box
If you see a small square symbol instead of a line break, Wrap Text is not enabled for the cell. Select the cell and click the Wrap Text button on the Home tab. Also, ensure the cell is wide enough for the text to wrap properly.
Extra Blank Lines in the Result
This happens if your source range includes cells that contain only spaces or are not truly empty. The TEXTJOIN function with TRUE as the second argument ignores truly empty cells but not those with formulas returning empty strings or space characters. To fix this, clean your source data or use a more complex formula that trims spaces.
TEXTJOIN Function is Not Available
If you get a #NAME? error, you are using a version of Excel older than 2016. In this case, you must use an older method, such as a formula like =A2 & CHAR(10) & B2 & CHAR(10) & C2. This method requires manually connecting each cell with the & operator and CHAR(10).
Line Breaks Don’t Work in Excel for the Web or Mac
The CHAR(10) code works in Windows versions of Excel. For Excel on a Mac, you might need to use CHAR(13) instead. Test both codes to see which one produces a line break in your environment. Excel for the web supports CHAR(10) but may have rendering differences.
TEXTJOIN vs. CONCAT and Ampersand Method
| Item | TEXTJOIN with CHAR(10) | CONCAT or & with CHAR(10) |
|---|---|---|
| Primary use | Joining ranges with a consistent delimiter | Joining specific, individual cell references |
| Handling empty cells | Automatic skipping with TRUE argument | Manual formula adjustment required |
| Ease of editing | Edit one range reference | Edit each cell reference in the formula |
| Excel version requirement | Excel 2016, Excel 2019, Microsoft 365 | All versions of Excel |
| Formula length for many cells | Short and manageable | Long and prone to error |
You can now create clean, multi-line text entries in a single cell using the TEXTJOIN function. Remember to use CHAR(10) as the delimiter and enable Wrap Text formatting to see the breaks. For more advanced text manipulation, explore combining TEXTJOIN with the TRIM function to clean extra spaces. Use the F9 key in the formula bar to evaluate parts of your TEXTJOIN formula when debugging complex text combinations.