CONCATENATE or CONCAT

Quick question: how do we combine or join values from multiple cells to create one big text string? We’ve been able to use the concatenation operator (&) or the CONCATENATE function for decades. The CONCATENATE function has been a great friend over the years, and has enabled some wonderful formulas. But, now there’s a new kid on the block: CONCAT. In summary, it is designed to replace CONCATENATE and supports range references in addition to cell references. Let’s check it out.

Note: CONCAT is available in Excel O365 and 2019+

Objective

Before we get to the details, let’s first have a concrete example of what it means to combine or join values with concatenation.

Let’s say we have a workbook that includes a bunch of financial reports. We need to print them and deliver them to our report readers, whoever they may be (client, boss, CFO, etc). In addition to the actual reports, we would like to include some type of letter or notes, for example, a cover letter, opinion letter, or some notes to the financials.

This letter could be written with Microsoft Word instead of Excel, and that would be just fine. But, if we were able to somehow use Excel to prepare the letter, then the entire report package is generated from a single place (instead of printing the reports from Excel, and then manually updating a Word document or using mail merge). And, since Excel has formulas, the letter could be automatically updated with selected values, such as the effective date, client name, or period. The benefits would be (1) the letter could be prepared more quickly (because it is automated with Excel formulas) and (2) the letter is more reliable (because when we manually update dates or values in a Word document there is a chance we will miss one of them somewhere).

So, what might this cover letter look like? Here is the first paragraph of an audit opinion letter that I’ll use to illustrate this idea and the CONCAT function:

We have audited the accompanying balance sheets of COMPANY XYZ as of December 31, 2030, 2029 and 2028, and the related statements of income, retained earnings, and cash flows for the years then ended.

I used bold font in the paragraph above to identify the values that can change each period. If you are familiar with Word’s Mail Merge feature, these would be the merge fields.

So, the overall idea would be to break the paragraph into each element on one worksheet, like this:

And then use concatenation to assemble them on the formatted sheet that you’d print, like this:

This “assembly” step is where we’ll use CONCAT because it joins individual values to create one big text string like the “We have audited … ” paragraph. Let’s get to the mechanics of CONCAT.

CONCAT

So, what is the deal with CONCAT? Well, in summary, it joins individual values to create a combined text string. This essentially accomplishes the same thing as the concatenation operator (&) or the legacy CONCATENATE function. But, there is an improvement: CONCAT accepts range references in addition to cell references.

CONCAT is designed to replace CONCATENATE, but, CONCATENATE is still available in Excel for backwards compatibility (so that legacy formulas that use CONCATENATE will continue to work). If you go to write a CONCATENATE function, it has a warning icon reminding you it is included for backwards compatibility, like this:

To quickly review, CONCATENATE enables us to specify each cell reference individually. So, if we wanted to combine the elements of our letter, we would write a formula like this:

=CONCATENATE(B9, B10, B11, B12, B13)

CONCAT not only supports individual cell references like CONCATENATE, we can also provide a range reference, like this:

=CONCAT(B9:B13)

So, when we write this formula, Excel combines each value in the range, and produces a big text string, like this:

We have audited the accompanying balance sheets of Company XYZ as of December 31, 2030, 2029 and 2028, and the related statements of income, retained earnings, and cash flows for the years then ended.

But, when we hit Enter, our paragraph is displayed on one line, and looks a bit like this:

There are a couple ways to address this formatting so that it looks like an actual paragraph. One fairly easy way requires two steps. First, make the column wider so that it is the desired width, something like this:

Then, we select the cell and click the Home > Wrap Text command. The result is shown below:

But wow Jeff, that was way more work than just updating the values manually. For the first period, yes. However, the benefit is that each subsequent period, we can update the values like client name and period in one place, and those updated values automatically flow to the letter and any other reports that reference those cells. This improves efficiency and reduces the risk of errors. So, for recurring use projects, this can be a wonderful approach.

If you have any other CONCAT tips, please share by posting a comment below … thanks!

Sample file: CONCAT.xlsx