Beyond Numbers: Excel Tips and Tricks: Joining Text.

Often when we use Excel, we think of its ability to operate with numbers. But Excel is also good at operating on other data types, such as dates and text strings. When we limit our use of formulas to numbers only, we miss out on many opportunities for efficiency. In this article, I'll talk about one of my favorite text operations, and, how it just got way better in a recent update.

One of my most-used text operations is that of joining text values together. This operation is known as concatenation. Traditionally, concatenation was performed by using the CONCATENATE function, or by using the concatenation operator (&). Let's visualize this idea with an example.

Let's say we've exported an account list from our accounting system. The primary account is in column A, and the subaccount is in column B. We need to combine them into a full account and separate them with a colon, as shown in Figure 1, column C.

Figure 1

   A        B           C

1  Primary  Subaccount  Full Account
2  Cash     Checking    Cash:Checking
3  Cash     Savings     Cash:Savings
4  Cash     Payroll     Cash:Payroll

The following formula, written into C2, uses the CONCATENATE function to accomplish this task. =CONCATENATE(A2, ":", B2)

Or, we could use the concatenation operator (&) instead, as shown here: =A2 & ":" & B2

Both would join the values in A2 and B2 to create the combined, full account. We could then fill the formula down for the remaining accounts.

Figure 2

   A        B           C

1  Primary  Subaccount  Subaccount
2  Cash
3  Cash     Checking
4  Cash     Checking    Wells Fargo
5  Cash     Checking    BofA

While this would work if all accounts had a primary account and one subaccount, it wouldn't work if some accounts had multiple subaccounts, or, if some accounts had only a primary account, as illustrated in Figure 2.

Fortunately, in a recent update to Excel 2016 for Windows subscription license, we have two new functions, CONCAT and TEXTJOIN.

Figure 3

   A        B           C            D

1  Primary  Subaccount  Subaccount   Full Account
2  Cash                              Cash
3  Cash     Checking                 Cash:Checking
4  Cash     Checking    Wells Fargo  Cash:Checking:Wells Fargo
5  Cash     Checking    BofA         Cash:Checking:BofA

The CONCAT function replaces CONCATENATE, and Microsoft recommends using it going forward, as CONCATENATE is available only for backwards compatibility. The big improvement in CONCAT is that it accepts a range reference, rather than being restricted to single-cell references.

The TEXTJOIN function joins cell values and allows us to specify a delimiter, such as a colon. The first function argument is the delimiter, the second allows us to skip blank cells, and the third is the range of cells to join. For example, the following formula written in D2 would combine the accounts with a colon delimiter, and could be filled down to work for all rows: =TEXTJOIN(":",TRUE,A2:C2)

The result of this formula is illustrated in Figure 3, column D.

A quick note about Excel versions: Excel 2016 for Windows is offered with two licenses, a perpetual license and a subscription license. The perpetual license is the way we're used to buying Excel. Buy it once, install it and use it forever. If you're an Office 365 user, then you have a subscription license and will receive updates and enhancements, including new features and functions. The perpetual license doesn't receive new features and functions. So, if you're on a perpetual license, you may not see the CONCAT and TEXTJOIN functions, which were enhancements made in subscription licenses.

I use concatenation all the time in my workbooks, and these new options will enable me to use it even more often. I hope they will help you out, as well, and remember, Excel rules!

by Jeff Lenning, CPA, CITP

Jeff Lenning CPA, CITP, is author of Excel University (www.excel-university.com) and owner of Click Consulting. You can reach him at jeff@excel-university.com.

Article Details

Printer friendly Cite/link Email Feedback
Title Annotation:	Tech Talk
Author:	Lenning, Jeff
Publication:	California CPA
Date:	Oct 1, 2017
Words:	642
Previous Article:	REVENUE RECOGNITION: It's Here. Now What?
Next Article:	Apportionment: Calculating Community Interest In Separate Property.
Topics:	Spreadsheet software Statistics Spreadsheets Statistics