How to use the CONCATENATE function in Microsoft Excel

Let's look at some Excel Concatenate function examples and explore how to use the Concatenate function as a worksheet function in Microsoft Excel:

Summary

In your Excel workbooks, the data is not always structured according to your needs. Often you may want to combine data from two or more columns into a single column.
The Excel CONCATENATE function allows you to join 2 or more strings together. The CONCAT function replaces CONCATENATE in newer versions (Excel 2019) of Excel.
The Concatenate function is a built-in function in Excel that is categorized as a Text Function.

Purpose

Join text together

Return value

Text joined together.

What Does It Do ?

This function joins separate pieces of text into one item.

Syntax

CODE function has one syntax and hence they can be written in Two different ways:

=CONCATENATE (text1, text2,...) or
=CONCATENATE (cell reference1, cell reference2,...)

Parameters or Arguments

Text - The text value to join together.
Cell reference - Reference to cells containing value..

Formatting

No special formatting is needed, the result will be shown as normal text.

Usage notes

• In versions of Excel 2007 and later We can join up to 255 text items that are up to 8192 characters together. but in Excel 2003, the function can only accept up to 30 text arguments.
• Text items can include text strings, numbers, or cell references that refer to one cell.
• Numbers are converted to text when joined. If you need to specify a number format for a number being joined, see the TEXT function.
• Similarly to the CONCATENATE function, you can use "&" in Excel to combine different text strings, cell values and results returned by other functions. This method come in very handy in many scenarios because typing the ampersand sign (&) is much quicker than typing the word "concatenate".
• If at least one of the CONCATENATE function's arguments is invalid, the formula returns a #VALUE! error.
• If we directly write the string without double quotes, CONCATENATE will not recognize it as a string and will throw an error #NAME?
• Numbers don’t need to be in quotation marks.
• Excel CONCATENATE does not recognize arrays. Each cell reference must be listed separately. For example, you should write =CONCATENATE(A1, A2, A3) instead of =CONCATENATE(A1:A3).
• In recent versions of excel Microsoft has developed a new function called TEXTJOIN by looping through arrays.

Excel CONCATENATE function vs. operator

Many users wonder which is a more efficient way to concatenate strings in Excel - CONCATENATE function or "&" operator.

The only essential difference between CONCATENATE and "&" operator is the 255 strings limit of the Excel CONCATENATE function and no such limitations when using the ampersand. Other than that, there is no difference between these two concatenation methods, nor is there any speed difference between the CONCATENATE and "&" formulas.

And since 255 is a really big number and in real-life tasks someone will hardly ever need to combine that many strings, the difference boils down to the comfort and ease of use. Some users find CONCATENATE formulas easier to read, I personally prefer using the "&" method. So, simply stick to the concatenation technique that you feel more comfortable with.

Click here to learn more excel function