Many people have recently, or will be in 2021, upgrading to Excel 2019 or Office 365.
And, I’m personally excited for some of the new features – some of them are just time-savers – and you know I’m all about time management and productivity, so any time you can save time typing a formula in Excel is time saved in your life to do the same thing that used to take longer – love it!
The first one that I’m showing here, to me, is huge for me personally, but I know many of my clients will love this because it’s a Function that is not only a long name, but difficult to type – not that people can’t spell it, but typing it is difficult – and those truly are 2 different things.
=CONCATENATE is now =CONCAT
But, not only has =CONCAT replaced =CONCATENATE, it now has additional capabilities. Instead of joining single cells into one, the =CONCAT function combines the text from multiple ranges and/or strings.
NOTE: =CONCATENATE will remain available so that it is compatible with previous versions of spreadsheets.
The Syntax of the function is as follows:
=CONCAT(text1, [text2], …)
For example, =CONCAT("The"," ","sun"," ","will"," ","come"," ","up"," ","tomorrow.") will return The sun will come up tomorrow.
Now let’s take a real-life example from customer data where the customer address may be located in multiple cells or columns, but you want the entire address to appear in one cell so that it can work with mail-merge for printing and sending mailed letters to your customers.
Columns C-I include the various fields of the mailing address.
To merge them together into column H, we’ll write the function in Column J as this:
=CONCAT(C2:I2)
You can click and drag to select Cells C2 through I2 or type it.
Something to notice with this example.
First, this would not make sense for a mailing address because the text does not include spaces between the address parts and it would not be able to be read by the post office, right?
However, the first thing I notice when looking at new functions is that if I do this same thing when using =CONCATENATE, I receive the #VALUE! Error message.
If I type the function as this:
=CONCAT(C5," ",D5," ",E5," ",F5," ",G5," ",H5)
I receive the same clear mailing address, blanks included, as I would when using the =CONCATENATE function. So, I saved a bit of time by not typing the entire word “CONCATENATE,” but nothing changed about the formula syntax.
However, here is where =CONCAT becomes more powerful.
If you have data in various columns that would help you be able to tell a story, then the =CONCAT function can truly pull your data together in a meaningful way.
I know in the manufacturing business or other businesses that use key codes or long numbers where each part of the number has specific meaning, the range piece of the =CONCAT function can truly be helpful.
I encourage you to start using this new function =CONCAT to see how it can help you – does it simply save you time? Does it give you more powerful ways of merging data?
Let me know how you use it; I’d love to see it!
If you're interested in an entire course on Excel Pivot Tables, Analyzing Data, Macros, and more, CLICK HERE for a 50% discount.
