Happy Friday Excellers. Welcome back to another #FormulaFriday #Excel blog post in my Excel series.  I will compare TEXTJOIN and CONCATENATE.  If you have Excel 2016 then you may be already aware of some of the new functions that are available.  One of those functions is TEXTJOIN, and also CONCAT which was introduced in February 2016.   I have been using TEXTJOIN recently to replace the CONCATENATE function I used on a regular basis.   I get the same results, without the hassle the CONCATENATE brings.

Want To See How Simple TEXTJOIN is?

Let’s carry out the same process, first with CONCATENATE then TEXTJOIN.  Here is the data below, and we are going to carry out some standard joining of the two strings, First_Name and Last_Name.

Here is the difference between the two formulas TEXTJOIN and CONCATENATE.

The Syntax of TEXTJOIN

TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)

Where

delimiter – this is required this is what you want in between the cells that you are joining, for example- a comma, or space.

ignore_empty – this is so you can choose to ignore and empty cells in your selected range.  This can be important if you are using a space ” ” as your delimiter and if you do not want extra spaces in between your joined text strings due to blank spaces in your selected cells.

text1,-is required, which can be a single text string or an array of strings like a range of cells

[text2] – this is an optional argument; use this additional text strings to be added to text1

note:- if the length of the value created by TEXJOIN is over 32,767 characters long then Excel will return a #VALUE! error.

It is really useful to declare the delimiter at the beginning of the formula and then be able to select the range of cells to join, in particular when the number of cells you want to join increase in number, as you can see in the example below.  TEXTJOIN makes life so much easier and I am definitely a convert!.