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:
The CHAR function returns a character that corresponds to the number in the character set used by your computer (ANSI for Windows). There are up to 255 different characters in the set, with the capital letters from A-Z starting at number 65. Z is 90. To generate a random letter you can use the RANDBETWEEN function:
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:
We often need to measure the length of a text, for example if we write a text that will appear on a website. Typically, in a web shop the product introduction text has to be of a certain length in order to fit within the frames.
Of course, the easiest way to do this is in Excel. This is extremely easy – it only takes a few seconds: