TEXTJOIN
share TWEET PIN IT share share 0

Tech Tips

Combine Text With Ease Using TEXTJOIN in Microsoft Excel

Let Excel do the work for you!

By Danielle DavisRoe

Book cover Microsoft Excel for Legal Professionals

Ever wish you could get past the basics in Excel and work a little bit smarter? Here’s a great tip on using TEXTJOIN, from Affinity Consulting Group’s newly updated guide “Microsoft Excel for Legal Professionals.”

If you’ve ever needed to combine text from multiple cells in Excel, you know how challenging it can be to work with the CONCATENATE function or to use ampersands to get the job done. It’s even harder if your data has some blank cells. Unless you jump through hoops to nest some formulas, you’re going to end up with double spaces that need to be removed. Enter TEXTJOIN, available in Microsoft 365 and Office 2019.

When You Need to Use TEXTJOIN

If you have rows of data that need combined (think a column for first name, a column for middle name, and a column for last name) and some of the cells are blank (not everyone has a middle name), then TEXTJOIN in Excel is the easiest way to get the job done.

TEXTJOIN is especially useful when you’re preparing data in Excel for a mail merge. Data is much easier to manipulate in Excel than it is in Word, so you want to account for things like missing middle names in Excel, not Word, whenever possible.

Excel Textjoin

How to Use TEXTJOIN in Excel

Start by making sure you’ve created a column for your newly combined text — Full Name in our example. Now, click in the first cell where you want the combined text to appear. In the example above, click in cell D2.

Functions, like TEXTJOIN, can be easily accessed from the Formulas ribbon. In the Function Library group, click on Text. Select TEXTJOIN from the drop-down list. TEXTJOIN has 3 unique arguments (information it needs to work): Delimiter, Ignore_empty and Text. You can enter multiple text arguments.

Delimiter

A delimiter is a character (such as a space, comma or period) that separates different pieces of text. In our example, where we’re combining first, middle and last names, we want to use a space as a delimiter. That is, we want a space between the first and middle name and a space between the middle and last name.

Click in the delimiter box and tap the space key on your keyboard to insert a space. When you click out of the delimiter box, quotation marks will automatically appear around your space.

You do not need to type the quotation marks yourself.

Ignore_Empty

By default, Excel TEXTJOIN will ignore empty cells — ensuring that you don’t end up with double spaces. You can leave this argument blank.

TEXT1, TEXT2, TEXT3, Etc.

When you first open the Function Arguments dialog, you have only three arguments (Delimiter, Ignore_empty and Text1). As soon as you click on Text1, Text 2 appears. When you click in Text 2, Text3 through Text252 will appear.

Start with Text1. Click in the box for Text1, then click on the cell with the contents you want to start with. In our example, click in cell A2 to bring in the first name.

Repeat this process for each cell with content that you need to combine in the order you want them in. In our example, click in Text2, then click on cell B2, click in Text3, and then click on cell C2.

Excel TEXTJOIN

Once you’ve set up all of your text arguments, click OK.

Replicate the Excel TEXTJOIN Formula for the Next Rows

Now that you’ve done the hard work for your first row, it’s time to replicate it for the remaining rows. Click on the cell with the full name. Then double-click on the fill handle in the bottom right-hand corner of the cell to autofill the formula down the column.

You can read more about how to use the autofill handle here, in “How to Master Autofill in Excel.”

About Affinity Consulting Group

Affinity Consulting Group inspires, enables, and empowers legal teams of all sizes to work smarter, from anywhere. The company’s holistic approach incorporates people, process and technology. Affinity’s passionate, well-connected industry experts work hand in hand with you to help you better understand and optimize your business — from software to growth strategy, and everything in between.

In this tips series, experts from Affinity Consulting Group offer straightforward answers to common questions about popular software programs used in law offices. These tips are from their book “Microsoft Excel for Legal Professionals. It is is an easy read, full of numbered steps and screen illustrations. Download your copy of the book in the Attorney at Work bookstore, or opt for a license for all your attorneys.

©iStockphoto.com

More Microsoft Office Tips:

“How to Master Page Numbers in Microsoft Word”

“Using Styles to Create an Automated Table of Contents in Microsoft Word” (Video)

“Tricks for Reading Documents in Microsoft Word”

“How to Master Autofill in Excel”

share TWEET PIN IT share share
Danielle Danielle DavisRoe

Danielle DavisRoe is a senior consultant with Affinity Consulting Group’s legal document automation team (@affinitylegal). Whether it’s teaching clients a new skill, writing a macro to automate a complicated task, fixing a document that doesn’t work properly, or automating a complex set of documents, Danielle is 100% focused on making the lives of her clients better. She has a Bachelor of Science in Business Administration from The Ohio State University Fisher College of Business and a Juris Doctorate from The Ohio State University Moritz College of Law.

More Posts By This Author
envelope

Welcome to Attorney at Work!

Sign up for our free newsletter.

x

All fields are required. By signing up, you are opting in to Attorney at Work's free practice tips newsletter and occasional emails with news and offers. By using this service, you indicate that you agree to our Terms and Conditions and have read and understand our Privacy Policy.