CONCATENATE Function in Excel – How to use it

In this tutorial let us explore how to use CONCATENATE Function in Excel with examples

Summary

CONCATENATE function is used to join several text strings into one.

The CONCATENATE function is an inbuilt worksheet function in excel which comes under the String/Text function category. This function could be used as a single function or nest it into other functions. This function could also be used in a macro (Excel VBA)

Syntax:

The syntax for the CONCATENATE function is

=CONCATENATE (text1,tex2,text3…text255)

 

Arguments or Inputs

The CONCATENATE function requires the following arguments

Text1 to Text255:

This argument is required

At least two text values are required to join as one string.

Excel allows to join up to 255 text inputs.

Result:

The CONCATENATE function will join two or more strings and return as a single string. Excel allows to input up to 255 text strings to join as a single string. However, if the strings are more than 255 we could use one more Concatenate function by nesting.

Example

The following screenshot shows how the CONCATENATE function is used with different input values and the result of the function.

concatenate function screenshot

Explanation based on the above screenshot

 

  • Function is =CONCATENATE(A2,B2,C2)

A2,B2 and C2 refers to the text in cells A2, B2 and C2 respectively

The CONCATENATE function joins all the three strings as it is and returns “Tom&Jerry”

  • Function is =CONCATENATE(A3,B3,C3)

A3,B3 and C3 refers to the text in cells A3, B3 and C3 respectively

The CONCATENATE function joins all the three strings. However, it returns “Harry Potter” because there is a space inserted in B3 cell

  • Function is CONCATENATE(A4,” and “,B4)

A4 and B4 refers to the text in cells A4 and B4 respectively

The CONCATENATE function joins A4 cell value and the text “and” with space in both sides and B4 string.

It returns “Bread and Breakfast”

  • Function is CONCATENATE(A5,(TEXT(B5,”dddd dd, yyyy”)))

A5 and B5 refers to the text in cells A5 and B5 respectively

A5 is a string and B5 is a date. Concatenate function will return “Day:43842”. This is because Excel stores date as number.

To avoid this Text Function is used to convert the date to Day and year

We could also use “&” to join strings but CONCATENATE Function is more useful if the strings are too many.

Note: Usually when we import data from an external source, we may get words separated for example the first name and Last name of a person may be imported as two strings. In such case CONCATENATE function could be used to make them as one string. Also, we could further refine any such data by using other TEXT functions like TRIM, FIND, SUBSTITUTE, VALUE as the case may be.

 

Question: How to quickly input a range of cell values into CONCATENATE Function

Answer: In CONCATENATE function it is not necessary to enter the arguments one by one. Instead we could just enter by selecting the range. Example CONCATENATE (A2:J2) will join all the strings in cells range A2 to J2.

The CONCATENATE function is not available in VBA but we could use “&” ampersand