Combining Strings Of Data In An Instant
With the release of Excel 2016 (as well as Excel Online, Mobile and Android versions), Microsoft has added six new functions to the line up.
One of those brand-new functions is: ‘TEXTJOIN’.
Up until now, if we wanted to join text strings from a range of cells in Excel, we had to either select each cell individually within that range or we had to be highly skilled at developing custom functions.
The former could become extremely tedious, mundane, and inefficient if the range of cells grew very large while the latter was simply not feasible for most users short of a good knowledge of VBA.
“One of the most powerful benefits of the ‘TEXTJOIN’ function is that it allows us to select an entire range of cell references to be joined. This is a huge productivity-booster!”
For this discussion, we will be working in Excel 2016 for Windows.
How combining strings was originally done
Formerly, there have been two predominant methods for joining text strings or cell references with strings in Excel.
You could either join using the ‘&’ like this:
In this case, we have joined the strings for “First” and “Last” names while placing a space (“ “) between the two using ‘&’.
The other method is by using the ‘CONCATENATE’ function:
As you can see, this isn’t really much of an improvement from an efficiency or flexibility perspective.
It is simply another way to do the same thing without any other real benefit.
We no longer have to type a ‘&’ in between each string to be joined, but we still have to refer to each individual string or cell reference.
“One of the major improvements TEXTJOIN offers is the flexibility to use cell ranges rather than individual cell references. So let’s get into it!”
Beyond the limits of ‘CONCAT’
There is a lot of value to be had from ‘TEXTJOIN’ that is likely to make it far more useful and popular than ‘CONCAT’ (or the now obsolete ‘CONCATENATE’).
First, the inclusion of delimiter parameter, and second, the built in ability to ignore empty values.
“If we use a list of individual strings that we would like to join as a sentence we can simply select a space (defined by “ “ in the formula) as our delimiter.”
Therefore, we should get “This is what TEXTJOIN is capable of.” from the following:
Notice that there are no spaces included in any of the text strings enclosed in double quotes in our formula where we have input our text parameters. The space is supplied by the ‘delimiter’ parameter.
To demonstrate further, let’s take a look at the outcome if we replace “ “ with “-“ as our delimiter:
As we can see in the figure above, the function will now return an output of “This-is-what-TEXTJOIN-is-capable-of.”
Practical use of ‘TEXTJOIN’
Now that we have firmly established how the delimiter parameter is used in ‘TEXTJOIN’, let’s take a look at using the function in a more practical scenario.
We are looking at a file that has a list of first names, middle initials, and last names in separate columns.
We would like to combine, or join, all three into a single column we will call “Full Name”.
This should be pretty simple.
However, if we complicate things a bit by requiring a period (.) after each middle initial, we can actually see that the ‘TEXTJOIN’ function offers some really nice flexibility in doing so if we get a little creative.
“Something to point out here that is not initially obvious, is that the ‘delimiter’ parameter can actually be a range of cell references containing different delimiters to meet the specific needs of our solution.”
For the sake of contrast, let’s quickly take a look at joining our separate names into a combined full name with just a space between each string.
This example simply takes our earlier demonstration of how ‘TEXTJOIN’ works a bit further by showing it in action with cell references for the text parameters.
Also, notice that we are inserting a literal delimiter of “ “ in order to add a space in between each text string in our range. This works quite nicely for a solution to our problem except we still haven’t placed a period after the middle initial in our “Full Name” result column.
First, let’s take a look at how we will use a different delimiter after our second and third text strings in our range.
In a case like this where we need a space between the first and second text parameter and a period and a space between the second and third text parameters, we can simply type a space into a cell and period and a space in the cell next to it and use the range of the two cells as our reference for the delimiter.
That was a lot of information, so let’s just take a look:
First, let’s examine the formula for our “Full Name” column closely (listed in the “Formula” column). Notice that the big difference here is that our ‘delimiter’ parameter is the range A$8:B$8.
The dollar signs sitting in front of the row numbers simply indicate that these references are locked which means that as we copy the formula down the “Full Name” column, this range reference will remain A8:B8.
Notice that A8 contains a space (“ “) while B8 contains a period followed by a space (“. “).
These are the delimiters we want to place in the joined string in the same sequence based on their position in the range A8:B8.
“This illustrates how to move beyond the limitations of placing a literal delimiter in the ‘TEXTJOIN’ function and use cell references to create the kind of flexibility that allows for multiple delimiters.”
Taking it further
Taking this same scenario a little further just to see how powerful ‘TEXTJOIN’ is when used with other functions, let’s consider that our “Middle” column actually contains full name text strings.
However, we still would like to have our “Full Name” column only contain the middle initial.
We can definitely get that result if we make strategic use of the ‘LEFT’ function.
For anyone not familiar with the ‘LEFT’ function, its syntax is:
‘text’ is our string (or cell reference containing the string – middle name in this case) and ‘num_chars’ is the number of characters from the left most end of our string we would like returned.
In our case, we will select the number 1 for our ‘num_chars’ parameter since we only want the initial of the middle names.
“One major difference in our formula now compared to the previous example is that we will not be able to use a range of cells as our ‘text’ parameter in the ‘TEXTJOIN’ function. However, each of the other parameters will remain the same.”
Therefore, we list out ‘A2’ and ‘C2’ for the ‘text’ parameters for first and last names while in between we have ‘LEFT(B2,1)’ for the middle initial.
This is a great demonstration of how Excel allow us to simplify the needs of joining text strings with ‘TEXTJOIN’ while also allowing us to simultaneously extract parts of those strings as we require.
Ignoring blank cells
Another challenge that we may face when using ‘TEXTJOIN’ is the presence of blank cells in our range of cell references to be joined.
For example, what if in our original data for names there are some missing middle names on some rows?
“If we choose ‘FALSE’ for our ‘ignore_empty’ parameter, any of these rows with a missing middle name will result in a returned value with two spaces between our first and last names.”
To avoid this, we simply need to choose ‘TRUE’ as our ‘ignore_empty parameter.
Notice in the following figure that row 3 is missing a value in the “Middle” column and yet the result of the ‘TEXTJOIN’ function in the “Full Name” column is “Melanie Davidson”.
Another detail to consider is: With ‘TEXTJOIN’, our cell reference ranges to be joined do not need to be horizontal.
Vertical ranges work just as well:
Again, if we need to avoid the perils of blank cells in our range, we simply select ‘TRUE’ for our ‘ignore_empty’ parameter like in the following scenario.
Note the result of our ‘TEXTJOIN’ function with two delimiters between each joined text string (;;) where we did not require the function to ignore empty cells.
Microsoft definitely raised the bar with the addition of the new functions in the latest releases of Excel.
This is clearly evident as we have explored the power and flexibility available with ‘TEXTJOIN’.
“What used to be a cumbersome and inefficient proposition of joining text strings under the limitations of old methods is now one of the simplest and most powerful features of Excel.”
While we merely scratched the surface of what ‘TEXTJOIN’ is capable of, further exploration of the possibilities when applied to more complex scenarios will only serve to reveal the value of this simple function.