Post navigation

Join cells in Excel and remove excess commas between elements

Unclean data can cause a lot of problems in Excel. In this post I will show how you can join data from different columns with a comma between them. That’s the easy part. The problem occurs when you have empty cells in your data, like in the table below. The result of the first row looks fine, but if you look at the rows with empty cells, you get too many commas:

Joining the strings

First, let’s look at how we join the strings. We’ll deal with the commas later.

There are two ways to do this, either with the CONCATENATE function, or with the Ampersand (&)operator. With Ampersand, you start with the first element, add the the “&”, type the next, add another “&”, and so on:

=A2&B2&C2&D2&E2&F2&G2

The result of this formula is “SmithJacobHRBlue1ANortheastBuffalo”, so we have to add the commas (and a space) between the cell references. The comma+space is a text string, and has to be wrapped between double quotes:

=A2&”, “&B2&”, “&C2&”, “&D2&”, “&E2&”, “&F2&”, “&G2

The second way to join cells is the CONCATENATE function. Instead of all the Ampersand symbols, you simply start with the function name, and separate the elements with a comma (or semicolon for some international Excel users):

=CONCATENATE(A2,”, “,B2,”, “,C2,”, “,D2,”, “,E2,”, “,F2,”, “,G2)

Removing the extra commas

As we saw on the picture above, this works fine as long as there are no empty cells in the raw data. However, that is rarely the case, and if we don’t want the extra commas in our result, we have to adjust our formula a little bit.

The TRIM function in Excel can be used to get rid of excess spaces in a text string. Unfortunately, there is no function that removes excess commas, so we have to start with a string with spaces instead of commas, then remove excess spaces, and finally replace the spaces with commas. Here are the 3 steps:

Step 1: Join the cells with a space between each cell (The CONCATENATE part)

Step 2: Wrap the TRIM function around it to get rid of excess spaces

Step 3: Wrap the SUBSTITUTE function around that to replace the spaces with commas

We end up with this formula: =SUBSTITUTE(TRIM(CONCATENATE(A2,” “,B2,” “,C2,” “,D2,” “,E2,” “,F2,” “,G2)),” “,”, “)

If you have your data in A2 to G2, this should work:
=SUBSTITUTE(TRIM(CONCATENATE(A2,” “,B2,” “,C2,” “,D2,” “,E2,” “,F2,” “,G2)),” “,”, “)
But you might want to try to type the formula instead of copying it. The quotes can cause problems.
Also, if you use a non-English version, you might need to replace the commas between the formula arguments with semi-colons.

The IF statements checks cell A2. If there is no value (=0) in the cell it returns “”. This returns nothing and doesn’t generate a space at all. If there is a value in the cell it returns the value of A2 and includes “, ” as a suffix.

Repeat for the remainder of cells you would like to include and leave the suffix out of the last IF statement.