Separate Names in Excel (Split First & Last Name)

puneet-gogia-excel-champs

- Written by Puneet

Often we face a situation where we need to separate words (first name and last name) (get a word form a cell/text string) in Excel.

Let’s say you want to extract the first name from a cell where you have both first and last names or you want to extract the last name from the same cell.

We do need a function that can extract this. The bad news is, in Excel, there is no specific function to split the first and the last names from a cell directly.

But the good news is you can combine functions and create a formula to get these words. Today in this post, I’d like to share with you how to get separate names using a formula

Extract First Name in Excel

Extracting the first name from a text string is much easier than extracting the last name. For this, we can create a formula by combining two different text functions, that’s SEARCH and LEFT.

Let’s understand this with an example. In the below table, we have a list of names which includes the first and the last name.

And now from this, we need to extract the first name which is the first name in the cell.

data to get last and first name min

And the formula to get the first name from the above column is:

=LEFT(A2,SEARCH(" ",A2)-1)

This simply returns the first name which is the first name from the text.

get first word from full name min

In the above example, we have used a combination of SEARCH (It can search for a text string in another text string) and LEFT (It can extract a text from the left side).

How it works

This formula works in two different parts.

formula combination of left and search to get first word from a text string from a cell in excel min

In the first part, the SEARCH function finds the position of the space in the text and returns a number from which you have deducted one to get the position of the last character of the first name.

how combination of search and left works to get first word from a text string from a cell in excel min

In the second part, using the number returned by the SEARCH function, the LEFT function extracts the first name from the cell.

first word from a text string from a cell in excel return by this formula min

Deal with Single Word Cells

If the cell contains text without spaces (i.e., a first name only), you can modify the formula to handle this situation:

=IF(ISERROR(SEARCH(" ", A1)), A1, LEFT(A1, SEARCH(" ", A1)-1))

This formula checks if there’s a space in the text in cell A1. If there isn’t any space, it returns the whole text from A1.

If there is a space, it finds where the first space is and gives you everything before that space. Essentially, it gets the first name from the text in A1. If A1 has only one word without any spaces, it returns that word.

Extract Last Name in Excel

Getting the last name from a text string can be tricky for you, but once you understand the entire formula it will be much easier to use it in the future.

So, to extract the last name from a cell you need to combine RIGHT, SUBSTITUTE, and REPT. And the formula will be:

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),LEN(A2)))
formula combination to get last word from a text string from a cell in excel min

This formula returns the last name from the cell which is the last name and works the same even if you have more than two words in a cell.

How it Works

Again, you need to break down this formula to understand how it works.

how formula combination to get last word from a text string from a cell in excel works min

In the FIRST part, the SUBSTITUTE function replaces every single space with spaces equal to the length of the text.

first part of formula to get last word from a text string from a cell in excel min

The text becomes double in length with extra spaces and looks something like this. After that, the RIGHT function will extract characters from the right side equal to the original length of the text.

second part of formula to get last word from a text string from a cell in excel min

In the end, the TRIM function removes all the leading spaces from the text.

third part of formula to get last word from a text string from a cell in excel min

Custom Functions to Extract First Name and Last Name in Excel

You can also extract the first name from a text and write a custom function using VBA. This function will be useable like any other Excel function directly within your worksheet.

Function GetFirstName(text As String) As String
    Dim spacePosition As Integer

    ' Find the position of the first space
    spacePosition = InStr(text, " ")
    
    ' Check if there is a space
    If spacePosition > 0 Then
        ' Return the substring from the start to the position before the first space
        GetFirstName = Left(text, spacePosition - 1)
    Else
        ' If no space, return the whole text
        GetFirstName = text
    End If
End Function
=GetFirstName(A1)

Open Excel and press ALT + F11 to open the VBA editor. On the left side, right-click on your workbook name, select Insert, and then Module.

Copy and paste the VBA function provided into the large white space that appears.

Close the VBA editor and return to your Excel worksheet. In your worksheet, type =GetFirstName(A1) (replace A1 with the cell you want to extract word) to use the function.

And for the last name:

Function GetLastName(text As String) As String
    Dim spacePosition As Integer
    
    ' Trim the text to remove any leading or trailing spaces
    text = Trim(text)
    
    ' Find the position of the last space
    spacePosition = InStrRev(text, " ")
    
    ' Check if there is a space
    If spacePosition > 0 Then
        ' Return the substring from the position after the last space to the end of the string
        GetLastName = Mid(text, spacePosition + 1)
    Else
        ' If no space, return the whole text
        GetLastName = text
    End If
End Function

Split Names using Text to Column

Text to Column is an option that separates data in a single column into multiple columns. You can select the column with the data, choose a separator like a space or a comma, and it splits the data based on that separator.

  1. Click on the column header containing the names to select the entire column. You can also select a cell of that column.
  2. Go to the “Data” tab on the Ribbon. In the “Data Tools” group, click the “Text to Columns” button.
  3. In the “Convert Text to Columns Wizard”, select the “Delimited” option and click “Next.”
  4. Choose the delimiter that separates the names. As we need to separate the first and the last name, you can select a space for that.
  5. In the next step, select the way you want the separated names to appear. By default, the data will overwrite the original column, but you can choose another location to keep the original data intact.
  6. In the end, click “Finish”.

Conclusion

By using the above formulas, you can easily get the first and the last name from a text string. The formula for the first name is super easy and for the last name is a bit tricky but you need to understand it once it starts.

I hope you found this tip helpful.

Now tell me one thing. Do you have any other method to get first and last from a string? Please share your views with me in the comment section, I’d love to hear from you.

And please don’t forget to share this tip with your friends.

Last Updated: May 13, 2024

11 thoughts on “Separate Names in Excel (Split First & Last Name)”

  1. TRIM(RIGHT(SUBSTITUTE(TRIM(A1),” “,REPT(” “,255)),255))

    Changes all spaces into string of 255 blanks, then trims to the left leaving the last word intact.

    Reply
  2. Nice. If only everyone had a single word surname. Nothing like a van Beek or van der Wettering to create formulaic issues. I did this many years ago; from memory there were various IF’s and OR’s involved.

    Reply
  3. I only get an error about too few arguments on both of Adrians suggestions. Running Excel 2016 latest version.

    Reply
  4. Suppose your text is in cell A1. Then to find the last word in B1 write:
    = LEN(TRIM(A1)) – LEN(SUBSTITUTE(A1, ” “, “”,))
    This give as result the number of spaces in A1
    Then in B2 write:
    = SUBSTITUTE(A1,” “, “*”,B1)
    This replaces the last space of A1 by an asterisk
    In B3 write:
    =FIND(“*”, B2)
    That finds the position of “*”
    In B4 write:
    = RIGHT(A1, LEN(A1) – B3)
    If you want, you could indent all this formulas in one (a little more complex, but it’s possible)
    Sincerely yours,
    Carlos M.

    Reply
  5. Hi Puneet

    I prefer the slightly more general case version of:

    =IF(ISERR(FIND(” “,A1)),A1,RIGHT(A1,LEN(A1)-FIND(“*”,SUBSTITUTE(A1,” “,”*”,LEN(A1)-LEN(SUBSTITUTE(A1,” “,””))-B1+1))))

    as I am often trying to get the last one or two words of an address. I can just go down column B and change any numbers that don’t make sense eg New York requires a 2, and Rio de Janeiro would require a 3. If I only want the last word I just delete “-B1+1”.

    It also has the advantage of working on very long strings

    Thank you for our daily lessons. I have learned a lot from you

    Adrian

    Reply
  6. Cool solution Puneet!
    I have not had problems extracting the last names before, but
    I’ve never seen it done with YOUR function-combination.
    There’s always another way to do things in Excel – thanks

    Reply
    • Yup.

      Well, after writing, this I have got a super cool solution to do it without using formulas and even without using VBA.

      That’s text to column, but not the normal way we always do.

      Reply

Leave a Comment