CONCATENATE vs TEXTJOIN

Hello everybody!

In this article, I invite you to say goodbye to the old CONCATENATE function that when we wanted to add a space or any other character while concatenating the data, obliged us to do it one by one and thus that was very laboring, and say welcome to a more efficient function. If you need to join the data in multiple cells and add characters like a space, comma, semi colon, and hyphen; there is just one function that you need to use and that is the TEXTJOIN function.

WHAT DOES IT DO

Like I mentioned above, you don’t need to click cells one by one and use an expression like ;” “; to add a space in between each time or a statement like & ” ” & when joining with the & character in the textjoin function. It joins each cell in the selected cell range by adding the slicer we’ve indicated. This way, we can join the data in hundreds of cells easily as a text.

SYNTAX

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

There are 3 main arguments in the function.

These 3 main arguments are required, the other arguments are optional.Now let’s take a look at what these arguments mean, i.e. what the function wants from us and what we will give it.

delimiter (required)	A text string, either empty, or one or more characters enclosed by double quotes, or a reference to a valid text string. If a number is supplied, it will be treated as text.
ignore_empty (required)	If TRUE, ignores empty cells.
text1 (required)	Text item to be joined. A text string, or array of strings, such as a range of cells.
[text2, …] (optional)	Additional text items to be joined. There can be a maximum of 252 text arguments for the text items, including text1. Each can be a text string, or array of strings, such as a range of cells.

WORKING CONDITIONS

If the resulting string exceeds 32767 characters (cell limit), TEXTJOIN returns the #VALUE! error.
It came with Microsoft Excel 2019. If you are using and older Office version, you will get the #NAME? error and thus not be able to use this function.
If you Office 2019 or Office 365, you can easily use this function.

USING THE FUNCTION

After we write the function name, we specify the delimeter. And then we will choose how it should behave about the possible blank cells in the cell range. And in the last arguments, we choose the cell range to be joined. Now, let’s take a look at its use together.

In the example below, we join the data between the A and G columns with a semi colon in between them.

As you see, it is as easy as that to join a multiple cell range in a single text with any character between them.

See you in another articles, bye. 🙋🏻‍♂️
You can share this post with your friends and help them get informed as well.👍🏻