The Concatenate Function

Concatenate is one of the most effective ways of combining data in Microsoft Excel.

Fundamentally there are two ways in which you can combine data in Excel spreadsheets, you can:

Merge cells or,
Concatenate cell values

When you merge cells, you are “physically” merging two or more cells into a single cell. As a result, you have one larger cell that is displayed across multiple rows and/or columns in your worksheet.

When you concatenate cells in Excel, you combine only the contents of those cells. Strictly speaking, concatenation in Excel is the process of joining two or more values together. This method is often used to combine a few pieces of text that reside in different cells (technically, these are called text strings or simply strings) or insert a formula-calculated value in the middle of some text.

Below is a short tutorial on how to effectively use the concatenate function:

Step 1:

Consider the following list that has both First & Last name columns. We will use concatenate function to combine both the names to create a "Full Name column".

Step 2:

excel exert

Select a cell (D2) where you want to put your Full Name. Then click the Insert Function command in Formula Tab and find Concatenate Function. Click OK.

Step 3:

The CONCATENATE dialog box pops up on the screen and the Formula Name appears in the cell. Notice that, this function has two arguments

Step 4:

Select first argument (Text1) and then click on First Name (Brian). Address of the cell (B2) appears in the box.

Step 5:

excel exert

Select the second argument (Text2) and notice that a third argument (Text3) automatically appears in the dialog box. Type “ “ in the box. By doing this, we instruct our function to put one space between the names.

Step 6:

Now select 3^rd argument and click on Last Name (Houson). Address of the cell (C2) pops up in the tab.

Step 7:

Click OK and you will see both names have been combined with a space in between.

Step 8:

excel exert

Point towards bottom right corner until you see a four-headed black arrow and double click. Your entire column is populated with Full Names!