CONCATENATE vs CONCAT in Excel (2 Ideal Examples)

Introduction to the CONCATENATE Function

Summary

The CONCATENATE function in Excel combines multiple text values or numeric values into a single text value.

Syntax

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

Syntax of CONCATENATE Function in Excel

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 the 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

=CONCAT(text1,...)

Syntax of CONCAT Function in Excel

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 is to be joined. Can be any text value, number, cell reference, or array of values.

Return

The function returns a combined text value after it merges all of the arguments.

CONCATENATE vs. CONCAT Functions in Excel: 2 Ideal Examples

We will differentiate the functions based on their availability and input criteria. All the other aspects of these two functions are the same. We will use the following dataset.

2 Ideal Examples of CONCATENATE vs CONCAT in Excel

Example 1 – Availability of CONCATENATE and CONCAT Functions in Excel

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.

You will find the CONCAT function only in Excel 2019, Excel 2021, and Microsoft 365 versions.

Example 2 – Difference of Input Criteria Between CONCATENATE and CONCAT Functions in Excel

Steps:

We will demonstrate the incompatibility of the CONCATENATE function with the cell range as a reference in its formula.
In a merged cell, use the following formula in cell B5.

=CONCATENATE(B5:E5)

After pressing Enter, you will get #SPILL as an error.

We will apply the same formula in an unmerged cell to see if it joins all the given strings in a single cell.

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.

To undo this error, use the above formula in the proper syntax for the CONCATENATE function in cell B5.

=CONCATENATE(B5,C5,D5,E5)

After pressing Enter, we will get the desired result.
Use AutoFill to drag the formula to the lower cells of the column.

To get this similar result by using the CONCAT function, use the following formula. Instead of mentioning each of the cells, give a proper cell range to merge the texts.

=CONCAT(B5:E5)