You may have an Excel workbook that contains first and last names in separate cells, but you want them combined into a single cell. There are multiple methods to merge or combine First and Last Names in Excel, and in this post we will cover them.
How to combine First and Last Names in Excel
Combining or merging the first and last names in an Excel spreadsheet or workbook requires using the CONCAT and Ampersand functions or formulas. You can also use Flash Fill and TEXTJOIN instead. Let us see each of the methods.
- Use the CONCAT Function
- Make use of Flash Fill
- Use the Function known as TEXTJOIN
- Use the Ampersand symbol in Excel
1] Use the Excel CONCAT Function
Excel has a function known as CONCAT, and it can be used to combine multiple contents in cells into a single cell. We suggest using a function for this task because it can make your formula much easier for others to understand when need be.
To combine first and last names with CONCAT in any given workbook, choose the cell where you’d like the merged names to appear.
From within the selected cell, you must enter the following function, then hit the Enter key.
Bear in mind that you may have to replace B2 with the cell that contains your first name, and do the same for C2 with the cell that houses your last name.
Now, once the Enter key is pressed, both names should merge right away. Also, if you drag from the bottom-right corner of the selected cell where the function is located, you can copy the function to other cells to quickly update your records.
Additionally, if you want to merge the first and last names with the middle name initial, then use this function:
Folks who prefer the last name to come ahead of the first name, then please make the relevant changes and use the following:
2] Make use of Flash Fill
Microsoft Excel has a feature called Flash Fill, and it is capable of automatically filling cells by identifying the fill pattern. All you have to do is manually fill the cell with the relevant data, and from there, the feature picks up on the data and fills the selected cells automatically and in accordance with your needs.
No formula is needed here, so Flash Fill is best for novices who are relatively new to Microsoft Excel.
To use the Flash Fill feature, please select the first cell where the combined names will be displayed.
Go down to the next row and begin typing the first and last name in the record. Right away Flash Fill will automatically sense the pattern and fill in the records in a gray color. If you want to use what is suggested, hit the Enter key and the cell will then be filled.
3] Use the Excel TEXTJOIN Function
Another function good enough for combing names in a spreadsheet is no other than TEXTJOIN. Before going forward, this function is more complicated to use than CONCAT, but that’s because it’s more powerful, therefore, it’s more geared towards advanced users.
When it comes down to using the TEXTJOIN, you must first click the cell where you want the merged names to appear.
Next, you must type the following function promptly:
Bear in mind you must replace B2 with the correct cell where the first name is stored. Do the same with C2 where the last name is concerned.
If you want to ignore empty cells, then please change the False value to True.
4] Use the Ampersand symbol in Excel
Finally, we want to look at another important method where folks must use the Ampersand symbol, or to be less complicated, the & symbol.
Ampersand is for those who prefer to use an Excel formula to bring names together. By using the & (ampersand) operator, users can bring the content of multiple cells into a single cell.
To use this feature, please select the cell where you want the merged names to appear.
From within that cell, type the following formula and press the Enter key:
You will want to replace B2 with the preferred cell that contains the first name. Next, replace C2 with the cell that holds the last name.
You can copy this method for other records on your spreadsheet by clicking the bottom-right corner of the current cell, then drag it down. This will automatically convert the others without you having to manually add the formula.
READ: How to use the COUNTA function in Excel
How can I combine columns in Excel?
First, you must click where the combined cells data will go.
Type…… then click the first cell you want to combine.
Type , then click the second cell you want to have combined and type ).
Press the Enter key, and that’s it, you’re done.
Which function or operator is used to combine first and last name?
The most useful function and operators required for this purpose, are CONCATENATE, and Ampersand. These will work on Excel 2016 and newer, so bear that in mind when going forward.