Beyond Numbers: Excel Tips and Tricks: Joining Text.

• Author: Lenning, Jeff
• Position: Tech Talk

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