join cells in excel

How to Join Cells In Excel with a Comma (or other delimiter)

Joseph Excel, Formulas, VBA 0 Comments

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.

The Goal

If you had cells A1:A3 like so:

Join Function - setup

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 (&):

Join - Using &

However, this can become a lot of typing, especially if you have many cells you need to put together.

typing

We can make this easier by creating a custom function in VBA that we can use in our spreadsheet.

Join - Using VBA function

Note
There 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:

Join - Using VBA function

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:

=Join(A1:A3,",")

Here would be the breakdown of how the function works.

A1

At this point, Join = "", cell.Text = "one", and delimiter = ",".

So this line:

Join = Join & cell.Text & delimiter

Turns into:

Join = "" & "one" & ","
Join = "one,"

A2

Right now, 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,"

A3

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”.

Also, the 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:

Join Cells in Excel - Using VBA function

And you’re not limited to a single character for a delimiter. You can have an entire word if you want:

Join Cells in Excel - word delimiter

Resulting in:

Join - word delimiter

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?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!