CONCATENATE Function in Microsoft Excel | CONCATENATE Formula

We hope you all are doing good and understanding and practicing Excel tips and tricks that are shared here.Today we are going to discuss How to use CONCATENATE Function in Excel, Before that you all must know that CONCATENATE Function is now replaced by CONCAT Function in Excel 2016 version and in upcoming versions also, But It is still available for backward compatibility in Excel 2016 version.

WHAT DOES CONCATENATE FUNCTION DO?

Concatenate Function is a text Function which helps in combining the two or more strings into a single string in Excel.It can combine two or more cells, ranges, columns or rows.It acts in two ways in Excel,

  • Merge cells
  • Concatenate cell’s value

Look at this image below to understand the difference,

Merge and concatenate in Excel

In Merged cells column, the two columns “Month” and “Sales” are merged together and formed a new larger column, while In Concatenated data cell, the values or content or text are combined and stored in a cell of the same length.

HOW TO USE CONCATENATE FUNCTION IN EXCEL?

Now, Let’s discuss how can we implement Concatenate Function in Excel Sheet,

Syntax for CONCATENATE Function,

   =CONCATENATE(text1, [text2], …)

ARGUMENTS:

text1-This is a required argument, which can be a text value, cell reference or number.

  • [text2…..]– These are the additional text strings or values that need to be joined with the first argument, which can be up to 255 values.

Let us do an example in order to understand this better,

Consider we are having this Excel Sheet, where we want to combine First Name and Last Name into one Column,

screenshot of excel 2013

So, we need to write its CONCATENATE Function, but before that, e would create a new Column namely Combined Name, like this,

screenshot of excel 2013

Now, we are good to go, let’s write its CONTENATE Function,

  1. Firstly we would start with its syntax,

                                          =CONCATENATE(

2.Now, we would insert our arguments, but if we would individually specify each First name with the last name, it would take considerable time, so for saving our time Excel helps us in Concatenating by just specifying cell references, like this,

                                         =CONCATENATE(A2,B2)

screenshot of excel 2013

Here, you would observe that we need a space in between first and last name, So we would modify our CONCATENATE Function like this,

                                         =CONCATENATE(B2,” “,A2)

We just have added space as argument in double quotes here and hence we can see the change below,

screenshot of excel 2013

3. Now, without specifying these cell references again by using fill handle, we could use the same formula for all the remaining cells of this column.

screenshot of excel 2013

This is How we use Concatenate Function in Excel, It’s a very useful tool in Excel.We hope you understand and easily implement the Concatenate Function in your Excel Sheet.If you like our post and any of your friends or colleagues are facing problem in using Concatenation Function share it with them and help them. You can also share on social media websites like Facebook, Twitter, and Instagram. If you have any queries regarding this post then you can freely ask to us. We’ll be happy to solve your queries as soon as possible.

Add a Comment

Your email address will not be published. Required fields are marked *