TEXTJOIN function makes list building easy

Technology: Office 365 as of 2020

Microsoft introduced a new function which makes combining the contents of cells even easier than before. In addition to concatenating, or joining, the contents of cells the TEXTJOIN function also allows you to insert a delimiter between each item. This makes it more powerful than the old concatenate formula.

The ability to add a delimiter is what makes this function very useful. In the past we had the CONCAT and CONCATENATE functions which would join together text however, depending on the application, you usually need some character between your values.

Here’s a common example. Pasting a list of email addresses from Excel into an email in Outlook. The application expects a semicolon or comma between each email address or name, however you may not have included these characters in the list in Excel as it wouldn’t make sense to store this with the data.

If you require functionality similar to the old CONCATENATE function you can set the delimiter to “” which joins the text together without any character between each item. There is also the ability to include or exclude blank cells in the cell range from the results. If you need to do this depends on your use case.

The syntax is =TEXTJOIN([delimiter], [ignore blank cells], [cell range])

Where:

Delimiter is any character you want between your list. The character need to be enclosed with quotes, i.e. “;” This is required.

Ignore blank cells is either True or False depending if you want blanks included in your list. This is required

And the cell range is your list items you’re wanting to join together. You can also have multiple ranges in this field if you need them.This is required.

Let’s look at some examples

This first example presents a case where we want to email some individuals, to put their email addresses in the To field of our email client we need them separated by a semi-colon.

This next example provides a result similar to the old concatenate function. In this example the list of numbers is joined together without any delimiter.

The TEXTJOIN function saves a lot of time for those of us who use Excel to work with data needed in other applications. The power of adding a delimiter means you can quickly transform a list stored in a cell range to suit the needs of your application. It’s a simple & powerful formula to add into your repertoire.

Do you want to get started with VBA? Want to learn how to automate your financial workpapers? 

My book, Beginning Microsoft Excel VBA Programming for Accountants has many examples like this to teach you to use Excel to maximize your productivity! It’s available on Amazon, Apple iBooks and other eBook retailers!

Learn More:

Example Workbook:View Workbook on Microsoft OneDrive

TextJoin Function: TEXTJOIN at Microsoft Office Support

9 time-saving tech tips for the busy CPA

In the average day you probably spend at least an hour or more doing the various steps to accomplish one goal. The time you spend highlighting a sentence in order to paste it into google. The time you spend finding file paths so you can share it with a colleague.

These mini-tasks are not only tedious they waste precious seconds which add up to minutes. Combine with with the few minutes it takes us to switch between tasks, and you’ll see how it adds up quickly! The only solution is to best optimize your workflow so that you reduce the number and time of these mini-tasks.

A quick Google search will reveal thousands of articles on this topic. Although many of them have the same suggestions. However this particular article has some unique points and is well worth a read. One that I find really useful, and I didn’t know until I saw it here, is to use the Copy as Path option.

In Windows, to copy the path of a folder (such as M:\Important Stuff\Firm Holiday Schedule.docx), hold down the Shift key and right-click the file or folder that you want and select Copy as path. The path will be copied to your Clipboard, and you can then paste it where you need to. This tip can also come in handy when you’re attaching files to emails or filling in dialog boxes.

Another is to use MS Office’s built in PDF functionality. I’ve used this for a long time and I prefer it to using Acrobat Pro’s PDF printer or even other many of the third-party utilities. The Save As PDF feature is much less prone to crashes and faster than the apps out there. Plus it’s already built in to the app so there’s no additional IT cost.

We’re always going to be juggling tasks in an effort to get through the day. It’s a part of our job, but it’s something we need to master. By taking every available shortcut we can maximize our own productivity and take back our minutes.

Source: 9 time-saving tech tips for the busy CPA