• Skip to main content
  • Skip to primary sidebar

Techbout

Technology Simplified.

  • Windows
  • iOS
  • Mac
  • Android
  • Office
Home » Windows 10 » How to Use Concatenate Function in Excel

How to Use Concatenate Function in Excel

By: Waseem Patwegar

In this article, we are taking a look at how to effectively use Concatenate Function in Excel to combine contents of multiple cells or to add Prefix and suffix to a group of cells in Excel Worksheet.

Use Concatenate Function in Excel

Use Concatenate Function in Excel

The Concatenate Function in Excel is mostly used to join or combine contents of multiple cells into a single cell.

For example, if you have a worksheet containing the First Names in one Column and the Second Names in another column, you can use the Concatenate Function to combine the First and Second Names into a third column.

The Concatenate Function can be used to join up to 255 strings containing up to a maximum of 8,192 characters, which makes it really useful in complex worksheets.

In addition to combining contents, the Concatenate Function can also be used for adding Prefix and Suffix to a group of cells in Excel.

Syntax of Concatenate Function

The Concatenate function joins text strings into one text string and the Syntax of the Concatenate function in Microsoft Excel is as provided below.

CONCATENATE (text1, text2, … text_n)

text1, text2, etc. can be text strings, numbers or single-cell references.

Combine Content of Multiple Cells Using Concatenate Function

Let us take the case of an Excel Worksheet having the “First Names” of students in Column A and “Second Names” in Column B.

Now, the task is to combine the First and Second Names, in order to write the Full Names of all the students in Column C.

To combine the First and Second Names, type =Concatenate(A4,” “,B4) and hit the enter key on the keyboard of your computer.

Concatenate Function in Microsoft Excel

Note: ” “ is being used in the above formula to create a space between the first and second name.

Once you combine the first and second names in the first cell, you can quickly combine all the remaining cells by dragging the formula down for all the required cells.

Combine Content in Group of Cells Using Concatenate Function

Use Concatenate Function to Add Prefix to Group of Cells in Excel

In this case, let us assume that you have the Names of doctors in an Excel Worksheet and the task is to add a common Prefix “Dr.” in front of all the Names in Column A.

To Add Prefix, type =Concatenate(“Dr. “,A4) and hit the enter key on the keyboard of your computer.

Add Prefix in Excel Using Concatenate Function

Tip: Instead of typing A4 in the above formula, you can move the cursor to A4 cell

Once Prefix is added to the first cell, you can quickly add this common Prefix to all the remaining Cells in the Excel worksheet by dragging the formula to all the remaining cells.

Add Suffix in Excel Using Concatenate Function

After adding the Prefix (Dr.), let us now assume that you are also required to add a common Suffix “PHD.” to the end of all the names in Column B.

To Add Suffix, place the cursor in Column C and type =Concatenate(B4,” PHD.”) and hit the enter key on the keyboard of your computer.

Add Suffix in Excel Using Concatenate Function

After adding the Suffix in the first cell, you can quickly add the Suffix to all the cells by dragging the formula to all the remaining cells.

Related
  • How to Delete Empty Rows in Excel Worksheet
  • How to Use SUMIF Function in Excel

You May Also Like
    • Prevent Outlook From Adding Calendar Events
      How to Prevent Outlook From Adding Calendar Events
      • Show Developer Tab in Excel Ribbon Menu
        Add or Show Developer Tab in Excel Ribbon Menu
        • Set Out of Office Message in Microsoft Teams
          How to Set Out of Office Message in Microsoft Teams

Primary Sidebar



Recent Posts

  • Fix: iPhone Camera Black Screen Problem
  • How to Clear Your Activity History In Windows 11
  • How to Find WiFi Password in Windows 11
  • How to Enable Touch Keyboard in Windows 11/10
  • How to Send Group Email In Gmail App On iPhone




About | Privacy Policy | Disclosure | Terms | Contact
© 2023 Techbout.com · All Rights Reserved