CONCAT vs. TEXTJOIN: Which function should I choose to concatenate data?
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:
- Text1 is required and is the value you want to concatenate.
- [text2, .] is optional and is the second value or cell you want to concatenate.
- … 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:
- Delimiter is a text separator. It can be characters, text, numbers, even spaces.
- Ignore_empty allows you to specify whether you want to ignore empty cells in a range. If true, ignore blank cells.
- Text1 is the first cell or text that you want to combine.
- [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)
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")
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)
' '
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.
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.
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.
Above are the things to know and the difference between the CONCAT and TEXTJOIN functions in Excel .
You should read it
- How to use ConcateNate function on Excel
- How to use Hlookup function on Excel
- How to use the SUM function to calculate totals in Excel
- How to use the WRAPROWS function in Excel
- How to use the NPER function in Excel to plan loans and savings
- How to use the LEN function in Excel
- Save time with these text formatting functions in Microsoft Excel
- How to use the MOD function and QUOTIENT function in Excel
May be interested
- Instructions for cutting strings, concatenating strings in Excelyou are looking for a way to cut strings, concatenate strings in excel to separate names or concatenate text strings. so please refer to the following article for instructions on how to cut strings, concatenate strings in excel.
- TRY_CAST function in SQL Serverthe article will show you in detail how to use the try_cast data type conversion function in sql server with specific syntax and examples to better visualize and capture functions.
- COUNTA function in Excel, function to count cells containing data with specific usage and examplesthe counta function in excel is a useful data statistics function. below are details on how to use the function to count cells with data in excel.
- Cell counting function with data in Excel - Enclose examplethe function to count cells with data in excel. in excel supports the data counting function to help you summarize the fastest way. the following article introduces data counting functions in excel.
- How to use Hlookup function on Excelhlookup function basically also has the function syntax and features like vlookup function, which is to help users find data in excel table, with the conditions or given information. here is the guide for using the hlookup function in detail.
- Multiplication function (PRODUCT function) in Excelthe multiplication function in excel is very important for those who often have to process and calculate data in excel. in excel you can also use the * operator to multiply data, but with large amounts of data you should use the multiplication function - product to get
- TRY_CONVERT function in SQL Serverthis article will show you in detail how to use the try_convert data type conversion function in sql server with specific syntax and examples to better visualize and capture functions.
- MS Excel - Lesson 16: CHOOSE function in Excelchoose in excel allows you to select a value from a list of more than 29 items
- How to combine IF, AND and OR functions to filter datahow to combine if, and and or functions to filter data if you only use the if function, the and function and the or function, you cannot see the full usefulness of these functions in excel. the following article will combine the if function, the and function and the or function to make jars
- SUBSTITUTE function - The function replaces text strings in Excelin the course of working you have excel data file need to change a series of data over the years. with a simple operation, you can change all that data. the following article details the substitute function - the function replaces the text string in excel.