Happy Friday Excellers. If you have Excel 2016 then you maybe already aware of some of the new functions that are available.
One of thos functions is TEXTJOIN, and also CONCAT which was introduced in February 2016. I have been using TEXTJOIN recently to replace the function I used on a regular basis —CONCATENATE, and 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 in the two formulas.
The Syntax of TEXTJOIN
TEXTJOIN(delimiter, ignore_empty, text1, [text2], …)
delimiter – this is required this is what you want in between the cells that you are joining, for example- a comma, or a 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 delimter 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 definately a convert!.
If you want to download the example file for this blog post you can so here.
Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.