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:
Column D now displays the concatenated text from each of the cells in each row, using line breaks as the delimiter.
Note: You can find the complete documentation for the CONCATENATE function in Excel here.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
Excel: How to Convert Column into Comma Separated List
Excel: How to Use a Concatenate If Formula
Excel: How to Concatenate Values and Keep Leading Zeros
