**Excel Tutorial: Combine Columns, Rows, Cells**

Combining columns, rows, or cells is usually used in our daily Excel job, such as combining first name and last name which in two columns into one column to get the full name, combining rows based the same ID and summing the corresponding values, combining a range of cells into one single cell and so on. This tutorial lists all scenarios about combining columns/rows/ cells in Excel, and provides the different solutions for you.

In this tutorial, I create some examples to explain the methods, you can change the references for you need when you use below VBA code or formulas, or you can download the samples for trying methods directly.

Click to download the sample file

### 1 Combine columns/rows into one cell

Combining columns or rows into one cell and separating the result by comma, space or other delimiters as below screenshot shown is used most widely in Excel.

Combine columns into one cell |

Combine rows into one cell |

**1.11 Using ampersand symbol (&)**

In Excel, the ampersand symbol & is usually used to join texts.

**Example: Combine first name (column A) and last name (column B) to full name**

Select a cell where you want to place the combined result, then type a formula like this：

=A2&" "&B2

In the formula, & is used to join the texts, A2 and B2 are the two texts that need to be combined, " " is the delimiter (space) that separates the two texts in the result cell, if you want to use a comma as the delimiter, just type a comma enclosed with double quotations ",".

Press **Enter** key to get the combined result, then drag the auto-fill handle down to get the combined results.

**Example: Combine No. (row 15) and name (row 16) to one cell**

If you want to combine rows into one cell, change the cell references and separator in the formula as you need, and drag the auto-fill handle to the right to get the combined results.

**1.12 Using CONCATENATE function (Excel 2016 or earlier versions)**

If you are using Excel 2016 or earlier versions, the CONCATENATE function can help you.

Syntax about CONCATENATE |

CONCATENATE (text1,[text2],…) |

For more details about the CONCATENATE function, please visit: CONCATENATE.

**Example: Combine names (column F) and address (column G) into one column**

Select a cell where you want to place the combined result, then type a formula like this：

=CONCATENATE(F2,",",G2)

In the formula, F2 and G2 are the two texts that need to be combined, "," is the delimiter (comma) that separates the two texts in the result cell, if you want to use space as the delimiter, just type a space enclosed with double quotations " ".

Press **Enter** key to get the combined result, then drag the auto-fill handle down to get the combined results.

For combining rows, just change the cell references and delimiter as needed, and drag the auto-fill handle to the right.

**1.13 Using CONCAT function or TEXTJOIN function (Excel 2019 or Excel 365)**

If you use Excel 2019 or Excel 365, the CONCAT function and TEXTJOIN function may be the better choice.

Syntax about CONCAT |

CONCAT (text1,[text2],…) |

For more details about the CONCAT function, please visit: CONCAT.

The usage of CONCAT function is the same as CONCATENATE function, to combine first name and last name in two columns separately into one column, the formula is used like this:

=CONCAT(A21," ",B21)

In the formula, A21 and B21 are the two texts that need to be combined, " " is the delimiter (space) that separates the two texts in the result cell, if you want to use other delimiters, just type the delimiter enclosed with double quotations "".

Press Enter key to get the combined result, then drag the auto-fill handle down to get the combined results.

Differing from combining columns, while combining rows, after entering the first formula, you need to drag the auto-fill handle to the right until all combined results gotten..

Syntax about TEXTJOINT |

TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...) |

For more details about TEXTJOIN function, please visit: TEXTJOIN.

To combine columns or rows by using TEXTJOIN:

=TEXTJOIN(",",TRUE,E21:G21))

In the formula, E21:G21 is a continuous range that needs to be combined. " " is the delimiter (space) that separates the two texts in the result cell, if you want to use other delimiters, just type the delimiter enclosed with double quotations "". Logical text “TRUE” indicates to ignore empty cells when combining, if you want to combine with blanks, replace TRUE with FALSE.

Ignore blanks |

Include blanks |

**1.14 Using Notepad (only for combining columns) **

1. Using Notepad also can achieve combining columns (no effect on combining rows).

Select the range that you want to combine columns into one, and press **Ctrl** + **C** keys to copy the range.

2. Open a **Notepad**, and press **Ctrl** + **V** to paste the copied range.

3. Select a tab between two texts in the Notepad and press **Ctrl** + **H** to enable **Replace** feature, a tab symbol has been placed in **Find what** textbox.

4. In the **Replace with** textbox, type the delimiter you want to separate the combined texts, and then click **Replace All**. Then close the **Replace** dialog.

5. Now select the texts in Notepad, and press **Ctrl** + **C** to copy them, and go to Excel, select a cell and press **Ctrl** + **V** to paste the combined results.

**Extension: Combine columns/rows into one cell with line break as delimiter**

If you want to combine columns or rows into one cell with a line break as below screenshot shown:

Firstly, you should realize that in Excel, CHAR() function can be used to specify characters that are hard to enter in a formula, such as CHAR(10) returns a line break.

Then use the CHAR(10) into the formulas that list above to combine columns or rows with line break as delimiter:

Ampersand symbol:

=A49&CHAR(10)&B49

CONCATENATE function:

=CONCATENATE(A49,CHAR(10),B49)

CONCAT function

=CONCAT(A49,CHAR(10),B49)

TEXTJOIN function

=TEXTJOIN(CHAR(10),TRUE,A49:B49)

After entering an formula above, select the results, and click **Home** > **Wrap Text**.

Now, the columns are combined into one and separated by a line break.

If you just want to combine rows in one column to a single cell, the Justify feature in Excel can help you. Select the column with multiple rows, and click **Home** > **Fill** > **Justify**, then the rows will be combined into the top cell of the range with space as a separator.

In this part, the tutorial introduces the methods to combine columns or rows into one cell and skip empty cells as below screenshot shown:

**1.21 Using TEXTJOIN function (Excel 2019 or Excel 365)**

If you are using Excel 2019 or Excel 365, fortunately, the new function TEXTJOIN can solve this job easily.

Syntax about CONCATENATE |

TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...) |

For more details about TEXTJOIN function, please visit: TEXTJOIN.

**Example: Combine name (column A) and No. (column B) and domain (column C) into one cell to generate an e-mail address, some No. may be blank.**

Select a cell which used to place the combined result, type the formula as this:

=TEXTJOIN("",TRUE,A2:C2)

In the formula, A2:C2 is the range that contains texts that needed to be combined, "" is the delimiter (none) that separates the two texts in the result cell, if you want to use a delimiter to separate the texts, just type the delimiter enclosed with double quotations "", such as ",". Logical text “TRUE” indicates to ignore empty cells when combining, if you want to combine with blanks, replace TRUE with FALSE.

Press **Enter** key to get the first result, and drag the auto-fill handle down or right as you need to generate all results.

**1.22 Using ampersand symbol (&)**

If you are in Excel 2016 or earlier Excel versions, use the ampersand symbol & to connect the texts one by one and manually skip blanks.

Select a cell where you want to place the combined result, then type a formula like this：

=A10&C10

In the formula, & is used to join the texts, A10 and C10 are the two texts that needed to be combined, if you want to use a comma as a delimiter, just type a comma enclosed with double quotations like "," and use & to connect between two texts.

Press **Enter** key to get the combined result. Then change the references of the formula to get the next combined result.

For Excel 2016 and earlier Excel versions users, if there are multiple columns or rows needed to be combined while skipping blanks, you can use a VBA to create a Defined function to quickly solve this job.

1. Press **Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window.

2. Click **Insert** > **Module** to insert a new module.

3. Copy and paste the below code to the new module and save it.

**VBA: Combine cells skip empty cells**

```
Function Concatenatecells(ConcatArea As Range) As String
'updateby Extendoffice
For Each n In ConcatArea: nn = IIf(n = "", nn & "", nn & n & "_"): Next
Concatenatecells = Left(nn, Len(nn) - 1)
End Function
```

4. Go back to the worksheet, and select a cell that place the combined result, type the formula

=Concatenatecells(A15:C15)

In the formula, A15:C15 is the range contains texts that needed to be combined. In the VBA code, "_" indicates the delimiter that used to separate the texts in the combined result, you can change the delimiter as you need.

If you have installed **Kutools for Excel** in Excel, the **Combine Rows, Columns or Cells without Losing Data** feature is quite a help in kinds of combining jobs.

1. Select the cells that you want to combine and the cells that you want to place the combined results.

2. Click **Kutools** > **Merge & Split** > **Combine Rows, Columns or Cells without Losing Data**.

3. In the popping dialog, specify the options as below:

1) Specify to combine columns, or combine rows, or combine all cells into one.

Combine columns Eg. | Combine rows Eg. | Combine into single cell Eg. |

2) Specify the separator for the texts in the combined result.

3) Specify the location of the combined result (disabled when choosing the Combine into single cell option)

When choosing the Combine columns option, you can specify to place the result in the left cell of the selected range, or the right cell of the selected range.

Left cell Eg. | Right cell Eg. |

When choosing the** Combine rows** option, you can specify to place the result in the top cell of the selected range, or the bottom cell of the selected range.

Top cell Eg. | Bottom cell Eg. |

4) Specify the operation about combined results.

4. Click **Ok** or **Apply** to finish the combination.

Keep contents of combined cells |

Delete contents of combined cells |

;Merge the combined cells |

For more details about this feature, please visit Combine Columns, Rows, Cells without Losing Data.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

Sometimes, you may want to combine two columns if one of the columns contains blank cells. Supposing there are two columns, column A contains User Names and some blank cells, and column B contains Names, now just fill the blank cells in column A with the corresponding contents in column B when combining the two columns as below screenshot shown:

The IF function is used to test for a specific condition. Here you can use the IF function to test if the cell is blank, then fill the blanks with contents in another column.

Syntax about IF function |

IF (logical_test, [value_if_true], [value_if_false]) |

For more details about the IF function, please visit: IF

Select the top cell of the column that you want to place the combined results, and copy or enter below formula:

=IF(A2="",B2,A2)

Then press **Enter** key to get the first result, and drag the auto-fill handle down to get all results.

Now only the blank cells in column A have been filled with the contents in column B.

Here is a VBA code also can handle this job.

1. Press **Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. Copy and paste the below code to the new module.

**VBA: Merge two columns if blank**

```
Sub MergebyBlank()
'UpdatebyExtendoffice20220506
Dim xRg1, xRg2, xRgUser As Range
Dim xRg As Range
Dim xWsh As Worksheet
Dim xCount, xFNum As Integer
Set xRg1 = Range("A:A")
Set xRg2 = Range("B:B")
Set xWsh = xRg1.Worksheet
Set xRgUser = xWsh.UsedRange
Set xRg1 = Intersect(xRgUser, xRg1)
Set xWsh = xRg2.Worksheet
Set xRgUser = xWsh.UsedRange
Set xRg2 = Intersect(xRgUser, xRg2)
xCount = xRg1.Count
If (xCount > xRg2.Count) Then
xCount = xRg2.Count
End If
For xFNum = 1 To xCount
If (xRg1.Item(xFNum).Value = "") Then
If (xRg2.Item(xFNum).Value <> "") Then
xRg1.Item(xFNum).Value = xRg2.Item(xFNum).Value
End If
ElseIf (xRg2.Item(xFNum).Value = "") Then
If (xRg1.Item(xFNum).Value <> "") Then
xRg2.Item(xFNum).Value = xRg1.Item(xFNum).Value
End If
End If
Next
End Sub
```

In the code, A:A and B:B are the two columns that will be merged, just change them as you need.

3. Then press **F5** key or click **Run** button, then the column A will be filled with value in column B if the value is empty in column A.

### 2 Combine rows with same ID

Supposing there is a table with multiple columns, one column contains ID values which have duplicate items. Now the tutorial provides methods for combining the rows of the table based on the same ID and do some calculations.

Here in this part, it provides the methods for combining rows with the same ID and separated by commas as below screenshot shown:

Here provides a VBA code that can handle this job

1. Press **Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. Copy and paste the below code to the new module.

**VBA: Combine rows with same ID**

```
Sub Combine_Rows()
'UpdatebyExtendoffice20220506
Dim xRg As Range
Dim xRows As Long
Dim I As Long, J As Long, K As Long
On Error Resume Next
Set xRg = Application.InputBox("Select Range:", "Kutools For Excel", Selection.Address, , , , , 8)
Set xRg = Range(Intersect(xRg, ActiveSheet.UsedRange).Address)
If xRg Is Nothing Then Exit Sub
xRows = xRg.Rows.Count
For I = xRows To 2 Step -1
For J = 1 To I - 1
If xRg(I, 1).Value = xRg(J, 1).Value And J <> I Then
For K = 2 To xRg.Columns.Count
If xRg(J, K).Value <> "" Then
If xRg(I, K).Value = "" Then
xRg(I, K) = xRg(J, K).Text
Else
xRg(I, K) = xRg(I, K).Text & "," & xRg(J, K).Text 'here uses comma as separator, you can change it to others as you need
End If
End If
Next
xRg(J, 1).EntireRow.Delete
I = I - 1
J = J - 1
End If
Next
Next
ActiveSheet.UsedRange.Columns.AutoFit
End Sub
```

3. Then press **F5** key or click **Run** button, then a Kutools for Excel dialog pops out for you to select a table that needs to be combined rows with the same ID.

4. Click **OK**. Now the selected table has been combined rows based on the same ID.

**2.12 Using IF function to add helper columns**

If there are only two columns, and you want to combine the rows with the same ID, and another column values separated by a comma as the below screenshot shows, the IF function can do you a favor.

1. Sort the ID from A to Z. Select the ID column, click **Data** > **Sort A to Z**.

2. Then in the **Sort Warning** dialog, check **Expand** the selection option, click **Sort**.

Now the same IDs have been sorted together.

3. In the adjacent column, using the IF function as this:

=IF(A17=A18,C17&", "&B18,B18)

In the formula, A17 and A18 are the two adjacent cells in the ID column (A17 is the header of the ID column), B18 is the corresponding cell of cell A18. ", " indicates to separate the values with a comma. you can change the references and the separator as needed.

4. Press **Enter** key to get the first result, then drag the auto-fill handle down to get all results.

5. Go to the adjacent column, type the formula as this:

=IF(A18<>A19,"Merged","")

In the formula, A18 and A19 are the two adjacent cells in the ID column, if the adjacent cells are not equal, it will return to "Merged", otherwise, it will return empty.

6. Press **Enter** key to get the first result, then drag the auto-fill handle down to get all results.

7. Select the last formula column including header, and click **Data** > **Filter**.

8. Click the **Filter** button and tick **Merged** checkbox in the drop-down menu, click **OK**.

Now only the merged rows have been filtered out, copy the filtered data and paste them in another place.

More details about IF function. please visit: IF.

If you have installed **Kutools for Excel** in Excel, the **Advanced Combine Rows** can easily do this job.

1. Select the table then click **Kutools** > **Merge & Split** > **Advanced Combine Rows**.

2. In the **Advanced Combine Rows** dialog, please do as these:

1) Choose the ID column, and set it as **Primary Key**;

2) Choose the column that you want to combine the values with separator, click **Combine** and choose one separator.

3）click **Ok**.

Now the rows have been combined by the same ID.

The Advanced Combine Rows feature will break the original data, please save the data as a copy before using it.

For more details about this feature, please visit Advanced Combine Rows.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

If you want to combine rows with the same ID and then sum the values or do other calculations as the below screenshot shows, the below methods can help you.

**2.21 Using Consolidate feature**

If there are only two columns, one column contains texts (ID), and another one contains values that need to be calculated, such as sum, the Excel built-in feature **Consolidate** can do you a favor.

1. Select a cell whereyou want to place the combined result, then click **Data** > **Consolidate**.

2. In the **Consolidate** dialog, do as these:

1) Choose the **Functions** as you need;

2) Click the arrow to select the table;

3) Click **Add** to add the selected range to the **All references** list;

4) Tick checkboxes of **Top row** and **Left column**;

5) Click **OK**.

Now the table has been summed based on the same ID.

Here is a VBA that also can combine rows with the same ID and then sum values.

1. Press **Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. In the new module, copy and paste the below VBA code into it.

**VBA: Combine duplicate rows and sum**

```
Sub CombineRows()
'Updateby Extendoffice
Dim WorkRng As Range
Dim Dic As Variant
Dim arr As Variant
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Dic = CreateObject("Scripting.Dictionary")
arr = WorkRng.Value
For i = 1 To UBound(arr, 1)
Dic(arr(i, 1)) = Dic(arr(i, 1)) + arr(i, 2)
Next
Application.ScreenUpdating = False
WorkRng.ClearContents
WorkRng.Range("A1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.keys)
WorkRng.Range("B1").Resize(Dic.Count, 1) = Application.WorksheetFunction.Transpose(Dic.items)
Application.ScreenUpdating = True
End Sub
```

3. Then press **F5** key or click **Run** button, then a dialog pops out for selecting the table that you use, and then click **OK**.

Now the selected range has been combined with the same values and summed.

The VBA will break the original data, please save the data as a copy before using VBA.

**2.23 Using a handy tool – Advanced Combine Rows**

If there are more than two columns in the table that you want to combine and do calculations, for example, there are three columns, the first one contains duplicate product names which needed to be combined together, the second one contains store names which needed to be combined and separated by a comma, the last column contains numbers which needed to be sum based on the duplicate rows in the first column, as below screenshot shown, Kutools for Excel’s Advanced Combine Rows can help you.

1. Select the table then click **Kutools** > **Merge & Split** > **Advanced Combine Rows**.

2. In the** Advanced Combine Rows** dialog, please do as these:

1) Choose the ID column, and set it as **Primary Key**;

2) Choose the column that you want to combine the values with a separator, click **Combine** and choose one separator.

3) Choose the column that you want to do the calculation, click **Calculate** and choose one calculation.

4）click **Ok**.

Now the rows have been combined with the same rows and calculated.

The Advanced Combine Rows feature will break the original data, please save the data as a copy before using it.

For more details about this feature, please visit Advanced Combine Rows.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

If there is a table with multiple columns, one column contains some duplicate values, now the job is to combine the adjacent rows in this column with the same values as the below screenshot shows. How can you solve it?

In Excel, there is no built-in feature that can directly solve this job, but here provides a VBA which can handle it.

**Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. In the new module, copy and paste the below VBA code into it.

**VBA: Combine adjacent rows with same value**

```
Sub MergeSameCell()
'Updateby Extendoffice
Dim Rng As Range, xCell As Range
Dim xRows As Integer
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Application.ScreenUpdating = False
Application.DisplayAlerts = False
xRows = WorkRng.Rows.Count
For Each Rng In WorkRng.Columns
For i = 1 To xRows - 1
For j = i + 1 To xRows
If Rng.Cells(i, 1).Value <> Rng.Cells(j, 1).Value Then
Exit For
End If
Next
WorkRng.Parent.Range(Rng.Cells(i, 1), Rng.Cells(j - 1, 1)).Merge
i = j - 1
Next
Next
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
```

3. Then press **F5** key or click **Run** button to enable this VBA, then a dialog pops out for selecting the table that you use, and then click **OK**.

Now the adjacent rows with the same values in the first column are combined in the selected range.

The VBA will break the original data, please save the data as a copy before using VBA. And this VBA will merge the same values in the first column of the selected table.

**2.32 Using a handy tool – Merge Same Cells**

If you have **Kutools for Excel** installed in Excel, the **Merge Same Cells** feature of Kutools for Excel can solve this job in one step.

Select the column that you want to combine the same values, and then click **Kutools** > **Merge & Split** > **Merge Same Cells**.

Now the adjacent cells with the same values have been merged.

I If you want to unmerge the merged cells and fill values back, you can apply the **Unmerge Cells & Fill Values** feature.

For more details about this feature, please visit Merge Same Cells.

For more details about this feature, please visit Unmerge Cell.

For more details about Kutools for Excel, please visit Kutools for Excel.

For 30- day free trial of Kutools for Excel, please download now.

### 3 Combine Cells

For combining a range with multiple rows and columns into one single cell, here provides four methods.

**Example: Combine range A1:C3**

**3.11 Using ampersand symbol (&)**

In Excel, the ampersand symbol & is usually used to join texts.

Select a cell that you want to place the combined result, then type a formula like this：

=A1&", "&B1&", "&C1&", "&A2&", " &B2&", "&C2&", "&A3&", "&B3&", "&C3

In the formula, & is used to join the texts, ", " is the delimiter (comma + space) that separates the two texts in the result cell, if you want to use other delimiters, just type a separator enclosed with double quotations.

Press **Enter** key to get the combined result.

**3.12 Using CONCATENATE function (Excel 2016 or earlier versions)**

If you are using Excel 2016 or earlier versions, the CONCATENATE function can help you.

Syntax about CONCATENATE |

CONCATENATE (text1,[text2],…) |

For more details about the CONCATENATE function, please visit: CONCATENATE

Select a cell that you want to place the combined result, then type a formula like this:

=CONCATENATE(A1,", ",B1,", ",C1,", ",A2,", ",B2,", ",C2,", ",A3,", ",B3,", ",C3)

In the formula, A1, B1…,C3 are the texts you want to combine. ", " is the delimiter (comma + space) that separates the two texts in the result cell, if you want to use other delimiters, just type a separator enclosed with double quotations.

Then press **Enter** key to get the combined result.

**3. 13 Using CONCAT function or TEXTJOIN function (Excel 2019 or Excel 365)**

If you use Excel 2019 or Excel 365, the CONCAT function and TEXTJOIN function may be the better choice.

Syntax about CONCAT |

CONCAT (text1,[text2],…) |

For more details about the CONCAT function, please visit: CONCAT.

The usage of CONCAT function is the same as CONCATENATE function, to combine first name and last name which in two columns separately into one column, the formula is used like this:

=CONCAT(A1,", ",B1,", ",C1,", ",A2,", ",B2,", ",C2,", ",A3,", ",B3,", ",C3)

In the formula, A1, B1…,C3 are the texts you want to combine. ", " is the delimiter (comma + space) that separates the two texts in the result cell, if you want to use other delimiters, just type a separator enclosed with double quotations.

Then press **Enter** key to get the combined result.

Syntax about TEXTJOIN |

TEXTJOIN (delimiter, ignore_empty, text1, [text2], ...) |

For more details about TEXTJOIN function, please visit: TEXTJOIN.

To combine columns or rows by using TEXTJOIN:

=TEXTJOIN(", ",TRUE,A1:C3)

In the formula, A1:C3 is a continuous range that need to be combined. ", " is the delimiter (comma + space) that separates the two texts in the result cell, if you want to use other delimiters, just type the delimiter enclosed with double quotations "". Logical text “TRUE” indicates to ignore empty cells when combining, if you want to combine with blanks, replace TRUE with FALSE.

Then press **Enter** key to get the combined result.

If you want to use line break as a delimiter, using CHAR(10) in the formula, such as =TEXTJOIN(CHAR(10),TRUE,A1:C3), then format the result cell as Wrap Text.

**3.14 Using Combine columns/rows/cells into one cell without losing data**

If you have Kutools for Excel installed in Excel, the** Combine Rows, Columns or Cells without Losing Data** feature can quickly handle this job.

1. Select the range of cells that you want to combine into one cell, then click **Kutools** > **Merge & Split** > **Combine Rows, Columns or Cells without Losing Data**.

2. In the popping dialog, check** Combine into single cell** and specify a separator as you need, then click **Ok**.

Now the cells are merged into one cell with a specific delimiter. If the cell contents are too many to show in the merged cell, you can click **Wrap Text** under the **Home** tab to show them.

For more details about this feature, please visit Combine Columns, Rows, Cells without Losing Data.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

Transpose cells into one single column |

Transpose cells into one single row |

**3.21 Name the range and use INDEX function (only for to a single column)**

If you want to transpose the range of cells into a single column, you can name the range and then use INDEX function.

1. Select the range of cells, right-click to enable the context menu, and click **Define Name**.

2. In the popping **New Name** dialog, type a name in the **Name** textbox, click **OK**.

3. After naming the range, then select a cell that places the transposed data, use the INDEX function as this:

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

For more details about INDEX function, please visit here.

4. Press **Enter** key and then drag the auto-fill handle down to fill this formula until an #REF! error value appears.

5. Remove the error value, then the range of cells has been combined into one column.

**3.22 VBA (only for a single column)**

For combining cells into one single column, here is a VBA that also can work.

**Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. Copy and paste the below VBA code to the blank module..

**VBA: Convert range to a column**

```
Sub ConvertRangeToColumn()
'UpdatebyExtendoffice
Dim Range1 As Range, Range2 As Range, Rng As Range
Dim rowIndex As Integer
xTitleId = "KutoolsforExcel"
Set Range1 = Application.Selection
Set Range1 = Application.InputBox("Source Ranges:", xTitleId, Range1.Address, Type:=8)
Set Range2 = Application.InputBox("Convert to (single cell):", xTitleId, Type:=8)
rowIndex = 0
Application.ScreenUpdating = False
For Each Rng In Range1.Rows
Rng.Copy
Range2.Offset(rowIndex, 0).PasteSpecial Paste:=xlPasteAll, Transpose:=True
rowIndex = rowIndex + Rng.Columns.Count
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
```

3. Then press **F5** key or click **Run** button, a dialog pops out to select a range of cells, click **OK**.

4. Another dialog pops out for selecting a cell to place the result, click **OK**.

**3.23 Using a handy tool – Transform Range**

If you want to combine cells into one row, there is no built-in feature in Excel to support it. However, if you have** Kutools for Excel** installed, its **Transform Range** feature supports converting a range to a single column or a row, also supports converting a row or a column to a range.

1. select the range of cells, and click **Kutools** > **Range** > **Transform Range**.

2. In the **Transform Range** dialog, check **Range to single column** or **Range to single row** option as you need. Click **OK**.

3. A dialog pops out for selecting a cell to place the result, click **OK**.

Now the range of cells has been converted to a row or a column.

For more details about this feature, please visit Transform Range.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

If there are some duplicates in a table with multiple columns, how can you stack the columns into one column without the duplicates as the below screenshot shows?

In this part, it provides three different methods of handling this job.

**3.31 Copy and paste and remove duplicates**

In Excel, the general method to solve this job is to copy and paste the columns one by one, and then remove the duplicate values.

1. Select the first column and press **Ctrl** + **C** keys to copy it, then select a destinate cell and press **Ctrl** + **V** keys.

2. Then repeat step 1 to copy other columns and paste them below the first column.

3. Then select the stacked column, click **Data** > **Remove Duplicates**, then in the **Remove Duplicate** dialog, check the column name, click **OK**.

4. Now a dialog pops out to remind you the duplicates values are removed. Click **OK** to close it, and the stacked column only keeps the unique values.

If there are hundreds of columns, copying and pasting one by one is time-consuming. However, if you have **Kutools for Excel** installed in Excel, the **Transform Range** feature can quickly convert the range to a column, then apply the** Remove Duplicates** feature of Excel.

Select the range of columns, and click **Kutools** > **Range** > **Transform Range**.

Then check the **Range to single column** option, and click **OK** to select a cell to place the stacked column.

And then apply Remove Duplicates to remove the duplicate values.

For more details about this feature, please visit Transform Range.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a 30-day free trial of Kutools for Excel, please download it now.

Moreover, here is a VBA code that can solve this job.

**Alt** + **F11** keys to enable the **Microsoft Visual Basic for Applications** window, then click **Insert** > **Module** to create a new blank module.

2. Copy and paste the below code to the new module.

**VBA: Stack columns into one without duplicates**

```
Sub FindUniques()
'UpdatebyExtendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
Set dic = CreateObject("Scripting.Dictionary")
For j = 1 To InputRng.Columns.Count
For i = 1 To InputRng.Rows.Count
xValue = InputRng.Cells(i, j).Value
If xValue <> "" And Not dic.Exists(xValue) Then
OutRng.Value = xValue
dic(xValue) = ""
Set OutRng = OutRng.Offset(1, 0)
End If
Next
Next
End Sub
```

3. Then press **F5** key or click **Run** button, then a dialog pops out for selecting the columns that needed To be stacked, click OK.

4. Then in the second popping-out dialog, select a destinate cell to place the stacked column, and click **OK**.

Now the columns have been stacked into one column with unique values only.

If there are two columns, one is formatted as a special formatting, such as a custom DateTimemm/dd/yyyy, to combine these two columns into one with the normal methods, the custom formatting will be removed as the below screenshot shows:

Now in this part, it will provide some ways to combine cells and keep the formatting.

**3.41 Imbed TEXT function in formula**

TEXT function is used to convert a number to text in a specific formatting. Here we can use it to convert the cell (contains a number) to a formatting firstly, and then combine it with other cells by using “&”, CONCATENATE function, CONCAT function or TEXTJOIN function.

For details about general combining cells into one, please go back to 1.1.

Syntax about TEXT function |

TEXT (value, format_text) |

For more details about TEXT function, please visit TEXT function.

Here takes embedding TEXT function in CONCAT function as instance.

Please copy and paste the below formula to a cell in which you want the combined result placed:

=CONCAT(TEXT(A2,"mm/dd/yyyy hh:mm")," ",B2)

In the formula, A2 is the cell that you want to keep its formatting, “mm/dd/yyyy hh:mm” is the formatting you use, B2 is the other cell that is used to combine. " " indicates separating the values with space. you can change the references, formatting and separator as needed.

Press **Enter** key and drag the auto-fill handle down to fill the cells with this formula.

1. Select the table that you want to combine cells into one, and press **Ctrl** + **C** to copy them.

2. Open a blank **Word** document, press **Ctrl** + **V** to place them, then click on the table in the document, now a cross icon appears at the top right of the table.

3. Click the **Layout** tab, and then click **Convert to Text** in the **Data** group, in the popping **Convert Table To Text** dialog, specify the separator for the columns. Click **OK**.

Now the contents of table in Word have been converted to texts.

4. Select the converted texts and press **Ctrl** + **C** to copy them, and go back to Excel and select a blank cell, press **Ctrl** + **V** to paste the combined result.

**3.43 Using a handy tool – Combine without losing data**

The most efficient way must be using K**utools for Excel**’s **Combine Rows, Columns or Cells without Losing Data** feature, which just needs to tick the **Use formatted values** checkbox while applying the feature, the combined result will keep the data formatting.

1. Select the table including the cells that place the result, click **Kutools** > **Merge & Split** > **Combine Rows, Columns or Cells without Losing Data**.

2. In the popping dialog, specify the combine operation as you need, and untick the **Use formatted values** checkbox (in default, this option is ticked). Click **Ok**.

Now the data has been combined and keep the formatting.

For more details about this feature, please visit Combine Columns, Rows, Cells without Losing Data.

For more details about Kutools for Excel, please visit Kutools for Excel.

For a {module745}-day free trial of Kutools for Excel, please download it now.

Supposing there is a table that contains years, months, and days in separated columns, the job is to combine the columns and to generate a date as below screenshot shown:

The DATE function is used to create a date with year, month and day.

Syntax about DATE function |

DATE( year, month, day ) |

For more details about DATE function, please visit DATE function.

Copy and paste the below formula to a cell that will place the date:

=DATE(A2,B2,C2)

In the formula, A2, B2 and C2 are the cells containing the year, month and day values.

Press **Enter** key to get the first date, then drag the auto-fill handle down to get all dates.

**More Excel Tutorials:**

**Combine Multiple Workbooks/Worksheets Into One**

This tutorial, listing almost all combining scenarios you may face and providing relative professional solutions for you.

**Split Text, Number, And Date Cells (Separate Into Multiple Columns)**

This tutorial is divided into three parts: split text cells, split number cells and split date cells. Each part provides different examples to help you know how to handle the splitting job when encountering the same problem..

**Combine Contents Of Multiple Cells Without Losing Data In Excel**

This tutorial narrows down the extraction to a specific position in a cell and collects different methods to help extract text or numbers from a cell by specific position in Excel.

**Compare Two Columns For Matches And Differences In Excel**

Here this article covers most possible scenarios of the comparing two columns you might meet, and hope it can help you.

## The Best Office Productivity Tools

### Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...

**Enable tabbed editing and reading in Word, Excel, PowerPoint**, Publisher, Access, Visio and Project.- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!