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.
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-
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.
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
Join = Left(Join, Len(Join) – Len(delimiter))
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.
Now our VBA part is completed, let’s see how we could use this function to concatenate a range of values with a separator between them.
As you could see below, i have used this function in an excel cell just like a normal function in excel.
I have passed the second argument i.e. comma within quotes.
You could use any delimiter in place of comma.
Hope this helped.