How to Combine First and Last Name in Excel

In this simple guide, we will run through how to combine first and last name in Excel, using a couple of quick and easy formulas.

Before we get started, it is also worth noting that Excel also easily allows us to do the exact opposite of this! If we for any reason had a dataset with a combined name, and we wanted to separate this into multiple columns for a database, we could very easily to do in a couple of easy ways which we summarise in this article.

Why we would use Excel to combine names

A lot of Excel files are used for a range of purposes to track data such as demographic information, including names. Generally however an individuals first name, middle name, and last name are split into multiple columns.

In these instances, we may want to create an entirely new column that has a persons full name, whether its first and last name, first, last and middle name, or some combination of names and initials.

Depending on how many names we have to work with we could just type each person name out manually. When we start getting into the hundreds of rows though this becomes painfully tedious and time consuming.

This is where a couple of super handy formulas come in!

The formula to combine names in Excel

There are a range of ways we can use Excel formulas to combine a first and last name, but the two easiest are CONCAT (formerly known as CONCATENATE) and adding two specific cells together an ampersand.

Note that for both of the below formulas, not only do we need to include the cells themselves, but we are also required to add a blank space using quotation marks with an empty character.

Let’s start with the CONCAT formula. In older version of Excel this is known as CONCATENATE, and is still available as an option, however the CONCAT function has since replaced it.

If column B has a first name and column C has a second name, the formula to create a full name would look something like the below:

=CONCAT(B2, ” “, C2)

As we can see in the below examples, this formula quickly merges all first names and last names in a sheet:

The second approach works just as well, but rather than typing the word CONCAT, we use an ampersand after the first name and before the second name, such as:

=B2&” “&C2

This results in exactly the same output:

Let’s look at another example, but in a situation where we might want to only use the first initial of a persons first name, followed by their last name. In this case we can use the LEFT formula inside CONCAT. In this example we select the cell we want to use LEFT on, followed by the number of letters, in this case 1, and then follow it up with selecting the last name. For example:

=CONCAT(LEFT(B2,1),” “,C2)

This will result in the below:

As we can see, this formula can save us a lot of time when we want to display a specific output of a persons full name, when using a large document of individual strings.

Outside of combining a first and last name, we might also need to take formatting into consideration, which brings us to the next step.

Ensuring the output is formatted correctly

When we need to combine first and last name in excel for a specific output the formatting can be quite important. Depending on the source of our data, there is no guarantee that the names are formatted correctly in our spreadsheet. Some names might be in all capitals, some might be all lower case, or some might have a weird combination of the two.

This is where the PROPER function is a life saver! Proper basically takes a word and capitalizes the first character, in the same way that UPPER and LOWER make a word all caps or all lower case.

Using the same formula as above with cells B2 and C2, the formula would look like the below:

=PROPER(CONCAT(B2,” “,C2))

As easy as wrapping the whole formula with a single PROPER statement!

Using the same list of names above, this would result in:

This combination of CONCAT and PROPER helps ensure that even when the data we have to work with is a bit messy we can quickly and easily turn it into something far more useable.

This sums up our guide on how to combine first and last name in Excel.

For more handy guides on working with Excel, be sure to check out our Excel Tips page.