As we’ve all understood by now, Excel datasheets can contain a ton of datasets. Among them, information regarding various personnel is a rather common sight. It may involve information about students, employees, consumers, and so much more.
In these sets of data about people a pretty basic part is their names. Often, their first, last, and full names are divided in three columns. In this article, we’re going to talk about combining the first and last names in Excel.
There are actually a lot of ways to go about it. Not to worry, we’re going to cover it all. I hope you enjoy the read. So, let’s begin.
Contents
Ways to Combine First and Last Name in Excel
As I’ve said before, there are plenty of ways to complete the task at hand. Not every method might suit your needs. But I’m quite sure you’ll find the right one among the procedures we’re about to discuss. Let’s begin with the ones that involve using Formulas.
Combining Names in Excel Using Formulas
Using formulas to get the job done can really prove to be handy. There are multiple formulas that actually work efficiently. We’ll mostly be using the CONCATENATE function in these. Follow the steps that we’ll be describing and you should be alright.
Combining the First and Last Name Without Comma in Excel
Suppose, there are three columns in total. Two for the first and second name, and the third for the full name. Here’s how to get the results in the full name column (In this case, Column C).
- Here in Cell C2, we’re placing the formula =A2&” “&B2
- As you can see, we got the desired results without comma. So, you have to put the cells containing the first name, then place ampersand “&”.
- After that, you can’t miss the space character “ “.
- Once that’s done, you have to place ampersand again, and the cell name that contains the last name.
- And that should be it.
Note: You can also apply the CONCATENATE formula in cell C2. This will give you the same results in turn. Here’s the formula: =CONCATENATE(A2, ” “, B2)
Combining the First and Last Name with Comma in Excel
Here, the procedure is pretty much the same. The only difference here is, instead of the space character “ “, you have to place comma and space characters “, “.
- SO, according to our spreadsheet, the formula should look like this:
- The position of the cell numbers and ampersand remain the same. Only the space character is replaced with comma and space.
- Same goes for the CONCATENATE function. Just place this formula to get the similar results: =CONCATENATE(A2, “, “, B2)
Note: The formula will bear similar results if you type in CONCAT instead of CONCATENATE in the formula bar.
Combining Initial and Surname in Excel
To add the initial of the forename to the Surname, you’ll have to use the Left Function. This extracts the first letter from the forename and then allows you to concatenate it with the surname. Let’s see how it’s done.
- In our spreadsheet, the first name, or the forename is in cell A2 and the last name is in cell B2. So, our formula here should be: =LEFT(A2,1)&” “&B2. You can see the results in cell C2 below.
- To get the similar results without the use of ampersand, you can use the CONCATENATE function. The formula in this case will look like: =CONCATENATE(LEFT(A2,1), ” “, B2)
- As you can see, this too gives us the same results.
Note: If you want a dot after the initial, just type “. “ in the formula bar instead of just the space character.
Combining First and Last Name in Excel Using Flash Fill
While using formulas in combining first and last names, you’ll find that changes made in the other cells will result in changes in the cell that you combined the names in. If you don’t want that to happen, you can just use the Flash Fill feature in Excel. Here’s how to do it:
- The first thing you need to do to make this work is, type the first full name manually.
- Then, when you start to typing in the next row, Excel will immediately suggest names for the full column.
- That’s pretty much it. Press Enter to accept the suggestions, and you’re done.
Combine Names in Excel Using TEXTJOIN Function
The first thing you need to take into consideration is, the TEXTJOIN function is only available in Microsoft Excel 2019, Microsoft Office 365, and above versions. It can be used to combine cells way efficiently than the CONCATENATE and ampersand methods.
- Here, the formula you’ll need to apply is: =TEXTJOIN(” “,TRUE,A2:B2)
- Once applied, the results should look like this:
The Process
It might seem a bit complicated at first sight. But it really is handier than most other methods that we talked about. Allow me to explain how it works:
- The space character “ “ works as the delimiter. This needs to be there as we want to keep a space between the combined first name and last name.
- TRUE works as the Boolean value. Just in case there are blank cells, the formula will simply ignore it.
- As for the range of cells, they simply serve to identify the cells that you want to combine.
Some Tips That Will Help to Combine Names in Excel
These tips will help you achieve perfection in the procedure. There might be some issues even after you complete combine the names. Here’s how to solve them:
- While using formulas like =A2&” “&B2 there might accidentally come extra unforeseen spaces. In that case, you can use the trim Function to fix it. Just place this in the result cell and you should be fine: =TRIM(A2)&” “&TRIM(B2)
- Another issue might occur in capitalization of letters. In the first name or the last name, you might miss a capital letter in the beginning. In such cases, you can use this formula to make things work out: =PROPER(A2)&” “&PROPER(B2
As you can see. The “r” in Ray has been capitalized in cell C2.
Final Words
Now that we’ve gone through all the procedures that may prove to be useful to you, it’s time to wrap this up. The flash fill method might be the fastest method to get things done. However, if you prefer correctness, then perhaps the TEXTJOIN function is best suited.
So, which method should you pick in the end? Well, it’s a matter of personal preference to be honest. With that said, let’s not drag this along any further. Hope you enjoyed the read. Farewell for now.
Similar Post: