ATOI365D #Office365Challenge I’ve always been a huge fan of data clean-up formulas. LEFT, MID, RIGHT, TRIM, CONCATENATE etc. The January 2016 update had some updates around new functions and data entry.

Day:  327 of 365, 38 left
Tools:  Office 365, Office 2016, Excel
Description: Using CONCAT & TEXTJOIN in Microsoft Excel
Audience:  All

210117001

What does Microsoft Say? The CONCAT function combines the text from multiple ranges and/or strings, but it doesn’t provide the delimiter or Ignore Empty arguments. The TEXTJOIN function combines the text from multiple ranges and/or strings, and includes a delimiter you specify between each text value that will be combined. If the delimiter is an empty text string, this function will effectively concatenate the ranges.

This update was released January 2016. If you don’t see these updates in your Office 2016, speak to your Admin about the company’s release preferences on Office 365.

So the difference is that TEXTJOIN provides the delimiter. Another important thing to remember is that our machines work different when it comes to formulas. Commas vs Semicolons. My machine uses semicolons so I always have to change any examples I get on blogs, from commas to semicolons. This goes for calculated columns in SharePoint as well. Read this blog to refresh your memory.

Using these formulas:

CONCAT & TEXTJOIN will replace CONCATENATE (it will stay available for compatibility with earlier versions of Excel).

Below you’ll see examples of using TEXTJOIN. Pretty neat selecting a range and only stating the delimiter once.

2017-01-21-14_07_42-book1-excel

Here is an example of CONCATENATE. You still have to specify the delimiter between each field. When selecting a range – it will only add the text: I think I prefer TEXTJOIN.

2017-01-21-14_15_46-book1-excel

Happy joining 🙂

Overview of my challenge: As an absolute lover of all things Microsoft, I’ve decided to undertake the challenge, of writing a blog every single day, for the next 365 days. Crazy, I know. And I’ll try my best, but if I cannot find something good to say about Office 365 and the Tools it includes for 365 days, I’m changing my profession. So let’s write this epic tale of “Around the Office in 365 Days”. My ode to Microsoft Office 365.
Keep in mind that these tips and tricks do not only apply to Office 365 – but where applicable, to the overall Microsoft Office Suite and SharePoint.