CONCAT vs. TEXTJOIN: Which One Should You Choose to Join Values

Data comes in various forms and formats. One of these formats is your text data. You can manipulate text data using multiple functions like CONCAT and TEXTJOIN functions in Excel.

Here, we’ll learn about their differences and when you should use one over the other.

What Is CONCAT?

The CONCAT function in Excel allows you to join values together. This value can also be a text, number, or character within a cell or range of cells in your spreadsheet.

It is important to note that the CONCAT is a newer replacement for CONCATENATE function. The CONCAT function takes the following syntax:

=CONCAT(text1, [text2,...]) 

Where:

• text1 is required and is the values you want to join.
• [text2, …] is optional and is the second value or cell you want to join.
• …. means you can add more values. The function accepts a maximum of 253 text arguments.

What Is TEXTJOIN?

Like the CONCAT formula, the TEXTJOIN formula allows you to combine text within a range. This formula also allows you to specify a delimiter between these text values.

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

Where:

• Delimiter is what we wish you separate these texts. It can be a character, text, number, or even space.
• ignore_empty allows you to specify if you wish to ignore empty cells in a range. If TRUE, ignores empty cells.
• text1 is the first cell or text you want to combine.
• [text2, …] is the second value you want to join.
• …. means more values.

Similarities Between the CONCAT and TEXTJOIN Function

Their similarity lies in their ability to combine multiple texts or values. This is very helpful in some situations. For example, when trying to quickly combine two columns in Microsoft Excel.

Let’s look at how these two functions work individually.

How to Use the CONCAT Function

Suppose you have a table of data that includes columns for First Name and Last Name. If you want to join them into one column called Full Name, consider using the CONCAT function.

The syntax will be:

=CONCAT(A2," ",B2) 

To break down the formula. The text1 argument is A2, and the [text2 argument is ” “. The ” ” means we want a space before our text3, which is B2. Once you write the formula in the first cell, you don’t need to copy the formula in Excel. You can instead drag it to the last cell.

Let’s say you want Jajay-Ayu as the Full Name for someone whose First Name and Last Name are Jaja and Ayu, respectively.

=CONCAT(A2,"-",B2) 

Let’s look at another example.

Suppose you have a table of data that includes just various West Africa Country Name, and you want to add the phrase “is a country in Africa”. You will write the following syntax:

=CONCAT(A2," is a country in Africa") 

Like the first example, when writing a text or phrase in your formula, you need to put this phrase in a quotation.

How to Use the TEXTJOIN Function

Let’s use the data table we used for the CONCAT.

We had two columns. One was the First Name, and the other was the Last Name. To use the TEXTJOIN function, we will write this syntax.

=TEXTJOIN(" ",TRUE, A2:B2) 

The “ “ represent the space between our first and last names. The TRUE means it should ignore any blank cell.

CONCAT vs. TEXTJOIN: When Should You Use Them?

Both functions have fundamentally the same functionality. Their difference comes in their arguments.

CONCAT wants you to add the text from multiple ranges and specify where you want your delimiter(s). The TEXTJOIN function, on the other hand, allows you to pre-specify your delimiter and confirm if you wish to ignore empty.

Let’s look at a scenario where CONCAT will be a better choice to TEXTJOIN.

Suppose you have a table of data that includes columns for First Name, Last Name, and Age. Let’s say you want to join them to create “Jaja Giwa-36” and put your result into one column called Detail.

This is easy using CONCAT because you can pick multiple delimiters.

However, TEXTJOIN expects you to pick only one delimiter. You can only use the space or the dash (-), not both. In this situation, CONCAT does the work better.

Here’s a scenario where TEXTJOIN should be used instead of CONCAT.

Create a table of data that includes columns for First Name, Middle Name, and Last Name. If a name in a row doesn’t contain a middle name, joining these names into one column called Full Name using the CONCAT function will result in additional spaces.

For example, the result in E3 has two spaces instead of one space because it lacks a middle name. However, if TEXTJOIN was used, you could have used its IgnoreEmpty arguments to ignore empty cells.

It All Depends on Your Task

Various Excel functions are similar but also different. Learning what makes them different and when best to use them will help you get better at data analysis. A few examples of some of such functions are the COUNTIF and COUNTIFS and the XLOOKUP and VLOOKUP.