I wrote an article a few years ago about how you can join data from different columns, and add a comma between each part. It was quite tricky, especially if we had some empty cells, so we ended up with a long formula with SUBSTITUTE, TRIM and CONCATENATE.
If you have Excel 2019 or Office 365, there is an easier way: The TEXTJOIN function.
Here’s the same dataset that I used in the previous article, and the result we want in the column to the right:
…and this is the formula we ended up using back then:
=SUBSTITUTE(TRIM(CONCATENATE(A2,” “,B2,” “,C2,” “,D2,” “,E2,” “,F2,” “,G2)),” “,”, “)
(CONCATENATE to combine the cells, TRIM to get rid of excess spaces, and SUBSTITUTE to remove excess commas)
It’s a lot easier now, if you have a new version of Excel. The TEXTJOIN function takes care of everything!
The TEXTJOIN function has three arguments: 1) Delimiter, 2) Whether or not you want to ignore empty cells in your range, and 3) the range.
In our example, we want to have a comma and a space as delimiter, so that’s what we type first, between double quotes (“”). Since we want to ignore empty cells, the second argument is 1 or TRUE (0 or FALSE if you want to include empty cells), and the last argument is the cells we want to join, A2:G2:
More on text functions in Excel:
- 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 in Danish, Dutch, Finnish, French, German, Hungarian, Italian, Norwegian, Russian, Spanish and Swedish.