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.

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.

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.”

bout 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

The post Combine Text With Ease Using TEXTJOIN in Microsoft Excel appeared first on Attorney at Work.

Did you miss our previous article…
https://www.itcse.org/?p=511

Randy Reid