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

 

Posted in ,

Jeff Lenning

I love sharing the things I've learned about Excel, and I built Excel University to help me do that. My motto is: Learn Excel. Work Faster.

Excel is not what it used to be.

You need the Excel Proficiency Roadmap now. Includes 6 steps for a successful journey, 3 things to avoid, and weekly Excel tips.

Want to learn Excel?

Our training programs start at $29 and will help you learn Excel quickly.

8 Comments

  1. Janice on July 2, 2019 at 8:44 am

    Thank you for this information. I can’t wait to use this function in some of the reports I prepare monthly.

    • Jeff Lenning on July 2, 2019 at 8:45 am

      Awesome 🙂

  2. FRANCISCO on July 2, 2019 at 3:31 pm

    Hello,

    I have a question. Usually, you need to use a space ” “among the data, does concat do it automatically? and if that, what if I need the data without spaces between.

    Thank you

    • Paul on January 29, 2020 at 11:33 am

      He (Jeff) has another article called “Concat or Textjoin” that will answer this question.

  3. Alan on July 24, 2019 at 10:21 am

    Thanks Jeff!

  4. Donna on August 22, 2019 at 1:31 pm

    Francisco, I believe the spaces are typed into text cells B9, B11 and B13 but.not in B10 or B12 as they would reference cells B5 and B6. Must be why Jeff splits up the letter between cells rather than just typing the entire thing in one cell; there’s probably no way to use a cell reference in the middle of a test string. For data w/o spaces, don’t type them in.

  5. Kellie on July 14, 2022 at 5:13 pm

    Hi there! Is there a way to format within the concat function? For example if my concat formula is pulling a cell with a date that is in MM/DD/YY format and I want it to be in long form for this particular text string, can that be done?

    • Jeff Lenning on July 15, 2022 at 8:27 am

      Yes, you’d use the TEXT function. For example, if the cell reference you are concatenating is A1, instead of using A1 in your concatenation function you would use TEXT(A1,”mmmm d, yyyy”). Hope it helps!
      Thanks
      Jeff

Leave a Comment