Note: The other languages of the website are Google-translated. Back to English

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.

The Navigations of This Tutorial

1 Combine columns/rows into one cell

1.1 Combine columns/rows into one cell with space/comma or other delimiters

1.11 Using ampersand symbol (&)

1.12 Using CONCATENATE function (Excel 2016 or earlier versions)

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

1.14 Using Notepad (only for combining columns)

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

1.2 Combine columns/rows into one cell without blanks

1.21 Using TEXTJOIN function (Excel 2019 or Excel 365)

1.22 Using ampersand symbol (&)

1.23 Add at end of cells by using VBA code

1.3 Combine columns/rows/cells into one cell without losing data

1.4 Combine two columns if blank

1.41 Using IF function

1.42 Using VBA

2. Combine rows with same ID

2.1 Combine rows with same ID and separated by comma or other delimiters

2.11 Using VBA

2.12 Using IF function to add helper columns

2.13 Using a handy tool – Advanced Combine Rows

2.2 Combine rows with same ID and do some calculations

2.21 Using Consolidate feature

2.22 Using VBA

2.23 Using a handy tool – Advanced Combine Rows

2.3 Combine adjacent rows with same value

2.31 Using VBA

2.32 Using a handy tool – Merge Same Cells

3. Combine Cells

3.1. Combine multiple rows and columns into a single cell

3.11 Using ampersand symbol (&)

3.12 Using CONCATENATE function (Excel 2016 or earlier versions)

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

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

3.2. Combine cells into a single column/single row

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

3.22 VBA (only for a single column)

3.23 Using a handy tool – Transform Range

3.3 Stack columns into one column without duplicate

3.31 Copy and paste and remove duplicates

3.32 Using VBA

3.4. Combine cells and keep formatting

3.41 Imbed TEXT function in formula

3.42 Using Microsoft Word

3.43 Using a handy tool – Combine without losing data

3.5 Combine cells to make date

3.51 Using DATE function

 

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.

doc sampleClick to download the sample file


1 Combine columns/rows into one cell

1.1 Combine columns/rows into one cell with space/comma or other delimiters

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
doc combine columns rows to one 1
Combine rows into one cell
doc combine columns rows to one 1

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

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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)

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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

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)

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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

doc combine columns rows to one 1  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.

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

Ignore blanks
doc combine columns rows to one 1
Include blanks
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

2. Open a Notepad, and press Ctrl + V to paste the copied range.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1doc combine columns rows to one 1

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:
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

Now, the columns are combined into one and separated by a line break.
doc combine columns rows to one 1

doc combine columns rows to one 1 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.

doc combine columns rows to one 1 doc combine columns rows to one 1

1.2 Combine columns/rows into one cell without blanks

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

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)

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

1.23 Using Defined function

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.
doc combine columns rows to one 1

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

doc combine columns rows to one 1

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

=Concatenatecells(A15:C15)

doc combine columns rows to one 1 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.
doc combine columns rows to one 1


1.3 Combine columns/rows/cells into one cell without losing data

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.
doc combine columns rows to one 1

2. Click Kutools > Merge & Split > Combine Rows, Columns or Cells without Losing Data.
doc combine columns rows to one 1

3. In the popping dialog, specify the options as below:
doc combine columns rows to one 1

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.
doc combine columns rows to one 1 doc combine columns rows to one 1 doc combine columns rows to one 1

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.
doc combine columns rows to one 1 doc combine columns rows to one 1

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. 
doc combine columns rows to one 1 doc combine columns rows to one 1

4) Specify the operation about combined results.

4. Click Ok or Apply to finish the combination.

Keep contents of combined cells
doc combine columns rows to one 1
Delete contents of combined cells
doc combine columns rows to one 1
;Merge the combined cells
doc combine columns rows to one 1

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.


1.4 Combine two columns if blank

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:
doc combine columns rows to one 1

1.41 Using IF function

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.
doc combine columns rows to one 1

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

1.42 Using VBA

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

doc combine columns rows to one 1

doc combine columns rows to one 1 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 doc combine columns rows to one 1  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.
doc combine columns rows to one 1

2.1 Combine rows with same ID and separated by comma or other delimiters

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

2.11 Using VBA

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

doc combine columns rows to one 1

3. Then press F5 key or click Run doc combine columns rows to one 1  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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

1. Sort the ID from A to Z. Select the ID column, click Data > Sort A to Z.
doc combine columns rows to one 1

2. Then in the Sort Warning dialog, check Expand the selection option, click Sort.
doc combine columns rows to one 1

Now the same IDs have been sorted together.
doc combine columns rows to one 1

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

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

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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

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

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

7. Select the last formula column including header, and click Data > Filter.
doc combine columns rows to one 1

8. Click the Filter button and tick Merged checkbox in the drop-down menu, click OK.
doc combine columns rows to one 1

Now only the merged rows have been filtered out, copy the filtered data and paste them in another place.
doc combine columns rows to one 1

More details about IF function. please visit: IF.

2.13 Using a handy tool – Advanced Combine Rows

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

Now the rows have been combined by the same ID.
doc combine columns rows to one 1

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.

2.2 Combine rows with same ID and do some calculations

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

Now the table has been summed based on the same ID.
doc combine columns rows to one 1

2.22 Using VBA

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.
doc combine columns rows to one 1

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 doc combine columns rows to one 1  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.

doc combine columns rows to one 1  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.
doc combine columns rows to one 1

1. Select the table then click Kutools > Merge & Split > Advanced Combine Rows.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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

doc combine columns rows to one 1 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.

2.3 Combine adjacent rows with same value

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?
doc combine columns rows to one 1

2.31 Using VBA

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

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

doc combine columns rows to one 1

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

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

doc combine columns rows to one 1  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.
doc combine columns rows to one 1

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

doc combine columns rows to one 1I If you want to unmerge the merged cells and fill values back, you can apply the Unmerge Cells & Fill Values feature.
doc combine columns rows to one 1
doc combine columns rows to one 1

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

3.1. Combine multiple rows and columns into a single cell

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

Example: Combine range A1:C3
doc combine columns rows to one 1

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

doc combine columns rows to one 1 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)

doc combine columns rows to one 1 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)

doc combine columns rows to one 1 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)

doc combine columns rows to one 1 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.

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

2. In the popping dialog, check Combine into single cell and specify a separator as you need, then click Ok.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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.

3.2 Combine multiple rows and columns into a single cell

 Transpose cells into one single column
 doc combine columns rows to one 1
 Transpose cells into one single row
 doc combine columns rows to one 1
 

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.
doc combine columns rows to one 1

2. In the popping New Name dialog, type a name in the Name textbox, click OK.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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.

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

doc combine columns rows to one 1

3. Then press F5 key or click Run doc combine columns rows to one 1  button, a dialog pops out to select a range of cells, click OK.
doc combine columns rows to one 1

4. Another dialog pops out for selecting a cell to place the result, click OK.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

2. In the Transform Range dialog, check Range to single column or Range to single row option as you need. Click OK.
doc combine columns rows to one 1

3. A dialog pops out for selecting a cell to place the result, click OK.
doc combine columns rows to one 1

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.

3.3 Stack columns into one column without duplicate

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?
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

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

3. Then select the stacked column, click Data > Remove Duplicates, then in the Remove Duplicate dialog, check the column name, click OK.
doc combine columns rows to one 1
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

doc combine columns rows to one 1 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.
doc combine columns rows to one 1

Then check the Range to single column option, and click OK to select a cell to place the stacked column.
doc combine columns rows to one 1

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.

3.32 Using VBA

Moreover, here is a VBA code that can solve 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: 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

doc combine columns rows to one 1

3. Then press F5 key or click Run doc combine columns rows to one 1  button, then a dialog pops out for selecting the columns that needed To be stacked, click OK.
doc combine columns rows to one 1

4. Then in the second popping-out dialog, select a destinate cell to place the stacked column, and click OK.
doc combine columns rows to one 1

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

3.4. Combine cells and keep formatting

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:
doc combine columns rows to one 1

Now in this part, it will provide some ways to combine cells and keep the formatting.
doc combine columns rows to one 1

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)

doc combine columns rows to one 1 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.

3.42 Using Microsoft Word

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

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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1
doc combine columns rows to one 1

Now the contents of table in Word have been converted to texts.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

3.43 Using a handy tool – Combine without losing data

The most efficient way must be using Kutools 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.
doc combine columns rows to one 1

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.
doc combine columns rows to one 1

Now the data has been combined and keep the formatting.
doc combine columns rows to one 1

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.

3.5 Combine cells to make date

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:
doc combine columns rows to one 1

3.51 Using DATE function

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)

doc combine columns rows to one 1 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.
doc combine columns rows to one 1


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.



  • 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...
kte tab 201905
  • 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!
officetab bottom
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Leave your comments
Posting as Guest
×
Rate this post:
0  Characters
Suggested Locations