Concatenate Data with a LINE BREAK in Excel [ 5 Steps ]
Tags: concatenatedatawitha, exceltutorials, excel
Published: 2021-11-15 09:27:38

---
title: Concatenate Data with a LINE BREAK in Excel [ 5 Steps ]
published: true
date: 2021-11-11 14:28:00 UTC
tags: ConcatenateDatawitha,ExcelTutorials,Excel
canonical_url: https://geekexcel.com/concatenate-data-with-a-line-break-in-excel-365-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.

![](https://geekexcel.com/wp-content/uploads/2020/07/mi-28.png)

- 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.

![](https://geekexcel.com/wp-content/uploads/2020/07/mi-27.png)

- 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.

![](https://geekexcel.com/wp-content/uploads/2020/07/mi-29.png)

- 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.

![](https://geekexcel.com/wp-content/uploads/2020/07/nv1.png)

![](https://geekexcel.com/wp-content/uploads/2020/07/nv2.png)

![](https://geekexcel.com/wp-content/uploads/2020/07/nv3.png)

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.

![](https://geekexcel.com/wp-content/uploads/2020/07/nv4.png)

- 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.**](https://geekexcel.com/) Keep Learning!!