Concatenate Data with a LINE BREAK in Excel [ 5 Steps ]

Concatenate Data with a Line Break:

Here we come up with an exciting article to Concatenate Data with a Line Break in the data in Excel. We can easily combine the data from different cells in the spreadsheet into one cell and discrete them using line breaks. Let’s see the steps to Concatenate Data with a Line Break in Excel.

How to Concatenate with a Line Break by using the Ampersand Operator?

• It is the most commonly used method to ** Concatenate with a Line Break using the Ampersand** Operator.
• This & operator can concatenate the two or more cells into one cell.
• CHAR(10) function will return a line break on Windows. So here, we use this function for adding line breaks in the data.

• You can enter a Formula like the above example image.

How to Concatenate with a Line Break by using CONCATENATE or CONCAT Function?

• We can Concatenate data with a line break using either Concatenate or CONCAT Function.
• It is the same as the first method. We can use Concatenate or CONCAT Function in the Formula to discrete the data with line breaks.

• Like the above example image, you can include the formula to Concatenate strings in different cells into a single cell.

How to Concatenate with a Line Break by using TEXTJOIN Function?

• Use TEXTJOIN Function to Concatenate with a line break. This method is proper when you want to discrete more cells into one.
• This method is easy to Concatenate the range of cells with a line break.
• To do this, you need to enter the formula using *TEXTJOIN * like below.

• In this method, we need to use the CHAR function only once.

How to Concatenate with a Line Break by using Power Query?

• Power Query is an excellent tool in Excel. It allows you to import data from many sources and transform and reshape the data.
• Using Power Query, you can concatenate data with a line break into a single cell. To do this, you need to follow the below steps.

1. Select the table and On the Data tab, choose From Table/Range Icon.
2. It will open the Power Query Editor Dialog box , in which you need to select the column name ( Non-text data field ) and Left-click on it, then choose the text option from the menu list.
3. If there are any numbers in the data, it will show an error. So it would help if you changed that into Text.
4. Then, you have to concatenate with Text.Combine Function.
5. Now, you can combine the data. Click on the Fx icon near the formula bar in the Power Query Editor.
6. = Table.AddColumn(#”Changed Type”, “Address Labels”, each Text.Combine(Record.ToList(_),”#(lf)”)) type this formula in the formula bar. Then Click Enter key.
7. **“#(lf)” **is a Power Query Line Break Character.
8. Now, you can Close and Load the data using the Home tab.

If you feel this method is easy, then make use of it.

How to Format the results with wrap text?

• The above steps are used to Concatenate Data with a Line Break. But you still see the results.
• Let us give you the final step to see the result as you wish.
• All the Line Break Characters we include in the data are there. Now you need to format the cells to wrap text to see the results.
• Select any cells in the table that you want to format. Right-click on it and select the Format Cells option from the menu list.
• On the Alignment tab, Check the Wrap Text box and click the Ok button.

• Now, the selected cells are displayed in multiple lines.

Added Information:

• Press CTRL + 1 Shortcut to open the Format Cells Dialog box in Excel.

A Short Synopsis:

In the above article, you can learn the steps to Concatenate Data with a Line break in Excel. If this content is helpful to you, share your feedback in the comment section. Thanks for visiting Geek Excel. Keep Learning!!