Excel Zoom

...because it's more than just a calculator


Introducing the TEXTJOIN function in EXCEL 2016

by 1 Comment

Introducing the TEXTJOIN function in EXCEL 2016

We are familiar with CONCATENATE function that we have been using for a long time to perform concatenation or to join different strings or numbers. This function has been preceded by introduction of a new function in Excel 2016 –the TEXTJOIN function (and also CONCAT function).

The CONCATENATE function suffers from following limitations:

  1. Arguments – separated one by one by means of a comma.
  2. It can’t take ranges to perform concatenation.
  3. It has no provision to add delimiter

With these entire disadvantages, a user working with version earlier then Excel 2016 is left with little or no option to efficiently perform concatenation. Perhaps the best option has been to adopt some VBA code from internet that has all these provisions.

The TEXTJOIN Function – Syntax:

The function takes following arguments:

textjoin_syntax

Setting Delimiter:

“Delimiter” is a character that is inserted between texts being joined. For example number first name and second name of a person can be joined together with a  delimiter “_“ or underscore to make the identification of names easy and also to avoid space in the string.

In this example underscore was a delimiter and the text being joined was first and second name.

Ignoring empty cells:

It is understandable to avoid empty cells while concatenating ranges. They add extra space to the text and are unwanted. We can set the option to avoid empty cell by having TRUE set for this option and FALSE if we want to include empty cells as well.

Text to be joined:

The text to be joined could be individual cells or a range of cells. Either can be opted and will be joined. But there must be at least one cell to be selected. For this reason, third argument of the function is required. From fourth, the use of options is at user’s disposal (not mandatory).

And we can have at maximum 252 such arguments that can be joined.

Examples:

Since we have explored the syntax, we will start with examples to see how this function is used.

Example # 1

Often we need to create ids that are unique by any means. We need them to make identification of unique.

In the following example we have the first name, second name and the state columns available, we can use TEXTJOIN function to create a unique identifier for each of them. The formula takes underscore as delimiter, set second argument to TRUE so that no blank cells are shown and the range A2:C2 is taken as range to be concatenated.

textjoin_example_1

Drag down the formula and you will have you joined text.

Example # 2

In this example we have will try to use the TEXTJOIN function twice. The first instance will be the one when we have a particular state meet as criteria. This will result in the full name of the person as we will drag down the formula, the entire column will be filled with the complete name. If the name of state is different from the criteria, we will have a blank cell.

In the next step we will be using TEXTJOIN function again to get the list in to a cell that is comma separated. In order to do so, we will write the following formula in cell B2.

=TEXTJOIN(“,”,TRUE,D5:D13)

As shown in the following picture:

The result will be a comma separated list of the names as shown below:

The blank cells will be omitted from the joined string as we have set the second option to TRUE.

Example # 3

We can use TEXTJOIN to produce auto complete sentences. For example we can use it to write comments to a marks sheet that how much marks (s)he has obtained or so on.

In the following example, we will be creating a LOOKUP table to find the corresponding grade of the student and will return remarks by making use of TEXTJOIN function. The following table contains a lookup range that will be used to find the interval the marks fall in, and the last column will be used to return the grades.

The lookup formula that we have set is following:

=LOOKUP(F4,L5:L8,N5:N8)

As we know that LOOKUP takes three arguments at least, the first being the lookup value, which is F4 in our case. The second argument is lookup range i.e. L5:L8 in our formula and the last one is the return range that is N5:N8 in the formula. Thus the formula looks for the marks in the first column (Column L) and returns the value from Column N.

The formula picks up information from the following table located between E3:F5.

The lookup formula discussed in preceding paragraph is present in cell F5 and returns a grade C for marks 34. Now we will use the TEXTJOIN function to produce a comment that can be placed in such reports.

Let’s assume we want to have sentence like this:

[Candidate name] you have scored [marks], and your grade is [Grade].

The formula that we will be using is:

=TEXTJOIN(" ",TRUE,F3,"you have scored",F4,"marks, and your grade is",F5,".")

In this formula:

  1. The first argument is the delimiter that is set to a space (“ “) . This will give it the look of a sentence.
  2. The second argument is set to true as usual.
  3. F3 is the is the name of the candidate.
  4. The next part is “you have joined” that will remain same for each case of the student.
  5. This is followed by F4 that holds marks for the student.
  6. The next argument is a gain a fixed part that will remain same for every case.
  7. The (second) last argument is the grade that is present in cell F5.
  8. The last argument is a full stop mark that will end the sentence

Example # 4

In this last example, we will be producing list of students that fall in a bracket of marks. This is perhaps the extension of example # 02 that will show how to produce multiple lists of string by using such conditions.

Let’s assume that we have name of students and the brackets for marks as follow:

Again we have marks and brackets of marks that will be used to create lists depending on the marks. We will use following formula to put names in column according to marks criteria:

=IF(AND($B4>=C$2,$B4<=C$3),$A4,"")

The AND function is use to determine that two condition are true at the same time and then the formula will put either the name from cell A4 or a blank space in the column. The final sheet will look like (after we have populated the formula in entire range) like this one.

Now we can use TEXTJOIN function to join names of student in each column

Conclusion:

So we can see how useful this function is. We can use it for various purposes. It can be more useful if used with array formula. Please download the file from this link and see how the formulas are actually setup.