Concatenate the contents of cells
- Dec. 23, 2018
 | Example files with this article: |
Introduction
It's been a while, but finally I found the time for a new article and an example file (at the top of the page). In the attached file you will find a good number of smart solutions, but the main purpose of this article is to show how you can incorporate the contents of different cells in an overview in 1 cell, such that you can copy/paste that overview.
The case goes as follows. Every day I have to report on the number of hours I spent on 2 different projects. The output is always identical, only the number of hours for both projects changes. To ease thios job I created a small Excel file:
We can see:
- today's date (with a formula, no input)
- how many hours were spent on both projects, before today, today, and the running total
- so the yellow cells are input cells
The output that we desire spans the hours spent on both projects, in the past, today, and the total. We need to break it down by project. The aim will be to generate the textual overview below:
So we would like to gather the text from the cells in the table. We will merge different lines of output with a carriage return character: whenever we copy/paste that cell in an email or other application, then the output will be presented as different lines below each other:
Here I pasted the output in a new email in Outlook. As a side remark, we can easily automate Outlook VBA from within Excel, and for example we can start a new email whereby the output is shown in the body of the email. Of course this example is not as it is in reality, since I report the hours worked in an Excel file on SharePoint.
You might ask yourself how we could have merged the different cell contents within a single cell ? To this end I use a function called TEXTJOIN. Attention ! This function was only introduced starting with Excel 2016. So if you happen to use an older copy of Excel, you will not be able to use it. You will have to resort to clumsy workarounds or VBA itself. Now the function is an integral part of Excel itself.
The function allows to indicate which cell contents to merge, with whatever separator you want. Optionally, but rather useful, you can exclude the empty cells. Now this has been the direct reason to write up this article. In the past we had to resort to a lot of IF-formulas, helper cells, VBA-code, for what is now 1 simple function.
=TEXTJOIN( CHAR(10), TRUE, A2:A10 )
A2:A10 are the cells whose contents you want to merge. I advise you to inspect the exmaple file. It is possible that columns A and B are hidden - you have to unhide them. CHAR(10) is the carriage return, put differently, 'start a new line in the output'. TRUE designates the wish to exclude the empty cells. In our case, how can we get an empty cell ? Well, when a cell in the small table is 0 (no hours worked) we don't want it to appear in the output. We don't work every day on every project. Optionally, you can add a toggle to include or exclude the empty cells, should you want to.
The more simple ways to concatenate texts include: the CONCATENATE function or & (for examples, see the cells in column A).
So you can easily put that TEXTJOIN function in your sheet and when you are done updating the table, you copy/paste the output cell in a different program. Ready ! But in my file I went a step further. The green and white button automates a number of user actions:
- adds up the hours of today (in yellow) with the hours in the row above
- turns the hours in yellow to 0
- copies the output to the clipboard (then you don't have to copy yourself)
- I added a function in VBA to determine the numberformatting of different cells. The numberformatting is used to format dates and hours worked in the output just like they are formatted in the table
Right, this is another nice workbook with many tips and tricks. Play around with the hours and see how it changes the output. Copy the output to elsewhere. Send me an email if you would like to have a version of the file without VBA-code. The VBA-code here is not essential, it only speeds up our work even more.
I wish everyone a very nice end of the year 2018 and an even more fantastic 2019 !
