Using the CONCAT function

Available Files

The following files are available for download:

Download Exercise Files

The current data set has columns for both last name and first name. While this is optimal for sorting and analyzing data, it may occasionally be preferable to have the first and last names combined into a single column. The CONCAT function makes this task easy.

The typical syntax for the CONCAT function is as follows:

=CONCAT(text1,text2,...)

Because there is a space between the first and last names, it will be necessary to include that space in the function. This can be done by adding a space surrounded by double quotes. In addition, we will use the cell references for the first name (C2) and last name (B2). Therefore the syntax for this exercise will be as follows:

=CONCAT(C2," ",B2)

Before completing the CONCAT function, let's add a new column to contain the full names of the employees.

To select column D, in the header row,
Click
To add a new column,
Right-Click column D, Click Insert

To add the column heading,
Click in cell D1, type: Full Name
To accept this change, on the keyboard, press:
Enter key

With the new Full Name column in place, let's add the CONCAT function and insert the full names of the employees.

To make cell D2 the active cell, if necessary,
Click in cell D2
To enter the CONCAT function, type:
=CONCAT(C2," ",B2) Enter key

The first employee's name, Motonari Kawamoto appears in the Full Name column. The column may not be wide enough at this point. We will widen the columns after we have all of the names in place.

Let's use the autofill handle to copy the CONCAT function down column D.

To return to cell D2, on the keyboard, press:
Up Arrow key
To use the autofill handle to copy the formula to the rest of column D,
Double-Click

To widen column D, in the column headers,
Double-Click

The employees' full names are now in column D.

To save the workbook, on the keyboard, press:
Control key + S

Using the CONCAT function

Available Files

Step 1. Add a new column after column C.

Step 2. Add the column heading, "Full Name", to cell D1.

Step 3. Use the CONCAT function to combine the first and last names.

Step 4. Copy the CONCAT function down column D.

Step 5. Widen column D to fit all of the contents.

Step 6. Save the workbook.