How to Combine First and Last Names in MS Excel

Excel is a versatile tool to store, analyze, and manipulate data. A common task is to merge the first and last names, stored in two separate columns, into a single cell. Fortunately, Excel offers four easy-to-use formulas to help you combine the first and last names.

4 Methods to combine first and last names in Microsoft Excel

  • Use the CONCATENATE function 
  • Use the TEXTJOIN function
  • Use the Ampersand operator
  • Use the Flash Fill function

Let us begin by having a look at each of these methods one by one.


Method #1: Use the CONCATENATE Function

Combining the first and last name using the built-in CONCATENATE function in Excel is quite easy.

Step #1: Open the Excel sheet 

Open the Excel sheet in which you have a list of first names and last names.

Step #2: Enter the formula

Double click on the cell next to the first Last Name. In our example, that would be C2.

Type the formula =CONCATENATE(First Cell Number,” “,Second Cell Number)

  • First Cell Number would be the cell containing the First Name. In our example, that would be A2.
  • Second Cell Number would be the cell containing the Last Name. In our example, that would be B2.

To insert a space between the two names, you need to use a second argument, i.e., space.

In our example, the formula is:

=CONCATENATE(A2,” “,B2)

Step #3: Press enter to get the result

Press enter to get the result for the first entry in the Excel sheet.

Once the first two fields have been combined, place your cursor on the cell with the results. In our example, that would be C2.

A green frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be C8.

Release the mouse button.

Full names will show in column C.


Method #2: Use the TEXTJOIN Function

The Excel TEXTJOIN function is available in Excel 2019 and Office 365. If you have access to this Excel function, it’s simpler to use than the CONCATENATE function. 

Step #1: Open the Excel sheet 

Open the Excel sheet in which you have a list of first names and last names.

Step #2: Enter the formula

Double click on the cell next to the first Last Name. In our example, that would be C2.

Type the formula =TEXTJOIN(“ “,TRUE,First Cell Number:Second Cell Number)

  • First Cell Number would be the cell containing the First Name. In our example, that would be A2.
  • Second Cell Number would be the cell containing the Last Name. In our example, that would be B2.

=TEXTJOIN(“ “,TRUE,A2:B2)

Step #3: Press enter to get the result

Press enter to get the result for the first entry in the Excel sheet.

Once the first two cells have been joined, place your cursor on the cell with the results. In our example, that would be C2.

A green frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be C8.

Release the mouse button.

Full names will show in column C.


Method #3: Use the Ampersand Operator

The Excel Ampersand Operator can also be used to merge the first and last name.

Step #1: Open the Excel sheet 

Open the Excel sheet in which you have a list of first names and last names.

Step #2: Enter the formula

Double click on the cell next to the first Last Name. In our example, that would be C2.

Type the following =First Cell Number&” “&Second Cell Number

  • First Cell Number would be the cell containing the First Name. In our example, that would be A2.
  • Second Cell Number would be the cell containing the Last Name. In our example, that would be B2.

To insert a space between the two names, you need to use a second argument, i.e., space.

In our example, the formula is:

=A2&” “&B2

Step #3: Press enter to get the result

Press enter to get the result for the first entry in the Excel sheet.

Once the first two fields have been combined, place your cursor on the cell with the results. In our example, that would be C2.

A green frame will appear around the selected cell with a block in the bottom right corner. Click on that block, hold the mouse button in, and drag the cursor downwards to the last cell next to the last date. In our example, that would be C8.

Release the mouse button.

Full names will show in column C.


Method #4: Using the Flash Fill function

Excel’s Flash Fill is a smart function that understands the pattern you want and gives you the desired output. It is the easiest of all the methods mentioned above, ideal for when you are not dealing with a huge amount of data.

Step #1: Open the Excel sheet 

Open the Excel sheet in which you have a list of first names and last names.

Step #2: Type in the first name and the last name

In the adjacent cell, C2, type in the first name and last name.

Step #3: Accept the Excel suggestion

Likewise, enter the details in the next cell, C3, as well.

After typing the first few letters, you will observe that Excel will suggest the combination you want. It will look like a grayed out text box, with the complete entries you want.

Accept the Flash Fill suggestion by pressing Enter on your keyboard.


Conclusion

Use the methods given above to easily combine the first and last names in Excel. This will save you a lot of time and cut down on manual effort.

Leave a Comment