Combining First Name and Last Name in Microsoft Excel ( P2 )
Combining First Name and Last Name in Microsoft Excel: To merge first name and last name in Excel, enter the desired combined name in the first cell as you want it to appear, then use Flash Fill to automatically combine the remaining names. Additionally, you can have better control by combining your name cells using a formula or the Power Query tool.
Do you want to put the first name and last name in separate cells into one cell? Text merging is very easy in Excel, and we will show you five different ways to combine names.
Method 3: Using the CONCAT Function
The CONCAT function in Excel works similarly to the & operator and allows you to combine the contents of multiple cells into a single cell. Using the function instead of the operator can sometimes make it easier for others to read and understand your formula.
To combine your first and last name with CONCAT, select the cell where you want to display the combined names in your spreadsheet.
Select a cell.
In the selected cell, enter the following function and press Enter. In this function, replace B2 with the cell containing your first name and C2 with the cell containing your last name. Note that we have separated the first and last names with a space in this function.
=CONCAT(B2," ",C2)
After pressing Enter, in the selected cell, you will see both your names combined. You can copy the formula for all your records by dragging it down from the bottom right corner of the cell containing the formula.
Combined name.
If you also want to combine a middle name or initials, use the function as follows. Here, C2 represents the middle name cell, while D2 contains the last name.
=CONCAT(B2," ",C2," ",D2)
Alternatively, if you want the last name to come first, try swapping and inserting a comma around the formula like this:
=CONCAT(D2,"", ",B2," ",C2)
And that’s it.
Method 4: Using the TEXTJOIN Function
With the TEXTJOIN function, you can combine your names using a specified delimiter. This function also provides an option to ignore empty cells. It may be a bit more complex than using CONCAT, but it is also a more powerful function.
To use TEXTJOIN, in your spreadsheet, click on the cell where you want to display the combined names. Then, enter the following function in the cell. Here, replace B2 with the cell containing the first name and C2 with the cell containing the last name. To ignore empty cells, change FALSE to TRUE.
=TEXTJOIN(" ",FALSE,B2,C2)
Combining names with TEXTJOIN.
If your data set includes a first name, middle name, and last name in cells B2, C2, and D2 respectively, you can concatenate them using the following function:
=TEXTJOIN(" ",FALSE,B2,C2,D2)
Method 5: Using Power Query
Power Query is a feature in Excel that helps you combine data from multiple worksheets and workbooks. You can also use this option to merge your columns.
To use this method, first click on a cell in your data set. Then, from Excel’s ribbon at the top, select Data > From Table/Range.
Select Data > From Table/Range.
In the “Create Table” dialog box, make sure the range of your main data set is correctly specified. If your data set has column headers, enable the “My table has headers” option. Then, select “OK.”
Verify the data range and select “OK.”
A “Power Query Editor” window will open. Here, hold the Ctrl key (Windows) or Command key (Mac) and select your first and last name columns. Then, right-click on one of the columns and choose “Merge Columns.”
Select “Merge Columns” from the menu.
In the “Merge Columns” window, click on the drop-down menu for “Separator” and select “Space.” Optionally, in the “New column name” field, enter a name for the column that will contain your merged name. Then, select “OK.”
Customize the merge and select “OK.”
In the top-left corner of the “Power Query Editor” window, click on “Close & Load.”
Select “Close & Load” in the top-left corner.
In your worksheet, Power Query will add a new worksheet containing your merged names.
Names merged with Power Query.
Combining First Name and Last Name in Microsoft Excel ( P2 )
Now you have a complete spreadsheet with readable full names for each entry. If you’re entering multiple names and other data, explore the best Excel functions for data entry.
