Included in the group of six functions released by Excel 2016 is a very valuable function; TEXTJOIN. This tool makes it easier to combine strings of data within minutes rather than having to select each cell individually or having to be extremely skilled at forming custom functions. TEXTJOIN has a number of noteworthy features, including the ability to select an entire range of cell references that must be combined together.

Before TEXTJOIN was released, the process of combining data was done by using two different methods, including the “& function” and the “concatenate function”, both of which are not used as extensively anymore. In the functions prior to the release of TEXTJOIN, you would need to make cell references to each cell or string. TEXTJOIN has improved this aspect of Excel by allowing the ability to utilize cell ranges rather than having to make individual cell references. The syntax for this function is:

“TEXTJOIN(delimiter, ignore_empty,text1,[text2],...)’

In the function, the delimiter has to be interested in the form of text string which can include a variety of characters or even refer to a valid text string by using double quotes. You could leave the argument of delimiter blank, which would simply concatenate the text in a single contiguous string.

If the “ignore_empty” argument of the function has been set to “TRUE”, then it will simply ignore all the empty cells found in the selected portion of text. The “text1” portion of the function is the text that must be joined together whereas ‘[text2,...]’ is the optional text which need to be added to “text1”.

TEXTJOIN has numerous features that make it stand out from CONCAT, which has now become obsolete. For example,  in  ‘=TEXTJOIN(“ “,FALSE,”This”,”is”,”what”,”TEXTJOIN”,”is”,”capable”,”of.”)’ you will notice that there are not many spaces included in the text strings that have been placed between the double quotes in the formula. Also, if you replace the “delimiter” parameter of the formula with “with “-“, then it will look like ‘=TEXTJOIN(“-“,FALSE,”This”,”is”,”what”,”TEXTJOIN”,”is”,”capable”,”of.”)’, and the results generated will appear in the following form:

Practical Uses of the Function

If you have a file in front of you that contains a list of last names, middle initials, and first names all in different columns, and you would like to combine them into column, then the process of combining them is pretty simple. You can combine the list of names in the following manner by utilizing the formula given in the column E.

You must notice that a literal delimiter has been added to ensure a space is inserted in between each text string. If you would like to add a period after the middle initial, then you can simply type up a space in the cell and a space and period into the cell next to it to make it look like:

This helps us understand how we can progress beyond the restrictions of placing literal delimiters in the newly released function of Excel and utilize cell references to make proper use of the flexibility that permits many delimiters.

Ignoring Blank Cells

Another difficulty that you might end up facing when utilizing TEXTJOIN is the presence of various blank cells in the range of cell references you would like to combine. If “FALSE” is selected for the “ignore_empty” parameters, then the results will show a returned value that has two spaces between the last and first names in the cells. In order to avoid this issue, you must select “TRUE” for the parameter. You will notice in Row 3 how the middle column does not have middle initial and the TEXTJOIN function has still yielded proper results of the full name.

Also, keep in mind that when you are using TEXTJOIN, you do not have to worry about utilizing horizontal cell reference ranges, because vertical ranges function just as well.

However, you must keep in mind that you must select TRUE for the “ignore_empty” parameter as shown below.

Without a doubt, Excel 2016 has taken its performance to a whole new level with releasing its new functions.