How to Insert Line Breaks with TEXTJOIN and CHAR(10) in Excel
Question details
The user wants to replace CONCAT with TEXTJOIN to combine multiple horizontally arranged cell values and insert a line break after each value.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Combining three horizontally arranged production-shift values into a single cell with each value on a new line.
- Observed behavior
- The user needs the combined text to display properly on separate lines within a single cell, which requires the correct formula delimiter and cell formatting.
Ensure you are using a version of Excel or a spreadsheet application that supports the TEXTJOIN function, such as Office 2019, Microsoft 365, or WPS Office.
Use TEXTJOIN with CHAR(10) and Enable Wrap Text
This method uses TEXTJOIN to combine cells while ignoring empty ones, using CHAR(10) to generate a carriage return (line break) between the values.
Using TEXTJOIN is far more efficient than CONCAT or CONCATENATE when dealing with arrays or ranges, as you only need to specify the delimiter once. CHAR(10) acts as the line break character in Windows environments.
Click on the cell where you want the combined values with line breaks to appear.
Type the formula using CHAR(10) as the delimiter. For example: =TEXTJOIN(CHAR(10), TRUE, OFFSET('[Sample Titanium T1 Crib Sheets.xlsx]Data'!$D$6,0,(COLUMN()-COLUMN($D$6))*3,1,3)). The TRUE argument ensures empty cells are ignored.
Press Enter to apply the formula. Initially, the text might appear continuously on a single line.
Select the result cell, navigate to the 'Home' tab on the ribbon, and click the 'Wrap Text' button. The text will now correctly break into separate lines for each combined value.
Combine Text Easily in WPS Spreadsheet
WPS Office fully supports the TEXTJOIN and CHAR(10) functions, allowing you to combine complex data ranges with line breaks effortlessly. It is fully compatible with Microsoft Excel formulas.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your data.
- 2. Input the formula: Select the target cell and input your TEXTJOIN formula with CHAR(10) as the delimiter.
- 3. Format with Wrap Text: Go to the Home tab and click 'Wrap Text' to display the line breaks properly inside the cell.
Frequently Asked Questions
Why isn't CHAR(10) creating a new line in my cell?
The CHAR(10) character requires 'Wrap Text' to be enabled on the cell to render properly. Select the cell, go to the Home tab, and click Wrap Text for the line breaks to display correctly.
Does TEXTJOIN ignore empty cells when inserting line breaks?
Yes, if you set the second argument of the TEXTJOIN function to TRUE, it will ignore any empty cells in the selected range. This prevents consecutive line breaks from appearing in your result.
Can I use CHAR(10) for line breaks on a Mac?
Historically, Mac Excel used CHAR(13) as the line break character. However, in newer versions of Excel for Mac and Microsoft 365, CHAR(10) is fully supported and will work perfectly.
