Table of Contents
To use CONCATENATE with a new line in Excel, first select the cell where you want the combined text to appear. Then, type in the formula “=CONCATENATE(” followed by the first text or cell reference that you want to combine. Next, add the ampersand symbol “&” followed by the quotation marks and the line break symbol “CHAR(10)”. Finally, add another ampersand symbol “&” followed by the next text or cell reference and close the formula with a parenthesis “)”. Press Enter to see the combined text with a new line. This allows you to create a line break within a single cell in Excel.
Excel: Use CONCATENATE with New Line
You can use the CONCATENATE function in Excel to concatenate values from multiple cells into one cell.
To use the CONCATENATE function with a line break as the delimiter, you can use the following formula:
=CONCATENATE(A2, CHAR(10), B2, CHAR(10), C2)
This particular formula combines the values in cells A2, B2 and C2 into one cell, using a line break as the delimiter.
Note: In Excel, CHAR(10) is used to represent a line break.
The following examples shows how to use this formula in practice.
Example: How to Use CONCATENATE with New Line in Excel
Suppose we have the following dataset in Excel that shows the title, name and tenure for various people associated with some basketball team:
Suppose we would like to concatenate each cell in each row into one cell, using a line break as the delimiter.
We can type the following formula into cell D2 to do so:
=CONCATENATE(A2, CHAR(10), B2, CHAR(10), C2)
We can then click and drag this formula down to each remaining cell in column D:
In order to actually see the line breaks in Excel, we must highlight the cell range D2:D8 and then click the Wrap Text icon within the Alignment group in the Home tab:
Once we click this icon, we will be able to see the line breaks in column D:
Note: You can find the complete documentation for the CONCATENATE function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: