Skip to content
Related Articles

Related Articles

Improve Article

CONCATENATE in Excel

  • Difficulty Level : Medium
  • Last Updated : 01 Jun, 2021

Excel provides us the flexibility to combine the data from two or more columns into singular column data. For example: Joining the First Name, Middle Name, and Last Name of a person into a single column Full Name. There are two ways in which we can concatenate two or more cell data or texts :

  1. Using the keyword.
  2. Using the “&” operator.

The keyword used to combine these texts is “CONCATENATE”. However, in the recent 2016, 2019 version of Excel, the keyword is replaced with “CONCAT”.

In this article, we are going to see how to concatenate two or more cells in Excel with few examples using the above-listed methods and also differentiate between the two methods.

Concatenate Function

The CONCAT( ) or CONCATENATE( ) helps to combine the contents of two or more cells into a singular content. 

Syntax:



CONCATENATE(text1,text2,.....)   
(or)
CONCAT(text1,text2,.....)

Some important points to be noted about CONCATENATE function in Excel are :

  • Since our primary goal is to combine, so CONCATENATE function needs at least one text as its argument. The text can be a cell value or any string text.
  • If one of the arguments in the function is invalid, Excel returns an error.
  • A maximum of 255 strings can be concatenated using a single CONCATENATE function.
  • In the case of multiple and contiguous cell values there is no flexibility of using an ARRAY or looping. We have to individually enter the cell values as arguments. For example : =CONCATENATE(A1,A2,A3,A4,A5) is allowed but =CONCATENATE(A1:A5) is not allowed in Excel.
  • Braces are important while dealing with multiple functions inside or outside the CONCATENATE function.

Example 1: Concatenating values at different cells.

= CONCATENATE( cell_no_1 , cell_no_2)     ; concatenate without any space in between

cell_no : The location of the cell where the string to be concatenated is present.


Concatenated

= CONCATENATE( cell_no_1 ,” “, cell_no_2) ; concatenate with space in between

cell_no : The location of the cell where the string to be concatenated is present.

Concatenated



Now, suppose if we have the “First Name” and “Last Name” data of 1000 students. In the column “Full Name” for the first record, we can write the above statement to concatenate the two cells. Now, simply drag the “Auto Fill Options” from the first student of Full Name column to the last. This will automatically fill in the Full Name of various students. This is the benefit of CONCATENATE( ) else one would have to manually enter the record for 1000 students which are cumbersome.

Example 2: Concatenate cell value and string.

= CONCATENATE( cell_number(s), “text_string”)

or

= CONCATENATE( “text_string”, cell_number(s) )

Example 3: We can include any symbol, space, no space between two cell values as per requirements while concatenating.

Using the “&” Operator For Concatenation

The ampersand symbol “&” is an alternate way to concatenate two or more texts in Excel. It is easier and less time-consuming as compared to the above method as there is no need to type the entire keyword for concatenation. The syntax is :

cell_number1 & cell_number2 ; without space in between

cell_number1 & " " & cell_number2;  with space in between

cell_number1 & "Any_Symbol" & cell_number2 ; with any symbol in between

cell_number(s) & "string text" ; concatenate cell values and strings

"string_text" & cell_number(s) ; concatenate cell values and strings

Concatenate using & operator

In this way, using the above two methods we can perform various concatenations in Excel. The only difference between these two methods is that there is no limit of 255 strings in the case of “&” operator. We can concatenate as many strings as we want. Both are having the same speed and display the same value in the form of text and show the same error message during faults. It boils down to one choice to use either of the above two methods based on one’s comfort.

My Personal Notes arrow_drop_up
Recommended Articles
Page :