EDIT: If you are using Excel 2019 or Office 365 you can use the TEXTJOIN function to solve this problem. Click here for the new article:
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:
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)),” “,”, “)
More on text functions:
- How to Join Text from Several Cells in Excel using TEXTJOIN
- Convert text to number with formula
- Create random text strings and line breaks with the CHAR function
- Use Excel to Count Words
- Use Excel to count characters
Are you using a non-English version of Excel? Click here for translations of the 100 most common functions.