Being able to easily separate names in Excel can be a really valuable skill to have. Sometimes we will be working with a dataset that isn’t organised exactly how we want – for example a list of customers where their full name is in a single cell, when we want all of their details – including first and last name – to be their own values in separate columns.
Luckily there are a couple of nice and easy ways to do so in Excel. In this guide we will cover how to easily separate first and last names in Excel. We will cover:
- Separate first and last name using formulas
- Separate first and last name using the text to columns function
Before we get started, we do have to caveat that for these to work, the data we are splitting does need to be consistent in its structure. It doesn’t necessarily matter if the names are divided by a space, or maybe last name, a comma, and then first name, but both approaches rely on specifying the way they are set up.
Let’s get started with the first approach – the left and right formulas.
Separate Names in Excel using LEFT and RIGHT
The first approach we are going to cover involves making new new columns and using formulas. As mentioned above, we are relying on consistency in each name, so in our examples we have a space separating them.
Below we have a set of names where all five names are in a single column made up of first and last name:
To get started, we will pull out the first name of each person using a formula with the below syntax:
Essentially what this formula is doing, is starting at the very left of the cell (assuming the name is in A1), and then when pairing with a SEARCH formula it looks for the first blank space within cell A1 and stops there.
Using this formula on our dataset, we are then left with the below:
As we can see, this has perfectly split out the first name of each of our customers, stopping at the space between the names.
Moving on to the last name, this one is a little bit more complicated purely because when using RIGHT we need to add an extra step. The syntax is as follows:
To break this down, essentially what we need to do when working backwards is calculate the number of characters back we need to go, by layering in a LEN function to work out the overall length, and minus the difference between the overall length, and the length from the end back to the first space, which is again found by using the SEARCH formula.
Using our above example, if we simply did something like =RIGHT(B3,SEARCH(” “,B3)) it would actually find the first space from the left, but use characters from the right, we would just be left with venson as the output because it counted 5 characters from the word James. This guide sums up the text functions like LEFT and RIGHT quite nicely.
Using the same dataset as above, when paired with the first name formula, we are left with our final outputs as follows:
After this we can easily just copy and paste as values, delete the original column and we now have two columns in its place!
Now let’s move on to the second, and arguably far easier approach – text to columns.
Separate First and Last Name in Excel using Text to Columns
This second approach is far quicker to complete than the first, although it does require us making sure we have created enough blank space in the spreadsheet.
Using the initial example above, we need to have a second column set up to move the last name over in to. If its already blank then great, but if we have other columns to the right of the name make sure to insert a new one in between.
Essentially the way the text to columns feature works, is we select our delimiter (marker) of where we want to split at, and Excel does the rest.
To access text to columns, first highlight the cells that we want to separate, and under the Data ribbon select Text to Columns:
From here simply choose Delimited, and press next.
Now in our case make sure that Space is the only thing we have ticked. Or as mentioned earlier, if its a comma then you can use that instead.
Next up you can just leave this setting at General. We aren’t working with dates or anything where the overall structure is as important as its simply a name. So now press Finish.
You may or may not then be presented with a warning that there is already data here (in our case its because we have formatted the cells, so you may not get this) – if you are confident that the column to the right doesnt have data in it just press OK.
From here we are presented with the below result!
Super easy and we now have a column for first name and a column for last name. All we really need to do here is label the headers of the columns properly and we are good to go.
What is really handy about the text to columns feature, is that if we also had a middle name we could create two blank columns and it would split all three of them in to their own!
While the LEFT and RIGHT formulas are pretty powerful and can be tweaked for specifics where needed, in most cases the text to columns option is going to be a lot easier, and less fiddly as we simply need to just pick the marker that we want to split by and we are pretty much good to go.
This sums up our guide on how to separate names in Excel, with 2 easy approaches. For more handy guides on working with Excel, be sure to check out our Excel Tips page.