What is concatenate in Excel?

Microsoft Excel, or Excel, is the most useful and powerful spreadsheet program used to record financial data and accordingly perform analysis and calculations via built-in functions. When dealing with the data in spreadsheets, it is not always structured properly to perform respective analyses. We either organize the data manually or use Excel’s functions and tools in such cases. It is where the CONCATENATE function comes into play.

Introduction to CONCATENATE Function

In Excel, the term CONCATENATE means ‘to join together’ or ‘to combine’. The CONCATENATE function is one of the built-in Text functions in Excel, enabling users to combine or join two or more cell values into a single cell. The input cell values can be in the form of numbers, strings, or characters.

In addition to cells, the CONCATENATE can also join values from rows, columns, or ranges. The function can simultaneously concatenate (join) up to 30 values and provide the result in text format. Common examples when we may need to use the CONCATENATE function include the joining of names, addresses, dates, and times. For example, joining of first name, middle name, and the last name into a full name in a single Excel sheet cell.

Suppose that cells A2 and B2 contain values ‘Hello’ and ‘User’, respectively. If we apply the formula “=CONCATENATE(A2,” “,B2)” in cell C2, the formula will return the string of cell A2, followed by a space and the string of B2. That way, the result in cell C2 will be ‘Hello User’.

Syntax of the CONCATENATE Function

The syntax of CONCATENATE function in Excel is defined as below:

Arguments of the CONCATENATE Function

The CONCATENATE function requires the following arguments:

• Text1 (required argument): It is the first item/ value to join. This can be in the form of a text value, number, or number.
• Text2 (required argument): It is an additional text item/ value we want to join.
• [Text3] (optional argument): It is the third item/ value to join together. The CONCATENATE function can help us join a maximum of up to 255 items, up to 8192 characters.

Note: The CONCATENATE function is replaced with the CONCAT function in Excel 2016, Excel for web, and Excel Mobile. However, the function is still available for backward compatibility. But, it is better to practice using CONCAT because the CONCATENATE function may not support by upcoming Excel versions.

How to use CONCATENATE Function in Excel?

Let us now explore the working of the CONCATENATE function with the help of some examples:

Example 1: Using CONCATENATE function for values in different cells

Suppose we have some people’s first and last names in different cells across columns A and B. We need to combine the first and the last names to create a full name in a new column.

The simple syntax to concatenate cell values divided by space can be defined as below:

Where “cell_no” represents the cell reference containing strings that need to be concatenated.

We must perform the below steps to concatenate text values in different cells to create full names, respectively:

Before using the CONCATENATE function to join names, we are first required to insert a new column to create a combined name. Let’s say we use column C. We name its header as ‘Combined Name’.

Next, we must type the function name and supply respective arguments in it. First, we concatenate the text (first and last names) in cells A2 and B2. Therefore, we write the function in cell C2 in the following way:

=CONCATENATE(A2, B2)

After entering the function, we must press the Enter key to obtain the respective output.

The above example sheet shows that the first and last names are concatenated from cells A2 and B2 in cell C2. However, there is no space between the first and last names. It is because the CONCATENATE function only combines values as exactly as they are supplied and nothing more. So, if we need to insert a space, any punctuation, or other details in the resultant cell, we must include it accordingly while inserting the CONCATENATE function.

Since we need to include a space between the first and last names in the resultant cell (C2), we must add another argument in the CONCATENATE. We need to insert a space as an argument closed within the double quotes (” “), i.e.,

=CONCATENATE(A2,” “,B2)

After pressing the Enter key, the above function returns the combined name, where the space separates the first and last names.

Lastly, we can use the Fill Handle to copy-paste the formula from cell C2 to the below/ remaining cells in the column. This will instantly combine other names, as shown below:

Example 2: Using CONCATENATE function to join cell value and string

We can join one or more cell values with the desired string using the CONCATENATE function. We need to apply the formula and input the desired string directly as an argument. The string can be added to the formula’s starting, middle, and ending as per our requirements.

Suppose that we have the first and last names in corresponding cells A2 and B2. We want to join first name and last name and include the desired string (is a cricketer) in cell C2.

The simple syntax to concatenate cell values and a custom string can be defined as below:

Like the previous example, we are first required to concatenate the text (first name and last name) present in cells A2 and B2 and then add our string in the following way:

=CONCATENATE(A2,” “,B2,” “,”is a cricketer”)

We also added a space character in the formula to divide the words in the resultant string.

In the following example sheet, we concatenate a cell value with a string where the string is the starting argument.

We can notice that the CONCATENATE function only joins the values, irrespective of the existing formatting of the cell. Although the value in cell A2 is in the bold format, the resultant cell C2 displays a default formatting. That means the source cell formatting is not used in CONCATENATE.

In another example, we concatenate a cell value with start and ending strings, i.e., the cell value is in the mid.

Example 3: Using CONCATENATE to join cells with a space, comma, and other symbols or characters

Using Excel’s CONCATENATE function, we can join one or more cell values along with the special symbols, characters, etc. We can apply the CONCATENATE formula and directly input the desired symbol as an argument. The formula can contain up to 255 strings, including the symbols and cell values.

Suppose that we have the first and last names in corresponding cells A2 and B2. We want to join first and last names and include the desired symbol (-) between them in cell C2.

The simple syntax to concatenate cell values divided by a symbol can be defined as below:

In previous examples, we have concatenated cell values divided by a space. Similarly, we are first required to concatenate the text (first name and last name) present in cells A2 and B2 and then add an additional argument (sign or symbol) in between them in the following way:

=CONCATENATE(A2,”-“,B2)

Likewise, we can add any desired sign or symbol to join our strings using CONCATENATE function.

The example below also includes a custom string along with the cell values and a symbol while concatenation:

Example 4: Using CONCATENATE to join Text String and Formula Calculated Value

Using Excel’s CONCATENATE function, we can join one or more cell values and text strings along with the formula calculated value(s).

Suppose that we have some information like the time and date of different articles. We want to record complete information in a message explaining it more clearly.

Since we want to use the dates and time from our recorded cells, we only need to include the date (mm/dd/yyyy) and time (hh:mm:ss) formatting in the CONCATENATE formula. Therefore, we apply the CONCATENATE formula in the following way to get meaningful context about what date and time our posts went live.

=CONCATENATE(A2,” went live on “,TEXT(B2,”mm/dd/yyyy”),” at “,TEXT(C2,”hh:mm:ss”))

By copy-pasting the formula onto other remaining cells or dragging the Fill handle, we can get the concatenated message for other cells in the column.

How do we concatenate a range of cells?

Combining multiple values from different cells or a range using the CONCATENATE function requires additional effort compared to other Excel functions. Unlike other typical functions, the CONCATENATE function does not support arrays. That means we have to supply each desired cell reference in the formula for each argument.

For example, if we need to concatenate cells from A1 to A4, we have to use the following formula:

=CONCATENATE(A1, A2, A3, A4)

When dealing with a small number of cells, it is easy to enter cell references manually. However, if we have several cells or a large range to concatenate, we can press the Ctrl key and supply each cell in the CONCATENATE formula as an argument. Here are the respective steps:

• First, we need to select a cell to type CONCATENATE formula.
• Next, we must type the formula (=CONCATENATE) in the respective cell, followed by the opening parenthesis.
• After entering the formula, we must supply the desired arguments (or cells) separately. We can use the Ctrl key to select multiple cells to be concatenated. We must hold the Ctrl key on the keyboard and click on each cell individually that we want to concatenate.
• Lastly, we must release the Ctrl key, type the closing parenthesis at the end of the formula and press the Enter key.
  

In addition to this, if we have a huge range containing hundreds of cells to concatenate, it is best to use a little trick with the combination of the TRANSPOSE function. The TRANSPOSE function will help us return an array and further replace it with separate cell references at once. We can use the TRANSPOSE function in the following way:

• First, we must select the cell to concatenate a range and enter the TRANSPOSE formula. For our example, we enter the formula in the following way:
  =TRANSPOSE(A1:A10)
  
• After entering the TRANSPOSE formula, we need to go to the formula bar and press the F9 function key on the keyboard. This will display an array of numbers from the selected range that we need to concatenate.
  
• We need to delete the curly braces from the start and end of the array values in the formula bar. Also, we must type =CONCATENATE function followed by the opening parenthesis before the first array value and close the parenthesis at the end. After that, we have to press the ENTER key on the keyboard.
  
  This way, we can concatenate tens to hundreds of cells or a huge range with just a few clicks.

Difference between the CONCATENATE function and the “&” operator in Excel

The ampersand “&” operator is an alternate method of concatenating two or more cell data or texts. However, the application of the “&” operator can be quick and easy to use in many cases as compared to typing the ampersand sign (&) is somewhat faster than typing the CONCATENATE function.

The only difference between the working of CONCATENATE function and the “&” operator is the limit of 255 strings. The CONCATENATE function can only join up to a maximum of 255 strings, while there is no such limit in the case of the ampersand “&” operator. Despite this, there is no significant difference between these two concatenation methods.

Let us observe both the concatenation techniques with the help of the formulas discussed below:

Concatenation of values in A2 and B2

Using CONCATENATE Function: =CONCATENATE(A2,B2)

Using the ampersand “&” operator: =A2&B2

Concatenation of values in A2 and B2 separated with a Space

Using CONCATENATE Function: =CONCATENATE(A2,” “,B2)

Using the ampersand “&” operator: =A2&” “&B2

Concatenation of values in A2 and B2 and a Text String

Using CONCATENATE Function: =CONCATENATE(A2,B2,” completed”)

Using the ampersand “&” operator: =A2 & B2 & ” completed”

Concatenation of String and the result of the TEXT/ TODAY function

Using CONCATENATE Function: =CONCATENATE(“Today is “,TEXT(TODAY(),”dd-mmm-yy”))

Using the ampersand “&” operator: “Today is ” & TEXT(TODAY(), “dd-mmm-yy”)

Important Points to Remember

• The CONCATENATE function at least requires one argument (text string) to work.
• When entering numeric values in the CONCATENATE formula, we don’t need to enclose them in double quotation marks.
• If we don’t enclose the text strings within the double quotes, the CONCATENATE function does not recognize it and returns the #NAME! error.
• If there is any invalid argument supplied within the CONCATENATE formula, it may return the #VALUE! error.
• The CONCATENATE function always returns a text formatted output despite the data supplied in its formula. That means if we concatenate numbers of two or more cells, the result will be a text string, not the numbers.
• When concatenating values from multiple cells, we must click on each cell separately to supply them as arguments in the formula. If we directly select the entire range using the mouse, it will be supplied as an array in the CONCATENATE formula, which is not supported by the formula.
• Excel also has a Merge Cells tool that allows us to combine several cells with any data like text strings, numbers, dates, and special symbols into a single cell and separate them using any delimiter of our liking.