concatenate with line break in Excel

Q53. I want a formula to concatenate multiple cell values separated by line breaks.

For example, I would like to combine values in Columns G, H and I separated by line breaks in Column J as shown below. As I have a very big table, doing it manually is very laborious.

excel concatenate with line break

To do it in Excel, here is the answer:

a) Enter the formula =G4 & CHAR(10) & H4 & CHAR(10) & I4 in cell J4 as shown below. Drag the formula all the way down to end of the column.

ASCII code for line break is 10. Hence CHAR(10) is used in formula.

excel excel concatenate with line break

b) All the cells in "Combined Attributes" column have data in desired format.

Note: Select the cells in column and click on "Wrap Text" under "Home" to display as shown in Screenshot. If Wrap Text is not set, the cell values would show up next to each other.

excel excel excel concatenate with line break

 

You can find similar Excel Questions and Answer hereunder

1) How can I calculate depreciation of assets using Excel?

2) how do you enter multiple line in a cell in Excel

3) I want to force a page break at a certain row - how can I do that?

4) How can I add and link a Chart Title to a cell value?

5) How can I prevent edits on my worksheet by protecting it with a Password ?

6) Import txt file in Excel

7) How can I get the count of number of series in a Chart using VBA?

8) String split in vba in Excel

9) How can I remove hyperlinks from a range of cells?

10) Concatenate number with text but keeping in number format in Excel

11) I have data arranged in rows of information. Is there any way I can enter new data through a form instead of entering it directly in SpreadSheet?

12) How can I prevent edits on my worksheet by protecting it with a Password ?

13) How can I filter and copy only filtered data using VBA?

14) How to add a link in a sheet to another sheet

15) How can I identify all cells with Data Validation in my WorkSheet?