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