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.

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.

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

** 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.

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

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))**

**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.

- Find → to find blank space
- Len → to find total letters (including space)
- 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))**

Wow finally !!

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

## 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.

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*

## Carol O'brien

25 Aug 2016Sudheer, 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.

## Sudheer Tammini

25 Aug 2016Hi Carol,

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

## Chris

26 Aug 2016Sudheer, What if there are 2 First names like Mark Anthony Fernandez?

## Sudheer Tammini

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

## Bob Hawken

28 Aug 2016Hi 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.

## Sudheer Tammini

28 Aug 2016Hi 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.