Merge Two Columns in Excel with First Name and Last Name

Get FREE Advanced Excel Exercises with Solutions!

In many instances, users have to handle or exploit data in Excel in different ways. One such instance is to merge the first name and last name of people from two different columns. By using some formulas or built-in features of Excel, this task becomes quite easy. In this article, we will show you how to merge two columns in Excel with first name and last name.


Merge Two Columns in Excel with First Name and Last Name: 5 Handy Ways

The process of merging two columns with first name and last name is quite simple and easy. You can use some Excel formulas or built-in features of Excel to achieve your goal. In our article, you will see five different methods to merge two columns in Excel with the first name and last name.

Firstly, we will use the ampersand operator; then apply the CONCATENATE function in the second method; thirdly, we will utilize the TEXTJOIN function; after that, we will insert the Power Query feature of Excel; and lastly, utilizing the Flash Fill feature of Excel will help us solve our problem. To illustrate our procedure, we will take help from the following data set. The first and last names of a few random people are listed in columns B and C, respectively.

5 Handy Ways to Merge Two Columns in Excel with First Name and Last Name


1. Using Ampersand to Merge Two Columns in Excel with First Name and Last Name

In our first method, we will merge two columns with the first name and last name in Excel using the ampersand (&). This operator will join the values of two cells without the help of any other formula or function. The detailed steps for this procedure are as follows.

Step 1:

  • First, in column D, add a new column called Full Name.
  • Then, type the following formula into cell D5.
  • Consequently, this will merge the values from the two columns with a space.
=B5&" "&C5

Using Ampersand to Merge Two Columns in Excel with First Name and Last Name

Step 2:

  • Secondly, press Enter and you will see the full name of the first person.
  • Then, with the help of the AutoFill feature, drag the formula to the lower cells.


2. Applying CONCATENATE Function to Merge Two Columns

The second method relies on the use of the CONCATENATE function to achieve the goal. Here, this function will serve the same purpose as the ampersand, thus merging the first and last names. Follow the below-given steps for the detailed procedure.

Step 1:

  • Firstly, in cell D5, use the following formula. This function will merge the two columns with a space.
=CONCATENATE(B5," ",C5)

Applying CONCATENATE Function to Merge Two Columns in Excel with First Name and Last Name

Step 2:

  • Then, hit the Enter button to see the final result.
  • Consequently, drag the Fill Handle to show the results for the lower cells in that column.


3. Utilizing TEXTJOIN Function to Merge Two Columns

Another useful function to merge two columns with a first name and last name is the TEXTJOIN function. This function will join the cells of a given cell range. See the following steps to understand the whole procedure.

Step 1:

  • In the beginning, type the following formula into cell D5.
  • Here, we have set the formula to ignore blank cells by using the TRUE parameter.
=TEXTJOIN(" ",TRUE,B5:C5)

Utilizing TEXTJOIN Function to Merge Two Columns in Excel with First Name and Last Name

Step 2:

  • Secondly, after pressing Enter and using the AutoFill you will see all the names have been merged in column D.


4. Inserting Power Query Tool to Merge Two Columns in Excel with First Name and Last Name

In this method, we will use an amazing feature of Excel, which is the Power Query tool. This feature is used for withdrawing and altering data. We can use this feature to merge two columns with the first name and last name. You will find the detailed steps for this procedure in the following.

Step 1:

  • Firstly, to use the feature, we first have to convert our data into a tabular format.
  • For that, choose any cell from the data range of your data set.
  • Then, from the Insert tab of the ribbon, choose Table.

Inserting Power Query Tool to Merge Two Columns in Excel with First Name and Last Name

Step 2:

  • Secondly, you will see the Create Table dialogue box.
  • Here, insert the data range of the whole data set.
  • Then, mark the box “My table has headers”.
  • Lastly, press OK.

Step 3:

  • Fourthly, you will see your data set in tabular format.
  • Then, go to the Data tab of the ribbon.
  • From there, select the From Table/Range command from the Get & Transformed Data group.

Step 4:

  • Fifthly, the previous command will launch the Power Query Editor.
  • Then, select both columns by clicking Shift and the left button of the mouse.

Step 5:

  • Then, after selecting both columns, right-click on the mouse.
  • After clicking several options regarding the data will appear.
  • From there, choose Merge Columns.

Step 6:

  • After that, you will see the Merge Columns dialogue box.
  • Then, in the Separator dropdown choose Space.
  • Secondly, name the new merged column as Full Name.
  • Lastly, press OK.

Step 7:

  • Then, after clicking OK, you will see the names merged into one column.
  • After that, click on Close & Load to transfer this result into the main worksheet.

Step 8:

  • Finally, the final result after completing all the steps will look like the image below.


5. Utilizing Flash Fill to Merge Two Columns in Excel

Another amazing feature of Excel is the Flash Fill. This feature can observe the specimen in the users’ data set and then fill the lower cells in the same pattern. We will show the use of this feature as our last method to merge two columns in Excel with first name and last name. The steps for this procedure are given below.

Step 1:

  • First of all, in column D, under the Full Name header, type the full name of the first person manually in cell D5.

Utilizing Flash Fill to Merge Two Columns in Excel with First Name and Last Name

Step 2:

  • Secondly, come to cell D6.
  • Then, start to write the name of the second person.
  • Consequently, the Flash Fill feature will read the pattern from the previous cell and suggest the full names for the lower cells.
  • Lastly, click on the pattern. Alternatively, you can press Enter to do so.

Step 3:

  • Finally, you will be able to merge all the names with the help of this feature.


Download Practice Workbook

You can download the free Excel workbook here and practice on your own.


Conclusion

That’s the end of this article. We hope you find this article helpful. After reading the above description, you will be able to merge two columns in Excel with the first name and last name by using any of the above-mentioned methods. Please share any further queries or recommendations with us in the comments section below.


<< Go Back to Merge Columns in Excel  | Merge in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Araf Bin Jayed
Md. Araf Bin Jayed

I am Araf. I have completed my B.Sc in Industrial and Production Engineering from Ahsanullah University of Science and Technology. Currently I am working as an Excel & VBA Content Developer in Softeko. With proper guideline and aid of Softeko I want to be a flexible data analyst. With my acquired knowledge and hard work, I want to contribute to the overall growth of this organization.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo