Md. Araf Bin Jayed
While working with Excel, you need to use various functions depending on your purpose or criteria. Two such functions are the CONCATENATE function and the CONCAT function of Excel. As they have similarities in their names, their operating processes are also almost the same. But, they do have some similarities between them. In this article, I will show you CONCATENATE vs CONCAT in Excel.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
Introduction to CONCATENATE Function
Summary
The CONCATENATE function combines multiple text values or numeric values into a single text value.
Syntax
The syntax or formula for this function is,
=CONCATENATE(text1,[text2],...)
Argument
| ARGUMENT | REQUIRED OR OPTIONAL | VALUE |
|---|---|---|
| text1 | Required | The first value to be joined. Can be any text value, number, cell reference or array of values. |
| [text2] | Optional | The second value to be joined. Can be any text value, number, cell reference or an array of values. |
Return
After merging all of the arguments, the function returns a combined text value.
Introduction to CONCAT Function
Summary
The CONCAT function joins the text from different ranges or strings together. However, it doesn’t accept delimiters or ignore empty parameters.
Syntax
The syntax or formula for this function is,
=CONCAT(text1,...)
Argument
| ARGUMENT | REQUIRED OR OPTIONAL | VALUE |
|---|---|---|
| text1 | Required | The first value to be joined. Can be any text value, number, cell reference or array of values. |
| [text2] | Optional | The second value to be joined. Can be any text value, number, cell reference or an array of values. |
Return
The function returns a combined text value after it merges all of the arguments.
2 Ideal Examples of CONCATENATE vs CONCAT Functions in Excel
In this article, you will see two easy examples of CONCATENATE vs CONCAT in Excel. Here, I will differentiate them based on their availability and input criteria. All the other aspects of these two functions are the same. To illustrate the further procedure, I will use the following data set.
1. Availability of CONCATENATE and CONCAT Function in Excel
The first difference between these two functions is based on their availability. Users can find the CONCATENATE function in all older versions of Excel from Excel 2007, including the new ones like Microsoft 365. It is the predecessor of the CONCAT function.
On the other hand, you will find the CONCAT function in only the Excel 2019, Excel 2021, and Microsoft 365 versions. They introduced this function to solve the limitations of its predecessor.
Read More: How to Concatenate in Excel (3 Suitable Ways)
2. Difference of Input Criteria Between CONCATENATE and CONCAT Function in Excel
The second point will focus on the input criteria of these two functions. In the CONCATENATE function, you can not merge all text values in a range altogether. However, by using the CONCAT function, you can easily perform this task. See the following steps for a clear understanding.
Steps:
- First of all, I will demonstrate the incompatibility of the CONCATENATE function with the cell range as a reference in its formula.
- To do this, in a merged cell, use the following formula in cell B5.
=CONCATENATE(B5:E5)
- Secondly, after pressing Enter, you will get #SPILL as an error.
- Thirdly, I will apply the same formula in an unmerged cell to see if it joins all the given strings in a single cell.
- But, after pressing Enter, you will get the result like the following image where the result will be shown in an equal number of cells that matches the number of reference cells.
- Fifthly, to undo this error, use the above formula in the proper syntax for the CONCATENATE function in cell B5.
=CONCATENATE(B5,C5,D5,E5)
- Consequently, after pressing Enter, you will get the desired result.
- Then, use AutoFill to drag the formula to the lower cells of the column.
- Furthermore, to get this similar result by using the CONCAT function, use the following formula.
- Here, instead of mentioning each of the cells, give the proper cell range to merge the texts.
=CONCAT(B5:E5)
- Lastly, to see if the above formula gives you desired value press Enter.
- Consequently, to see the results for the lower cells of the column drag the Fill Handle.
Read More: How to Concatenate Email Addresses in Excel (4 Effective Ways)
Conclusion
That’s the end of this article. I hope you find this article helpful. After reading the above description, you will be able to understand CONCATENATE vs CONCAT in Excel. Please share any further queries or recommendations with us in the comments section below.
The ExcelDemy team is always concerned about your preferences. Therefore, after commenting, please give us some moments to solve your issues, and we will reply to your queries with the best possible solutions.
Related Articles
- Macro to Concatenate Multiple Columns in Excel (with UDF and UserForm)
- How to Concatenate Multiple Cells With Space in Excel (7 Methods)
- Concatenate Multiple Cells Based on Criteria in Excel (4 Methods)
- How to Concatenate Numbers with Leading Zeros in Excel (6 Methods)
- Combine Multiple Columns into One Column in Excel
- Opposite of Concatenate in Excel (4 Options)
- How to Concatenate Two Columns In Excel (5 Methods)
