5-Popular Ways to Concatenate a Range of Cells in Excel

CONCATENATE is a handy function to concatenate text from different cells. You need to refer to the cells which you want to combine. It’s simple and easy to apply.

But here is a bad news. In concatenate, it’s not possible to refer to an entire range.

Here are the steps:

That’s all.

Fill justify is one of the unused but most powerful tools in Excel. And, whenever you need to combine text from different cells you can use it.

​The best thing is, you need a single click to merge text. Have look at below data and follow the steps.

1. First of all, make sure to increase the width of the column where you have text.
2. After that, select all the cells.
3. In the end, go to Home Tab -> Editing -> Fill -> Justify.

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

Learn more about fill justify from here.

If you are using Excel 2016 (Office 365), there is a function called “TextJoin”. It can make it easy for you to combine text from different cells into a single cell.

Syntax:

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

1. ​delimiter a text string to use as a delimiter.
2. ignore_empty true to ignore blank cell, false to not.
3. text1 text to combine.
4. [text2] text to combine optional.

How to use it

To combine below list of values you can use the formula:

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

Here you have used space as a delimiter, TRUE to ignore blank cells and entire range in a single argument. In the end, hit enter and you’ll get all the text in a single cell.

Power Query is a fantastic tool and I love it. You can also use it to combine text from a list in a single cell. Below are the steps.

• Select the range of cells and click on “From Table” in data tab.

• If will edit your data into Power Query editor.

• Now from here, select the column and go to “Transform Tab”.
• From “Transform” tab, go to Table and click on “Transpose”

• Next, you need to merge all the columns.
• For this, select all the columns (select first column, press and hold shift key, click on the last column) and press right click and then select “Merge”.

• After that, from Merge window, select space as a separator and name the column.

• In the end, click OK and click on “Close and Load”.

Now, you have a new worksheet your workbook with all the text in a single cell.

The best thing about using Power Query is you don’t need to do this setup again and again. When you update old list with a new value you need to refresh your query and it will add that new value in the cell.​

If you want to use a macro code to combine text from different cell then I have something for you. With this code, you can combine text in no time.

All you need to do, select the range of cells where you have text and run this code.

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

Make sure to specify your desired location in the code where you want to combine the text.