Combine Values In Multiple Cells Into Single Cell Separated By Delimiter In Excel

In this post we will see how could we combine a range of cells separated by comma, space colon etc in excel.

We could concatenate a range of values by using concatenate formula or by using & concatenate operator, but the challenge of using these is that you have to manually insert each cell’s reference into the formula and if you have a large number of cells or values it could prove to be a hectic task.

So in the interest of your precious time and manual work, we will write a small function using VBA to concatenate values from cells separated by comma or space or any kind of delimiter in between them.

Suppose we want to combine below data in column A in one cell separated by comma “,”

Output shows the value that we want in a single cell separated by comma.

Step 1

To concatenate values in a cell separated by comma or by any delimiter we will use a small VBA code to write a function.

Open VB Editor as shown in pic below.

You will see a screen like this-

Step 2

Insert a new module in which we will write a small function that will allow us to concatenate entire list in any column in a single cell separated by delimiter like comma, colon etc.

And you will see a screen like the one shown below.

Step 3

Paste the code below in the module as shown below.

Public Function Join(rng As Range, delimiter As String) As String

Dim cell As Range

For Each cell In rng

Join = Join & cell.Text & delimiter

Next cell

Join = Left(Join, Len(Join) – Len(delimiter))

End Function

We are creating a user defined function to concatenate a range of values into one cell in excel.

This function takes two arguments, the first one is the range of values to be concatenated and the second argument is delimiter like comma, colon etc. Kindly note that delimiter should be passed within double quotes.

We could use this function just like any other function in excel.