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