How to Split Text in Cells Using Formulas

Bottom Line: Learn how to use formulas and functions in Excel to split full names into columns of first and last names.

Skill Level: Intermediate

Watch the Tutorial

Watch on YouTube & Subscribe to our Channel

Download the Excel Files

You can download both the before and after files below. The before file is so you can follow along, and the after file includes all of the formulas already written.

Excel File Icon 2021 xlsx Split-Names-with-Formulas-BEGIN.xlsx

Excel File Icon 2021 xlsx Split-Names-with-Formulas-FINAL.xlsx

Splitting Text Into Separate Columns

We've been talking about various ways to take the text that is in one column and divide it into two. Specifically, we've been looking at the common example of taking a Full Name column and splitting it into First Name and Last Name.

Split text in cells into separate columns

The first solution we looked at used Power Query and you can view that tutorial here: How to Split Cells and Text in Excel with Power Query. Then we explored how to use the Text to Columns feature that's built into Excel: Split Cells with Text to Columns in Excel. Today, I want to show you how to accomplish the same thing with formulas.

Using 4 Functions to Build our Formulas

To split our Full Name column into First and Last using formulas, we need to use four different functions. We'll be using SEARCH and LEFT to pull out the first name. Then we'll use LEN and RIGHT to pull out the last name.

The SEARCH Function

They key to breaking up the first names from the last names is for Excel to identify what all of the full names have in common. That common factor is the space character that separates the two names. To help our formula identify everything to the left of that space character as the first name, we need to use the SEARCH function.

The SEARCH function returns the number of the character at which a specific character or text string is found, reading left to right. In other words, what number is the space character in the line of characters that make up a full name? In my name, Jon Acampora, the space character is the 4th character (after J, o, and n), so the SEARCH function returns the number 4.

There are three arguments for SEARCH.

  • The first argument for the SEARCH function is find_text. The text we want to find in our entries is the space character. So, for find_text, we enter ” “, being sure to include the quotation marks.
  • The second argument is within_text. This is the text we are searching in for the space character. That would be the cell that has the full name. In our example, the first cell that has a full name is A2. Since we are working with Excel Tables, the formula will copy down and change to B2, C2, etc., for each respective row.
  • The third and last argument is [start_num]. This argument is for cases where you want to ignore a certain number of characters in the text before beginning your search. In our case, we want to search the entire text, beginning with the very first character, so we do not need to define this argument.

All together, our formula reads: =SEARCH(” “,A2)

SEARCH function to identify space character

I started with the SEARCH function because it will be used as one of the arguments for the next function we're going to look at. That is the LEFT function,

The LEFT Function

The LEFT function returns the specified number of characters from the start of a text string. To specify that number, we will use the value we just identified with the SEARCH function. The LEFT function will pull out the letters from the left of the Full Name column.

The LEFT function has two arguments.

  • The first argument is text. That is just the cell that the function is pulling from—in our case A2.
  • The second argument is [num_chars]. This is the number of characters that the function should pull. For this argument, we will use the formula we created above and subtract 1 from it, because we don't want to actually include the space character in our results. So for our example, this argument would be SEARCH(” “,A2)-1

All together, our formula reads =LEFT(A2,SEARCH(” “,A2)-1)

LEFT function pulls the leftmost characters

Now that we've extracted the first name using the LEFT function, you can guess how we're going to use the RIGHT function. It will pull out the last name. But before we go there, let me explain one of the components that we will need for that formula. That is the LEN function.

The LEN Function

LEN stands for LENGTH. This function returns the number of characters in a text string. In my name, there are 12 characters: 3 for Jon, 8 for Acampora, and 1 for the space in between.

There is only one argument for LEN, and that is to identify which text to count characters from. For our example, we again are using A2 for the Full Name. Our formula is simply =LEN(A2)

LEN function counts the number of characters

The RIGHT Function

The RIGHT formula returns the specified number of characters from the end of a text string. RIGHT has two arguments.

  • The first argument is text. This is the text that it is looking through in order to return the right characters. Just as with the LEFT function above, we are looking at cell A2.
  • The second argument is [num_chars]. For this argument we want to subtract the number of characters that we identified using the SEARCH function from the total number of characters that we identified with the LEN function. That will give us the number of characters in the last name.

Our formula, all together, is =RIGHT(A2,LEN(A2)-SEARCH(” “,A2))

RIGHT function pulls the rightmost characters

Note that we did not subtract 1 like we did before, because we want the space character included in the number that is being deducted from the total length.

Pros and Cons for Using Formulas to Split Cells

The one outstanding advantage to this method for splitting text is the automatic updates. When edits, additions, or deletions are made to the Full Name column, the First and Last names change as well. This is a big benefit compared to using Text to Columns, which requires you to completely repeat the process when changes are made. And even the Power Query method, though much simpler to update than Text to Columns, still requires a refresh to be manually selected.

Changes automatically update using formula method

Of course, one obvious disadvantage to this technique is that even though each of the four functions are relatively simple to understand, it takes some thought and time to combine them all and create formulas that work correctly. In other words, is not the easiest solution to implement of the three methods presented thus far.

Another disadvantage to consider is the fact that this only works for scenarios that have two names in the Full Name column. If you have data that includes middle names or two first names, the formula method isn't helpful without making some considerable modifications to the formulas. (Homework challenge! If you'd like to give that a try, please do so and let us know your results in the comments.) As we saw in the Power Query tutorial, you do in fact have the capability to pull out more than two names with that technique.

Conclusion

I hope you've learned something new from this post and that is helpful as you split data into separate columns. If you have questions or remarks, please leave a comment below!

    • Great question, Hans! Those types of names can be tricky. In the post on splitting names with Power Query, I explain how to split cells with more than two names.

      https://www.excelcampus.com/powerquery/split-column-power-query/

      The challenge is if the column contains a combination of middle names and two last names and/or two first names, etc.

      If you ONLY have cases where there are two last names, then you can use the Power Query technique.

      In a future post, we will look at how to split names with Flash Fill, which can also help with those more complex scenarios.

      I hope that helps. Thanks again and have a nice day! 🙂

  • I use the 1st left function mentioned to get the 1st name then I use the substitute function to get the others. For 3 names I concat the substitutes.

  • These splits can be achieved, in Excel 365, assuming names to be split are positioned in cells A4:A6, via these two formulas:
    =LEFT(A4:A6,FIND(” “,A4:A6))
    =RIGHT(A4:A6,LEN(A4:A6)-FIND(” “,A4:A6))

    You can’t write dynamic array formulas in an Excel table, so you can name the range of cells containing the names via OFFSET. In that way, names added to the range will automatically receive the split formulas.

    There’s also Google Sheets’ Split function.

  • I have sent you on the last issue some nice hard nuts to crack:

    Middle Initials, sometimes yes sometimes no. > you can try to search for the “.” with ifs, mid, len left and right
    German and Swiss double Family Names like
    Anneliese Leuschner-Schayan, but in Switzerland sometimes without the “-”
    Heinrich Graf von Wolkenburg > ?? > Only Heinrich is First Name
    Dr. Jörg Mittelsten Scheidt (Dr. and Mittelsten are titles, but Mittelsten must be at the family name)

    Why not make a competition out of this issue 🙂

    • Thanks Heinz! That is a tough one to crack. In next week’s post we look at Flash Fill with more complex scenarios like this, and I also open it up for suggestions. I like the idea of a competition though. We’ll do that in the future. Thanks again!

  • At Cell A1:D1, enter the text: “Full Name”; “First”; “Middle”; “Last”
    Data start from the second row.

    Let the “Full Name” at Cell A2 is:
    Jenifer Anne Drayn

    Formula at cell B2 (for First Name) is:
    =LEFT(A2,FIND(” “,A2)-1)

    Result: Jenifer

    Formula at cell D2 (for Last Name) is:
    =RIGHT(A2,LEN(A2)-FIND(“”*””,SUBSTITUTE(A2,”” “”,””*””,LEN(A2)-
    LEN(SUBSTITUTE(A2,”” “”,””””)))))”

    Result: Drayn

    Formula at cell C2 (for Middle Name) is:
    =IF(LEN(B2&D2)+2>=LEN(A2),””,MID(A2,LEN(B2)+2,LEN(A2)-LEN(B2&D2)-2))

    Result: Anne

  • Hi,
    I believe Flash fill can take of splitting text really handsomely. Very efficient – saves you the effort of figuring out the formula.

  • HI JON,
    How can we set it up for surnames with gaps eg
    Bob Du Plessis or Koos van der Merwe
    Can we write a formula looking ONLY for the first space?

  • thank you for this great video. it opens the mind to think in a different way. and help in actual job

  • Search
    Generic filters
    Exact matches only

    JOIN US & LEARN EXCEL

    Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
    Plus weekly updates to help you learn Excel.

    Download the eBook

    Excel Shortcuts List

    keyboard shortcuts list banner

    Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

    Excel Shortcuts List

    Join Our Weekly Newsletter

    The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

    Join Our Free Newsletter

    About Me

    Jon Acampora Profile

    Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

    This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

    MVP_Horizontal_BlueOnly

    Free Excel Training Webinar Modern Power Tools

    >