CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data?

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 1

Data comes in many different forms and formats. One of these formats is text data. You can refine the data with many functions such as CONCAT, TEXTJOIN in Excel.

Below are the differences and intended uses of each function.

What is CONCAT?

The CONCAT function in Excel allows you to join values ​​together. This value can also be text, numbers, or characters in a cell or range of cells in a spreadsheet.

It is important to note that CONCAT is a replacement for CONCATENATE. The CONCAT function has the following formula:

=CONCAT(text1, [text2,.])

In there:

1. Text1 is required and is the value you want to concatenate.
2. [text2, .] is optional and is the second value or cell you want to concatenate.
3. … meaning you can add more value. This function accepts up to 253 text arguments.

What is TEXTJOIN?

Like the CONCAT formula, TEXTJOIN allows you to combine text in a range. This formula also allows you to select the separator between text values.

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

In there:

1. Delimiter is a text separator. It can be characters, text, numbers, even spaces.
2. Ignore_empty allows you to specify whether you want to ignore empty cells in a range. If true, ignore blank cells.
3. Text1 is the first cell or text that you want to combine.
4. [text2, …] is the second value you want to concatenate.

Similarities Between CONCAT and TEXTJOIN . Functions

The similarity lies in the ability to combine multiple text or values. This is very useful in some situations. For example, when trying to quickly combine two columns in Microsoft Excel.

How to use the CONCAT . function

Suppose you have a data table consisting of First Name and Last Name. If you want to combine them into a Full Name column, consider using the CONCAT function.

The formula would be:

=CONCAT(A2," ",B2)

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 2

Detailed analysis of the formula:

The text1 argument is A2. The text2 argument is " ". Sign " "means you want a space before text3, here is B2. After writing this formula in the first cell, you don't need to copy the formula in Excel. Instead, you can drag it to the last cell.

For example, you want Jajay-Ayu to be the Full Name for someone, where First Name and Last Name are Jaja and Ayu respectively.

=CONCAT(A2,"-",B2)

Now consider another example.

Let's say you have a data table that includes the names of countries in West Africa. Add the phrase: "is a country in Africa". You would write the following command:

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

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 3

Like the first example, when writing text or phrases in a formula, you need to enter them in quotation marks.

How to use the TEXTJOIN . function

Consider the following example. We have two columns. One is First Name, the other is Last Name. To use the TEXTJOIN function, write the following formula:

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

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 4

' 'represents the space between first and last name. TRUE means the function will ignore any blank cells.

Should you use CONCAT or TEXTJOIN

Both of these functions have the same basic function. The difference between them comes from the arguments.

CONCAT wants you to add text from multiple ranges and delimiters. The JOIN function allows you to predefine the delimiter and whether or not you want to ignore empty fields.

Now let's look at an example where CONTCAT would be a better choice for TEXTJOIN.

Let's say you have a table of data, which includes columns for First Name, Last Name, and Age. Here want to join them together to create Jaja Giwa-36 and put the result in a column named Detail.

It is recommended to use CONCAT in this case because you can use multiple delimiters.

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 5

However, you should only select one delimiter when using the TEXTJOIN function. You can only use the spacebar or -, not both. In this situation, CONCAT does a better job.

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 6

Here's an example where TEXTJOIN should be used instead of CONCAT:

Create a data table, including columns for First Name, Middle Name, and Last Name. If the names in a row do not contain middle names, concatenate them into a column named Full Name, using the CONCAT function will create extra spaces.

For example, the result at E3 has two spaces instead of one because of the missing middle name. However, if TEXTJOIN was used, you could have used its IgnoreEmpty argument to ignore empty cells.

CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data? Picture 7

Above are the things to know and the difference between the CONCAT and TEXTJOIN functions in Excel .

Isabella Humphrey

Update 12 May 2023