TEXTJOIN Function
Function that joins two or more text strings together with or without the help of a delimiter.
What is the TEXTJOIN Function?
The TEXTJOIN function in Excel joins two or more text strings together with or without the help of a delimiter.
You aren’t hearing about the term ‘delimiter’ for the first time. Simply put, it is just a character that separates two text strings. For example, a comma(,), a space character( ), a dash(-), an ampersand(&), etc., are all different types of delimiters that you will find in Excel.
You must be wondering - “Well, we already had the CONCATENATE function earlier, and another CONCAT was introduced in the newer version. So why was this function even needed?”
The two most reasons are the flexibility of the delimiter that can be used while connecting text strings and the ability to include/exclude the empty cells from the result.
We will delve more into this when we compare all three functions, but for now, let’s see the TEXTJOIN function, how to use it, and a couple of examples to understand it better.
Key Takeaways
- The TEXTJOIN function will join two or more text strings together with or without the help of a delimiter.
- The function has a limit of 32767 characters for the resultant text string. If the limit is breached, the function returns the #VALUE! Error.
- The function can accept 252 additional text arguments referencing a cell or range of cells in the spreadsheet.
- If you use the function in a workbook and open the same workbook in an older Excel version, you will receive the #NAME? Error for the function.
- The function allows us to choose whether to include or exclude the values from blank cells in the resultant text string.
- The CONCATENATE and CONCAT are the alternative functions to join two or more text strings or numerical values.
- A delimiter is a character or a string that separates two or more substrings.
TEXTJOIN function Formula
A TEXTJOIN is categorized as a Text function that will concatenate two or more text strings together based on our choice of delimiter.
Additionally, the function allows us to ignore the empty cells or include them in our results.
It is quite obvious from the name what the function does. For example, suppose you have two text strings, ‘Elon’ and ‘Musk.’ Then, using the delimiter as a space character, the entire text string returned would be ‘Elon Musk.’
Similarly, if you have one empty cell and another cell consisting of the substring “WallStreet,” the result obtained using the delimiter (-) will be ‘-WallStreet.’
A null value is assumed for all the empty cells; hence, we only see the delimiter and no additional text strings.
The syntax for the function is
=TEXTJOIN(delimiter, ignore_empty, text1, [text2])
where
- delimiter - (required) any character or a string that separates two or more substrings.
- ignore_empty - (required) determines whether the empty cells will be included in the result. If the boolean value is TRUE, the empty cells are ignored. Whereas for FALSE, the empty cell values are included in the result.
- text1 - (required) the first text string or the range of cells containing several text strings.
- text2 - (optional) the second text string or the range of cells containing text strings
Note
Using the function, you can input 252 additional text strings as arguments as text2, text3, text4…..text252. This can be either a single cell reference, a range of cells in a single spreadsheet, or different ranges in different spreadsheets.
How to use the TEXTJOIN Function in Excel?
Since the function has only three mandatory arguments, using the function becomes a lot easier. This section will show how we can use the function as a worksheet formula.
If you intend to become an Excel wizard, then using the function as a worksheet formula is the quickest and the easiest method. Besides, the flexibility it will allow you to combine multiple functions and nest them is better than none.
The steps that you need to follow are:
1. Selecting the delimiter
Earlier, we saw what a delimiter means, which is something that separates two text strings. The most common delimiter that we use in our day-to-day life is the space character.
However, the function is not limited to such punctuation characters as delimiters. You can even create customized delimiters, for example, abc, qwerty, etc., made of strings.
For now, we will keep things simple. Suppose you have the data in Excel as illustrated below:
Here, our delimiter is a dash character(-). We will reference the cell E3 in the formula so that it becomes =TEXTJOIN(E3, ignore_empty, text1, [text2]) in cell F3. You can also directly hardcode the delimiter inside the quotation marks.
2. Overthinking about the empty cells
Once the delimiter that needs to be inserted is sorted, you must determine whether to include the empty cells in the final result.
As you can see, no middle name is given in cell C3, so we can exclude it from the result directly., The boolean value to exclude the empty cells will be TRUE, which makes our formula =TEXTJOIN(E3,TRUE, text1, [text2])
3. Referencing the text strings
Finally, we will reference the text strings we want to combine. The text strings in question are in the range B3:D3.
The good thing about the function is that we do not need to input each cell reference individually as the argument. Instead, the formula becomes =TEXTJOIN(E3,TRUE,B3:D3), after which you can press the Enter key to get the result in cell F3, as illustrated below.
We get the result as Aemond-Targaryen, i.e., the first and last names concatenated together and separated by the delimiter.
TEXTJOIN Function Example
There are a lot of different scenarios where you can use the function to connect two or more text strings. This section will explore how you can use the functions to manipulate text and return the desired results in Excel.
a. Example #1: Combining First and Last Names
Suppose you have the first and last names in Excel, as illustrated below:
Here, we will use the formula =TEXTJOIN(“ “,TRUE,B3:C3) in cell D3 and drag it down till cell D11, which gives the result as
Based on the space character as a delimiter, the first and last names are concatenated together and returned as a single string in column D.
b. Example #2: Combining the full address
The address is one of the most recurring datasets you might come across, no matter what industry you work in.
Suppose you have addresses in the dataset, as illustrated below:
To get each component of the address concatenated and separated by a comma, we will use the formula =TEXTJOIN(", ",TRUE,B3:E3), which gives the result in column F as
We use a combination of comma and space characters as our delimiter, giving us the desired results in column F.
c. Example #3: Text strings consisting of date, time, and formats
There will be at least one con if the function has numerous pros. For example, if you reference an mm-dd-yyyy format date in the function, it returns the serial number counterpart for the same.
Similarly, if you reference a cell of the current time in Excel, the function will return the decimal number equivalent to the time.
How do we bypass this scenario?
Suppose you have the example illustrated below:
Here, we have a dollar sign before the price, date, and time values. If we directly use the formula =TEXTJOIN(" ",TRUE,B3:E3), we lose all the formatting for all the mentioned datatypes.
Instead, we can reference each cell separately and use the TEXT function to determine the format we want to return those values.
A bit complicated but repetitive formula becomes =TEXTJOIN(" ",TRUE,B3,TEXT(C3,"$0,000"),TEXT(D3,"mm-dd-yyyy"),TEXT(E3,"hh:mm")), which gives the result as
With the introduction of the TEXT function, we could retain the formattings and combine the text strings.
TEXTJOIN Function Practical Example
Previously, we saw simple scenarios concatenating the text strings together. This section will include only a single example of how you can use the IF statements and the TEXTJOIN function to return the text string.
Suppose your bank evaluates borrowers for loans based on their FICO scores. The borrowers must have a FICO credit score of more than 650 to avail of bank loans. The data is as illustrated below:
The customers to whom the bank cannot avail loans can be returned in the form of an array using the formula
=TEXTJOIN(",",TRUE,IF($C$3:$C$11<650,$B$3:$B$11,""))
And press the Ctrl + Shift + Enter key to get the result:
The formula returns an array of borrower names that have FICO scores below 650 in cell F5.
You get the idea of how TEXTJOIN with conditional statements will allow you to return two different outcomes based on what condition is fulfilled.
Also, remember to use the Ctrl + Shift + Enter key to get the result since this is an array formula. You might not see the expected result if you click on the Enter key for the formula.
TEXTJOIN vs. CONCATENATE Function
The CONCATENATE function joins two or more text strings, numerical values, or formulas in Excel.
The major difference between both functions is that CONCATENATE requires you to input the delimiter every time in the formula.
You can also use the ampersand sign(&) to join two text strings instead of using the function.
The syntax for the function is
=CONCATENATE(text1, [text2])
where
- text1 - (required) the first text string, numerical value, or the cell reference
- text2 - (optional) the second text string, numerical value, or the cell reference
Note
You can add up to 255 additional text arguments for the function. However, the number of characters should not exceed 8192, or Excel will return an error.
To compare how both functions work, let us see an example of combining the first, middle, and last names.
Suppose you have the data in Excel as illustrated below:
We will use the formula =CONCATENATE(B3," ",C3," ",D3) in cell E3 and drag it down till cell E12, which gives the result:
Alternatively, you can also use the formula =B3&" "&C3&" "&D3 using the ampersand sign. It will give you the same result.
On the other hand, the TEXTJOIN function using the formula =TEXTJOIN(" ",TRUE,B3:D3) will give the result:
You might have noticed that for the CONCATENATE function, you need to reference each cell individually, which is why the newer CONCAT function in the latest Excel version replaced the function.
TEXTJOIN vs. CONCAT Function
The new gen version for the CONCATENATE allows you to directly reference a range of cells as a single argument which previously wasn't possible in the older version.
The CONCAT function combines the text strings directly from the referenced range of cells.
However, the only issue that arises is using a delimiter to separate the text strings. If we directly utilize the CONCAT function on a range of cells, the text strings won't be separated by delimiters.
Then again, you need to separately input the delimiters between each substring or reference the cell with those delimiters.
To avoid such complications, TEXTJOIN was born, which takes care of all those things for you.
The syntax for the CONCAT is
=CONCAT(text1, [text2])
where
- text1 - (required) reference to a cell or range of cells consisting of text strings or numerical values
- text2 - (optional) reference to a cell or range of cells consisting of text strings or numerical values.
Finally, let's see how the results for functions differ to understand why TEXTJOIN is superior to CONCAT and will save a lot of time if used wisely.
Suppose you have the first and last names in Excel, as illustrated below:
To join the first and last names, we will use the formula =CONCAT(B3:C3), which gives the result:
As you can see, the delimiter is missing between the two substrings. Thus, to input the delimiter, we must reference the cells individually so that formula becomes =CONCAT(B3," ",C3) to give:
However, this limitation is removed by the TEXTJOIN function, which gives you a similar result using the formula =TEXTJOIN(" ",TRUE,B3:C3) to provide:
What function you use entirely depends on what scenario you are working on. For example, if you do not need the delimiter, you can directly use the CONCAT function or opt for the TEXTJOIN.
or Want to Sign up with your social account?