Md. Araf Bin Jayed
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.
Download Practice Workbook
You can download the free Excel workbook here and practice on your own.
5 Handy Ways to Merge Two Columns in Excel with First Name and Last Name
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.
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
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.
Read More: How to Merge Two Columns in Excel Without Losing Data
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)
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)
Step 2:
- Secondly, after pressing Enter and using the AutoFill you will see all the names have been merged in column D.
Read More: How to Join Two Columns in Excel (5 Easy Methods)
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.
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.
Read More: How to Merge Columns in Excel (4 Ways)
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.
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.
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.
The ExcelDemy team is always concerned about your preferences. Moreover, you need to keep in mind that the submitted comments need to be approved. Therefore, after commenting, be patient and we will reply to your queries as soon as possible.
