How to Combine a Range of Cells using CONCATENATE in Excel

Combining values from a range of cells with a comma in Excel can be achieved through various methods. Let’s explore five different approaches:

How to Combine a Range of Cells with a Comma in Excel

1. Using CONCATENATE and TRANSPOSE:

In cell B8 (edit using F2), enter the following formula without pressing Enter:

=CONCATENATE(TRANSPOSE(A1:A5)&” “)

Select the entire inside portion of the CONCATENATE function and press F9 to convert it into an array.

Remove the curly brackets from the start and end of the array.

Finally, press Enter to combine the values into a single cell.

2. Fill Justify Option:

Increase the width of the column containing the text.

Select all the cells to be merged.

Go to the Home tab, click on Editing, and select Fill ➜ Justify.

This will merge the text from all the cells into the first cell of the selection.

3. TEXTJOIN Function:

If you have Excel 2016 or Office 365, you can use the TEXTJOIN function.

The syntax is: =TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

For example, to combine the values from range A1:A5 with a space delimiter, ignoring blank cells:

=TEXTJOIN(” “, TRUE, A1:A5)

4. Power Query:

Select the range of cells and click on “From table” in the Data tab.

In the Power Query editor, select the column and go to the Transform tab.

Click on Table ➜ Transpose to transform the columns into rows.

Select all the columns, right-click, and choose “Merge”.

Set the separator as a space and name the column.

How to Combine a Range of Cells using CONCATENATE in Excel

Click OK and then Close & Load to get the combined text in a new worksheet.

5. VBA Code:

If you prefer using a macro code, you can use the following VBA code.

Select the range of cells and run the code. It will combine the text into a single cell.

Sub combineText()

    Dim rng As Range

    Dim i As String

    For Each rng In Selection

        i = i & rng & ” “

    Next rng

    Range(“B1”).Value = Trim(i)

End Sub

Specify the desired location in the code where you want to combine the text.

Each method has its own advantages, so choose the one that suits your needs. Try them out and let me know your favorite method that worked for you. Don’t forget to share this post with your friends who might find it helpful.