Beyond Numbers

Often when we use Excel, we think of its ability to operate on 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.

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

The CONCAT function essentially 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.

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!

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 O365 user, then you have a subscription license. The subscription license receives 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.