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.
![]() ![]() ![]() ![]() | |
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: |