Join cells in Excel and remove excess commas between elements

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:

How to Join Text from Several Cells in Excel using TEXTJOIN

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:

EasyExcel_41_1_Join with concatenate and trim



READ MORE

Use Excel to Count Words

In Word you can easily count the words simply by clicking “Word Count” from the Review ribbon. But what if you have your text in Excel? There is no built-in word count function in Excel, so we have to find another way.

In a previous post I showed how to count characters in a text string using the LEN function. We will use the same function, with a twist, to do a word count.

READ MORE