Recently Microsoft released a brand new function called CONCAT. It's supposed to replace its predecessor CONCATENATE. But how does it work? And what's new? In this article, we take a closer look at all of this - we even included an exercise file for you to try out.
The release of Excel 2016 brought with it some very interesting new functions and formulas, one of them being an overhaul of one of our old friends - ‘CONCATENATE’.
The new ‘CONCAT’ function is now the go-to for all of your text string combining requirements. Although the ‘CONCATENATE’ function is still available for backward compatibility, you should consider using ‘CONCAT’ going forward as the latter may not be available in future versions of Excel.
WHY THE CHANGE??
What could be possibly changed about such a straightforward function like ‘CONCATENATE’?
Well, for starters it’s shorter and easier to type, which is always a good thing. Over and above that though, it supports range references in addition to cell references. This will be explained further below, but for now let’s take a closer look at a refresher on what ‘CONCAT’ actually does and how it performs along with some helpful syntax tips and tricks.
‘CONCAT’ is a text function that allows you to automatically combine text from multiple cells, ranges (which is the main new feature of the upgrade), and/or strings. So… what does that actually mean?
Well… say you have a data file with 2 columns: First Name and Last Name. As you can see, the first and last names are in their own individual cells. What ‘CONCAT’ can do in its simplest form is combine the text from the two initial cells and link them together into one string of text in a single cell. This would be helpful in a real world scenario where your source data file included first name and last name in separate cells, and your dashboard report required the name to be in one cell.
HOW DOES IT WORK?
The syntax for ‘CONCAT’ is very simple.
Yep – that’s it.
So far it's exactly that same as its longer spelled predecessor ‘CONTATENATE’’. In a nutshell, ‘text1’ would be the first item to be joined - whether it be a cell (A23), a range (A1:A23) or a string (“I love concatenation”).‘text2’ would be the second item to be joined, and so on. Excel maxes out at 253 text arguments… although hopefully you never have to deal with a requirement to concatenate 253 items into one cell.
Here are some simple examples of the 3 different options available for the various arguments you can use within ‘CONCAT’:
TIP - Remember, a space can also be joined into the result since it’s technically a string (“ “). Keep this in mind when concatenating cells. You will use “ “ as an argument in ‘CONCAT’ in most instances… because weusuallydon’typeorcompose
=CONCAT("There were ",A2," events today.")
TIP – Remember the spaces! Notice the space after “There were_” and “_events today”.
This was the new functionality added to ‘CONCAT’ (go ahead, try to use ‘CONCATENATE’ and use a range as an argument… I totally tried myself as well. It simply concatenates the first cell only). However… in full transparency, I can’t think of an instance where I would ever want to concatenate a range of values especially since in most cases there would be no spacing between the text. Although there must’ve been enough of a need for Microsoft to build this enhancement into Excel 2016 so I will leave it at that.
‘CONCAT’ IN THE REAL WORLD
Now that you have a better understanding of what ‘CONCAT’ does and how it is used, we’d love to show you a “real world” scenario where this function could be used to solve a lot of manual work for a client or end user.
A great example of this would be a situation where you have a bunch of raw data exported into Excel from some other source, and you need to somehow transpose the data into a single cell on a reporting dashboard. And there’s no way you want to type it manually.
If you open up Project-File---CONCAT.xlsx
– you will see the raw data on the left and the dashboard on the right. ‘CONCAT’ has been used to bring in the Representative Name using cell and string references (the name cells and the comma string). It has also been used to bring in a detailed Performance Review by concatenating numerous cells along with some text strings with the correct spaces and punctuation used. (Notice the “.” to add the period at the end of the sentence.)
WRAPPING IT UP
Hopefully you now have a better understanding of what the newly created ‘CONCAT’ can do for you. At its best, it can really be a valuable time saver when trying to avoid manual data entry along with reducing manual typing errors.
And hey… the shorter spelling of the new function in Excel 2016 is a win all in its own!
I'm right here to answer them :)