This post will guide you how to** combine multiple cells in one or more columns into one cell with line breaks** or Alt +enter in Excel. How do I concatenate different values together into one cell with line breaks.

For example, if you are trying to combine three cells (B1,B2,B3) into one(C1) with line breaks or **Alt + Enter** separating each value. You can refer to the below tutorial to achieve the result.

Table of Contents

## 1. Concatenate columns with Line Breaks (Alt + Enter) Using Formula

To concatenate the cells with line breaks, you should use the concatenate operator in combination with the CHAR(10) or CHAR(13) function. It means that if you want to add line breaks, you need to use the CHAR(10) to insert actual line breaks on Windows or use the CHAR(13) to insert line breaks on MAC system.

To join the cells with line breaks, just do the following steps:

**#1** type the following formula into the Cell C1, then press Enter key.

**=B1& CHAR(10) & B2 &CHAR(10)&B3**

or you can also use the CONCATENATE function as below:

**=CONCATENATE(B1&CHAR(10),B2&CHAR(13),B3)**

**2#** Select the Cell C1, then go to **Home** Tab, click **Warp Text **command under **Alignment** group.

**3#** you will see that three cells in column have been concatenated with line breaks or Alt +Enter.

## 2. Concatenate columns with Line Breaks with VBA

Now, let’s explore a method that infuses a touch of humanity into our Excel canvas—a dash of VBA magic through a user-defined function. Let’s walk through this together:

**Step1:** Press **Alt + F11** to open the Visual Basic for Applications (VBA) editor.

**Step2:**In the editor, go to **Insert **> **Module **to add a new module.

**Step3:**Copy and paste the provided VBA code into the module. Close the VBA editor.

```
Function MergeCells(rng As Range) As String
Dim cell As Range
Dim result As String
For Each cell In rng
If cell.Value <> "" Then
result = result & cell.Value & Chr(10)
End If
Next cell
' Remove the last line break
If Len(result) > 0 Then
result = Left(result, Len(result) - 1)
End If
MergeCells = result
End Function
```

**Step4:** Now, you can use the function in your worksheet. For example, if you want to merge cells A1 to A3 with line breaks, enter the formula in a cell.

`=MergeCells(A1:A3)`

**Step5:** press **Enter**.

This function loops through the specified range, concatenating non-empty cell values with line breaks. The final result is a string with line breaks between each cell’s value.

## 3. Video: Concatenate columns with Line Breaks

This Excel video tutorial, we’re embarking on a journey to master a delicate art—combining cells from different columns into a single cell, adorned with line breaks using formula or User defined function with VBA code.

## 4. Related Functions

- Excel Concat function

The excel CONCAT function combines 2 or more strings or ranges together.This is a new function in Excel 2016 and it replaces the CONCATENATE function.The syntax of the CONCAT function is as below:=CONCAT (text1,[text2],…)… - Excel CHAR function

The Excel CHAR function returns the character specified by a number (ASCII Value).The CHAR function is a build-in function in Microsoft Excel and it is categorized as a Text Function. The syntax of the CHAR function is as below:=CHAR(number)….

## Leave a Reply

You must be logged in to post a comment.