How to Split First and Last Name in Excel [with examples]

In this step by step tutorial, we are going take a look at how to split first and last name in excel with detailed examples.

Data mining is a cumbersome work!

You need to have a better understanding of various excel text formulas and tools like the Text to Columns to deal with the situation.

How to split First and Last Name in Excel using formulas

If you happen to be the one who lacks these skills, then no worries, you’ll certainly have a better understanding after reading this tutorial.


Guide to Split First and Last Name in Excel

Let me start with a simple example where you just need to separate employee first name in one column & second name in the other column.

This is how our data will look like.

split names in excel_Example #1

For this example, we will use built-in Text to Columns option to separate names.

Step 1: Select A2:A10 → head over to Data → Click on Text to Columns

Step 2: Select Delimited & click next

Step 3: Under Delimiter section → enable ‘Space’ checkbox

Step 4: Click on next → click on Finish

That’s it.Excel text to columns_Example #2

 Pro Tip: In case if you don’t have space in your data, you can enable Other Delimiter and specify accordingly.


 Excel separate first and last name using formulas [Right & Left]

There is no doubt, Text to columns is a great tool to separate names in excel, but it has a drawback.

The drawback is, it’s not a live tool.

Suppose, if your data is constantly changing then you will have to do same steps again and again.

But…

The good thing is you can automate using excel text formulas.


1) Separate first name using Left formula

In simple terms, Left is a formula to extract user specified letters from the left side of the text.

For example, to extract ‘Timothy’ from ‘Timothy Fox’ you can write a formula like below:

=LEFT(A2,7)

which means, we are instructing excel to extract first 7 letters from cell A2 (Timothy Fox). Awesome!

Hang on, there is a problem here.

Since we just have one name we were able to count the total number of letters to extract.

what if you have thousands of names in your workbook?

Can you count? The answer is no!

So, we have a workaround for that.

We will use FIND() formula to identify the number of letters to extract.

Also read: The Ultimate Guide to Excel Keyboard Shortcuts


How to use FIND formula to count number of letters to extract

Step 1: Excel find function example

Insert FIND formula in first name section

Step 2: 

Specify ‘Find Text’ (for this example, we will use blank space)

Step 3:

Provide ‘within text’ range. Now you’ll see find formula will return blank space position

Step 4: 

Now that we know how many letters to extract, let’s add left formula to separate the first name.

Add left formula before Find like this =LEFT(A2,FIND(” “,A2))

How to extract first name in excel


2) Separate last name using Right formula

The other part of this tutorial is to split the last name using Right formula.

The functionality of Right formula is similar to Left but the only change is it will extract text from the right side of the text.

For this, we need to use three formulas.

  1. Find → to find blank space
  2. Len → to find total letters (including space)
  3. Right → to split the last name

Step 1:

Find: please follow same steps as explained above

= FIND(” “,A2)

Step 2:

Len: Insert LEN formula to count total letters in your text (it will be including any blank spaces/ special characters).

=LEN(A2)

Step 3:

Now subtract Total letters i.e. LEN – FIND (letters till blank space).

=LEN(A2)FIND(” “,A2)

Step 4: 

After the third step, you will have to exact the last name from right side → add Right formula like below

=RIGHT(A2,LEN(A2)-FIND(” “,A2))

Separate last name using excel Right formula

Wow finally !!

We know how to split first and last name in excel using formulas. Great !

Also Read: How to create Planned vs Actual Chart in Excel 

How to separate middle name in excel using formulas:

I’ve seen few comments asking how to split middle name using formulas. So here is a tutorial for that.

For example, if we have a name like “Mark Anthony Fernandez” then how will you split?

Step 1: 

In this example, we have three names (first, middle & last name), by now you know how to extract first & the last name using Right & Left formulas. So, the only issue is extracting middle name.

Step 2:

We’ll use Substitute formula like below to add multiple spaces (for this example I’m adding 50 blank spaces after Anthony).

=SUBSTITUTE(A1,” “,REPT(” “,50),2)

Step 3: 

Now, we’ll use MID formula to extract middle name like below .

=MID(SUBSTITUTE(A1,” “,REPT(” “,50),2),FIND(” “,A1),50)

Step 4:

Let’s add TRIM formula to remove unwanted spaces.

=TRIM(MID(SUBSTITUTE(A1,” “,REPT(” “,50),2),FIND(” “,A1),50))

After all the steps your result will look like this.

How to split middle name in excel

In case if you have a better way to extract middle name using formulas please let me know.

Conclusion:

As promised, by now you know how to split first and last name in excel using various methods. For ad-hoc reporting, I’d suggest using text to columns functionality.

In case if you are building financial models then it’s a good idea to use above explained formulas to separate names in excel.

Now it’s your turn to apply this knowledge to your work. In case if you have any questions/suggestions please let me know.

Stay connected with us via our Facebook page & Twitter

This Post Has 6 Comments

  1. Sudheer, thank you for the FIND recommendation. I will use this monthly from now on. I knew about the LEFT and RIGHT functions but as you explain, this is the solution for variable string lengths. My thanks.

    1. Hi Carol,

      Good to hear that, let me know if you need any help.

  2. Sudheer, What if there are 2 First names like Mark Anthony Fernandez?

    1. Chris – there is formula to split middle name as well, I’ll include that in my above article, please refer and let me.

  3. Hi Sudheer

    When I need to split out middle names with a formula (rather than text to columns) I would use the functionality of FIND that lets you find the second instance of a character (such as a space character).

    You are quite right that =FIND(” “, A1) will find the first space. However, you can indicate where to start looking in a FIND function. If you start looking immediately after the first space (=FIND(” “, A1)+1) then you will find the position of the second one:

    =FIND(” “, A1, FIND(” “, A1)+1)

    The MID function allows you to specify the start point and number of characters. You now have both of these as the first space is the start point (+1) and the number of characters is the second space position – first space position – 1. The formula becomes:

    =MID(A1,FIND(” “,A1)+1,FIND(” “,A1,FIND(” “,A1)+1)-FIND(” “,A1)-1)

    The advantage of this formula over yours is that it does not contain the random 50 within the formula.

    I do like your use of the TRIM function which would allow you to use less of the +/- 1 figures in my formula above.

    Overall though I prefer to use several columns to do this exercise (one for the first space, one for the second space and one for the overall answer) as this makes it much more auditable.

    As a final point don’t forget that not everyone has a middle name and so an extra check should be included to ensure that there is a second space in the name and if not to include a null return.

    1. Hi Bob – First let me say thank you for the detailed explanation. I should say your approach is awesome.

      Yes, your right using a substitute for adding random 50 spaces no longer needed results in faster processing time (although, doesn’t matter for fewer data).

      Going forward I’ll try to use your approach in my regular work.

Comments are closed.