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.

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.

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

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.

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.

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:

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.

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

 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:

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

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.

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.

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?

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

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.

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

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.

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

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.

3.42 Using Microsoft Word

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

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:

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)

 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: