How to Use CHAR(10) for Line Breaks in Excel Concatenation
Question details
The user wants to insert line breaks between concatenated cell values using the CHAR(10) function, but the concatenated result remains on a single line.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Concatenating multiple text strings or cell values and attempting to separate them onto new lines within the same cell using a carriage return.
- Observed behavior
- The concatenated text appears on a single continuous line, seemingly ignoring the CHAR(10) line break function, because text wrapping is disabled by default.
Ensure the cells you are trying to concatenate contain standard text or general data. Verify that your row heights are set to auto-fit or are tall enough to display multiple lines of data.
Use CHAR(10) and Enable Wrap Text
The most common reason CHAR(10) fails to display a new line is that the cell's Wrap Text formatting is turned off. You must enable it for the line break to visually apply.
In Excel, simply adding the line feed character via a formula is not enough. The destination cell must be explicitly formatted to allow text wrapping, otherwise Excel will collapse the line break into a space or invisible character.
Select the destination cell and type the formula =A1&CHAR(10)&A2 (or use the CONCATENATE function). Press Enter.
Click once on the cell containing your newly created formula to make it the active cell.
Navigate to the Home tab on the Excel ribbon, locate the Alignment group, and click the Wrap Text button.
If the text still looks cut off, hover between the row numbers on the left and double-click to auto-fit the row height so both lines are visible.
Using TEXTJOIN with CHAR(10) for Multiple Cells
If you need to concatenate an entire range of cells with line breaks between each one, using TEXTJOIN is much more efficient than using the ampersand symbol repeatedly.
Concatenate Text with Line Breaks Effortlessly in WPS Office
WPS Spreadsheet fully supports standard formulas like CHAR(10), CONCATENATE, and TEXTJOIN. You can combine text and apply line breaks with the exact same steps used in Microsoft Excel, completely for free.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your text data.
- 2. Enter the formula: Type your formula using &CHAR(10)& to join your desired cells together.
- 3. Apply Wrap Text: Navigate to the Home tab and click the Wrap Text icon in the alignment section.
- 4. Adjust formatting: Expand the row height by dragging the row boundary to view your cleanly formatted multiline text.
Frequently Asked Questions
Why is my CHAR(10) formula showing as a single line?
By default, Excel does not wrap text in a cell. Even if you use CHAR(10) to insert a carriage return, you must explicitly enable 'Wrap Text' from the Home tab for the line break to visually apply and push the text to the next line.
Do I use CHAR(10) or CHAR(13) for Mac Excel?
In modern versions of Excel for Mac, CHAR(10) is the standard line-feed character and works exactly as it does on Windows. CHAR(13) is a carriage return requirement from much older Mac operating systems and is largely unnecessary today.
How do I remove CHAR(10) line breaks from my Excel data?
To remove line breaks, you can use the CLEAN function (e.g., =CLEAN(A1)) which strips all non-printable characters from the text. Alternatively, you can use the Find and Replace tool by pressing Ctrl+J in the 'Find what' box to locate line breaks and replacing them with a space.
Can I use CHAR(10) inside a standard CONCATENATE function?
Yes, you can structure your formula as =CONCATENATE(A1, CHAR(10), A2) or =CONCAT(A1, CHAR(10), A2). As long as you remember to apply 'Wrap Text' to the formula cell, the line breaks will render properly.
