TEXTJOIN Function
Joins text from two or more strings together with a given delimiter
Joins text from two or more strings together with a given delimiter
The TEXTJOIN Function joins text from two or more strings together with a delimiter separating each value. Introduced in MS Excel 2016, TEXTJOIN is categorized as a String/Text function.
=TEXTJOIN(delimiter, ignore_empty, text1, [text2], …text_n)
The TEXTJOIN function uses the following arguments:
Let’s see a few examples to understand how this function works.
In this example, let’s assume we extracted information from an address and the information is received in the following manner:
Using the formula =TEXTJOIN(“, “,TRUE, B5, C5, D5, E5,F5), we can join the text strings as shown below:
Now let’s assume that we wish to add the text – “Country” before US then in such scenario the formula would change to =TEXTJOIN(” “,TRUE,B6,C6,D6,E6,”Country”,F6).
Using this formula, we will get the result below:
Now suppose we wish to put a space as delimiter. In such scenario, the formula to use will be =TEXTJOIN(” “,TRUE,B7, C7, D7, E7,F7).
Let’s see an example of how to join more than two columns (using the data in columns below).
If we use the formula =TEXTJOIN(“, “, TRUE, B4:C12) then Excel will give the following result:
Let’s take an example where we are given the name, surname, and date of anniversary in separate columns and we wish to join them. In this scenario, we need to remember that if we directly enter the date, Excel will return a number as Excel store dates in number format.
Using the date below:
If for this data we enter the formula =TEXTJOIN( ” “, TRUE, B5:D5,)
Excel will give the result below:
Hence, we need to convert the date into text string first. So, the formula that we need to use would be =TEXTJOIN( ” “, TRUE, B5:D5, TEXT(C2, “mm/dd/yyyy”) ). We get the result below:
Click here to download the sample Excel file
Thanks for reading CFI’s guide to important Excel functions! By taking the time to learn and master these functions, you’ll significantly speed up your financial analysis. To learn more, check out these additional resources:
To master the art of Excel, check out CFI’s FREE Excel Crash Course, which teaches you how to become an Excel power user. Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.
Launch CFI’s Free Excel Course now to take your career to the next level and move up the ladder!