How to Concatenate Multiple Cells in MS Excel 2016?

Concatenate is a text function which is used to concatenate multiple cells in excel. The Concatenate function does not provide the delimiter. Like, it does not provide spacing between the texts that you want to concatenate.

The Syntax of the Concatenate function is given below:

=CONCATENATE(text1, [text2],……)

In the above syntax, text1 is the first item to consider while concatenating. It can be a string or array of string (i.e. range of cells). The field text2 is optional if considered it is next item to concatenate. We can take up to 253 text arguments in Concatenate function. Each text argument can be a string, array of string or the range of cells.

In this section, we will see the various method of concatenating multiple cells, columns or rows in the excel sheet.

1. Insert Function
2. Text Function
3. & Operator
4. Fill Justify

Steps to Concatenate Multiple Cells in Excel 2016:

Insert Function

Step 1: Select a cell where you want to display the result of concatenate function. Go to the Formulas tab, under the Function Library group, click on Insert Function.

Step 2: Insert Function dialog will appear; there you have, select a category dropdown box. As concatenate is a text function, select Text option. All Text function will start appearing in Select a function box. Here, select CONCATENATE. Click OK.

Step 3: Function Arguments dialog box will appear. Under CONCATENATE section you have Text1, Text2. And as you will start filling Text1, Text2; Text3 and Text4 will start appearing.

Text Function

You can get this Function Arguments dialog box directly from the Text Function in the Function Library group in Formulas tab.

Now, below are examples of Concatenation.

Example 1: In Text1 I have selected cell A4, in Text2 cell B4 and in Text3 cell C4. Below the Concatenate section, you will see how the result of concatenation will appear i.e. “Charles63”.

As concatenate function does not provide any delimiter, there is no spacing or nor any separator is found in the concatenated text.

Click OK to see the result in the selected cell.

Example 2: In Text1 I have selected cell A4, in Text2 I have entered the string “ work for ”, in Text3 C4, in Text4 “ days”. The result is “Charles works for 3 days ”.

The result will be :

Example 3: To provide space or “,” or “&” etc. between the two concatenated text we have to mention that delimiter while using the concatenate function.

Like in the picture below you can see that between Text1 and Text3, in Text2 I have specified the space delimiter within the inverted comas.

Whatever you do in Function Argument dialog box that appear in the Formula bar. So, you can directly type the formula to Concatenate in Formula bar.

Like in the picture below, I have typed:

=CONCATENATE(A2, “, ”, A3)                   result is “Joseph, Stieve”

& Operator to Concatenate Multiple Cells

The & operator is the easy way to concatenate multiple cells in excel.

Example 1: Select a cell and then type the formula using & operator.

For example, I have to concatenate cells A2, A3. So the formula is:

=A2&A3                               result is “JosephStieve”

I also want a space between the concatenated text. So the formula is:

=A2&” “&A3                      result is “Joseph Stieve”

Example 2: Concatenating more than 2 cells will go in this way

=A2&B2&C3                         result is “Joseph35”

Fill Justify

One of the ways to concatenate the single range of cells is Fill Justify. This works only for a single range of cells. It doesn’t work for multiple ranges of cells. But, using Fill Justify you can combine the text of multiple rows of single range in just one click.

Step 1: Select the range, and go to the Fill, drop-down button in the Editing group of Home tab. Click on Justify.

Step 2: You will see all the text of your selected range will appear concatenated in the first cell.

So this was all about the methods of concatenating the text, string or array. Hope the content is useful. Keep reading.