There are times where I need to join cells in Excel and separate them with a comma or some other text. I usually need this to make a SQL query or something like this. Whatever the case may be, Excel doesn’t seem to have an easy way to join multiple cells with a delimiter like a comma. So I’ve created a very simple VBA function to help us with this task.
If you had cells A1:A3 like so:
And you wanted to join the text with a comma between them, one way you can do that is by using a regular formula where you concatenate the cells with ampersand (&):
However, this can become a lot of typing, especially if you have many cells you need to put together.
We can make this easier by creating a custom function in VBA that we can use in our spreadsheet.
NoteThere is no Join() function in Excel formulas, which is why we need to create it using VBA.
The function will result with the same output, but with less typing:
Let’s create the VBA function Join() so that we can use it in our spreadsheet to make this task a lot easier.
Join Cells in Excel with a VBA Function
Let’s write a quick function you can write to help you join multiple cells with a delimiter. If you’re not very familiar with VBA, please see my Intro to VBA article.
Option Explicit 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 ' remove the last delimiter Join = Left(Join, Len(Join) - Len(delimiter)) End Function
Let’s break this down to understand what’s going on.
Public Function Join(rng As Range, delimiter As String) As String
The function accepts a Cell Range as its first argument, then a string as its second argument. The function will result in a single String that will be returned to the calling Cell that uses the formula.
Here’s the next part of the function that actually builds the resulting string:
For Each cell In rng Join = Join & cell.Text & delimiter Next cell
Here we loop through each cell in the
rng variable and we start building the final string by using
Join =. Notice that we are using the name of the Function here. You can think of this as an implicit variable that Excel will use that gets returned back to the spreadsheet.
Join = Join & cell.Text & delimiter
Here, we build up the string cell by cell. If we had the cells:
A1 = one A2 = two A3 = three
And used this formula:
Here would be the breakdown of how the function works.
At this point,
Join = "",
cell.Text = "one", and
delimiter = ",".
So this line:
Join = Join & cell.Text & delimiter
Join = "" & "one" & "," Join = "one,"
Join = "one,",
cell.Text = "two", and
delimiter = ",".
The next time we go through the line in the For Each statement, it turns into this:
Join = "one," & "two" & "," Join = "one,two,"
And in the third time we go through the loop:
Join = "one,two," & "three" & "," Join = "one,two,three,"
Now the loop has finished, and we have
Join = "one,two,three,". However, we don’t want that last comma there, so we need to get rid of it. That’s when we execute the next line:
Join = Left(Join, Len(Join) - Len(delimiter))
Which turns into:
Join = Left("one,two,three,", 14 - 1) Join = Left("one,two,three,", 13) = "one,two,three"
If you’re not familiar with the
Left(string, length) function, it’s very simple. It will take a string you give it, and only keep the amount of characters you specify. So,
Left("one,",3) will take the string “one,” and only keep the first 3 characters (from the Left) and end up with “one”.
Len() function just figures out the Length a string has. So
Len("one") will return 3, for 3 characters. (Len is short for Length).
So why not just use Left(Join, Len(Join) – 1)?
The reason for this is if you don’t want a single character as a delimiter. Maybe instead of a comma, you want a specific piece of text between each string. For example, you could do:
=Join(A1:A3," and ")
Here, right before the last line we would end up with:
Join = "one and two and three and "
If we used:
Left(Join, Len(Join) - 1)
We would end up with:
Join = "one and two and three and"
Basically, we would only get rid of the last space at the end. This is why we should use the Length of the delimiter to remove from the end of the text. With that in mind, here’s what would happen at the end of the function:
Join = "one and two and three and " Join = Left(Join, Len(Join) - Len(delimiter) Join = Left("one and two and three and ", 26 - Len(" and ")) Join = Left("one and two and three and ", 26 - 5) Join = Left("one and two and three and ", 21) Join = "one and two and three"
Putting the New Formula to Use
To use the formula, simply type it into a cell like a regular formula:
And you’re not limited to a single character for a delimiter. You can have an entire word if you want:
I hope you found this post helpful. If you have any questions or have found a tweak to this function you’d like to share, I’d love to hear about it in the comments below!
Are you ready to master Excel Dashboards?
Still not convinced? Check out my review of the course!
Suggest the next post!
I would love your help to know what I should post about next, so if you enjoyed this content and would like to see more, please let me know what you'd like me to talk about in the comments below. Thank you!