QueHow

A How-To and wiki Blog

  • Operating System
    • Windows
      • Windows 10
      • Windows 7
    • Linux
  • Software
    • Application Software
    • Microsoft Office
      • Outlook
      • Word
      • PowerPoint
  • Internet
    • Browsers
      • Mozilla Firefox
      • Google Chrome
    • CMS
    • Tips & Tricks
  • Website
    • ecommerce
    • Google
    • Social Networking
    • General Websites
  • Mobile
  • Email
    • Gmail
  • Wiki

How to Concatenate Multiple Cells in MS Excel 2016?

By Neha T Leave a Comment

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.

How to Concatenate in Excel 1

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.

How to Concatenate in Excel 2

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.

How to Concatenate in Excel 3

Text Function

You can get this Function Arguments dialog box directly from the Text Function in the Function Library group in Formulas tab.
How to Concatenate in Excel 2-1

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”.

How to Concatenate in Excel 2-1

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 ”.

How to Concatenate in Excel 5

The result will be :

How to Concatenate in Excel 6

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.

How to Concatenate in Excel 7

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”
How to Concatenate in Excel 8

& 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”

How to Concatenate in Excel 9

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

=A2&B2&C3                         result is “Joseph35”

How to Concatenate in Excel 10

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.

How to Concatenate in Excel 11

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

How to Concatenate in Excel 12

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

You Might Also Like:

How to convert text to number in Excel - Featured ImageHow to Convert Text to Number in MS Excel 2016? count unique values featured imageHow to Count Unique Values in Excel 2016? how-to-use-vlookup-in-ms-excel-2016-featured-imageHow to use VLOOKUP in MS Excel 2016? how-to-split-text-of-cells-in-ms-excel-2016-featured-imageHow to Split Text in a Cell in MS Excel 2016? how-to-protect-worksheet-and-cells-in-ms_excel-2016-featured-imageHow to Protect Worksheet and Cells in MS Excel 2016? Lock Formulas or a Cell in Excel - feature ImageHow to Lock Formulas or Cells in Excel? Merge Cell in Excel feature imageHow to Merge Cells, Columns & Rows in Excel? How to add in Excel-featured imageHow to Add or Sum in MS Excel 2016?

Leave a Reply Cancel reply

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

Most Viewed

  • How to Install and Use TELNET in Ubuntu
  • How to install Hindi fonts in MS Word
  • How to Change a File Extension in Windows 10?
  • How to create a flipkart account in 3 simple steps
  • How to Change Monitor Refresh Rate in Windows 10?
  • How to install IIS on Windows 10
  • How to Insert Clickable Checkbox in MS Word 2016?
  • How to Set Auto Reply in Outlook 2016?
  • How to Login as Administrator in Windows 10?
  • How to Check RAM Size and System Type in Windows 10?

Recent Additions

  • How to Add Hyperlink to Another Sheet in Excel?
  • How to Add and Remove Watermark in Excel
  • How to use Relative and Absolute Cell Reference in Excel?
  • How to Enable and Disable Scroll Lock in Excel?
  • How to Use INDEX and MATCH Functions in Excel?
  • How to Make Bar Graph in Excel?
  • How to Count Duplicate Values in Excel?
  • How to Wrap Text in Excel?
  • How to Round off Numbers in Excel?
  • How to Create a Bell Curve in Excel?

Related Searches

  • How to install gmail offline app
  • How to translate a PDF file into any language
  • How to import Bookmarks in Mozilla Firefox from Google Chrome?
  • How we can Change the Directory in Ubuntu
  • How to Reduce Boot Time of Windows 7

Copyright © 2023 · QueHow · Contact Us