How to Separate Names in Excel: 5 Quick and Easy Ways

Extracting different parts of a full name is a familiar task for any Excel user. If you have only five or ten names to deal with, you can type in parts of those names one by one. But when you have thousands of names in your hand, it becomes a hellish experience. This is where knowing how to separate names in Excel saves your day!

When you know your way around Excel, even a burdensome task becomes enjoyable. We understand this well. For this reason, in this guide, we are going to discuss the 5 best methods for separating names in Excel. For each step in every method, we will also attach corresponding pictures to aid with your learning.

The goal includes explaining all the options and formulas when we use them in the method. This way, you will actually learn the process and remember it forever. You should practice the steps as you read in this guide. For this, we are also sharing the practice workbook with you. This will help you follow the process exclusively without any confusion.

Let’s jump in!

How to Separate Names in Excel: 5 Painless Approaches

Excel demands a user be smart and efficient. When the criterion is to separate names, one can always start writing the names manually. But that is the most anti-Excel method. Therefore, we would like to go the other way. We will show how you can be superfast and effective by using 5 amazing techniques for separating names in Excel.

To make your learning fruitful, read the instructions carefully and practice each step along the way you progress through this guide. This way, you will be able to discover which method goes with your style most and easily make it a part of your daily Excel routine.

Now let’s start with the first method!

Method 1: Separating with Find and Replace

We are going to demonstrate how to separate names in Excel with the Find and Replace option in the very first method of this guide. The process requires copy-pasting names to an empty column and removing parts of the name that are not wanted.

The dataset shown below is going to be used in this demonstration.

Separating with Find and Replace 1

Now we will discuss a way you can use to separate FirstName and LastName from the list in Column A that has FullName in two-part naming conventions. Let’s begin then!

1. Select the names that you want to separate and copy them either from the right-click menu or by pressing the CTRL+C shortcut.

Separating with Find and Replace 2

2. Now click on the first cell of an empty column. Then go to the Paste option in the Clipboard group under the Home ribbon. From there, click the Values button in the Paste Values, as marked (2) in the picture below.

We suggest pasting the copied names as Values because, in that way, it will keep only the texts and not copy-paste any formula, in case any are accompanying the names.

Separating with Find and Replace 3

3. After pasting the names in the column, select them all again.

In our demonstration in the picture below, we are going to separate FirstNames now and LastNames later. The current selection is for removing the LastNames and thus keeping the FirstNames.

Separating with Find and Replace 4

4. Now under the Home ribbon, go to the Editing group, and click on the Find & Select button as marked (1) in the picture. Next, click the Replace option in the drop-down menu.

Alternatively, you can simply press the shortcut CTRL+H buttons for this option.

Separating with Find and Replace 5

5. Now you will see the Find and Replace box. Click on the Find what: text box.

Separating with Find and Replace 6

6. The intention of keeping only the FirstNames in the column means that all the LastNames should be removed. To achieve this outcome, we are going to find the only space character which separates the first and last name and remove everything after the space (LastName) along with the space itself.

To do it, write “ *” (a space character followed by an asterisk) in the Find what: text box. Then do not put anything in the Replace with: box, and then click Replace All.

Separating with Find and Replace 7

It means that you want to find the space plus everything after the space. The space character itself will find the space in the copied FullNames, and the asterisk is a wildcard that indicates all the characters after the space.

7. After hitting the Replace All button, you will see a confirmation dialogue box. Click the OK button.

Separating with Find and Replace 8

8. Now you will see that all the LastNames with the spaces before them have been removed from your selected range of names.

Separating with Find and Replace 9

9. It’s about keeping the LastName now. To do it, we simply need to remove the FirstName from the FullName.

First, copy FullNames to the column where you want to separate and keep the LastNames. Then select these copy-pasted names and then move to the next step.

Separating with Find and Replace 10

10. While having the names selected, find the Editing group under the Home ribbon. In that group, locate the Find & Select button and click on it. Then click the Replace option from the drop-down menu. If you want to use a shortcut, you can press the CTRL+H buttons for this option.

Separating with Find and Replace 11

11. The Find and Replace window will pop open. In the Find what: text box, write “* ” (an asterisk followed by a space character). Keep the Replace with: box empty. Finally, click the Replace All button.

The asterisk (wildcard) followed by the space character together declares that all the space characters and everything before the space character (FirstNames, in this case) will be found. Then replacing them with nothing will result in simply removing those FirstNames, leaving us with the LastNames only. Observe in the next step.

Separating with Find and Replace 12

12. After clicking the Replace All button, the confirmation dialogue box will pop up. Click OK.

Separating with Find and Replace 13

13. Now you may be taken back to the Find and Replace window. Click Close in this case.

Separating with Find and Replace 14

14. Now you will find that all the LastNames have been separated and are occupying your selected column.

Separating with Find and Replace 15

If you feel confused, it will take practicing those steps more than once to understand it completely. After that, you will be able to do it even with your eyes closed!

Method 2: Using Text to Columns Option

This approach with Text to Columns has been the most popular choice for separating names in Excel for a long time now. Although new techniques have appeared lately, it still stays as one of the best methods one can follow.

We will use the Text to Columns option to show how you can separate names in both instances of “FirstName LastName” (two-part) and “FirstName MiddleName LastName” (three-part). We will also discuss how you can separate names even if those names have various kinds of separators splitting the parts.

Unlike Method 1 where FullNames were copied to columns and then other than the needed parts of the names were removed, this method separates the names by following the separators or delimiters. Then it duplicates those separated names to the selected destination columns based on given instructions in the Text to Columns wizard.

Let’s see first how to separate names for two-part names. It is a multi-step process that will seem easier to execute as you learn more about it.

2.1: First Name, Last Name

Follow the steps carefully. We have two-part names to deal with in this section.

1. Select the names that you want to separate.

First Name, Last Name 1

2. Now go to the Data ribbon and find the Data Tools group. In there, locate the Text to Columns option and click on it.

First Name, Last Name 2

3. It will bring up the Convert Text to Columns Wizard. Make sure that Delimited is selected under the Original data type and then click the Next button.

First Name, Last Name 3

4. In this window of the Wizard, you need to select delimiters. By default, Tab is selected.

Delimiters are the characters or symbols that separate values in a cell. For instance, in this demonstration, we can see in the Data preview that spaces are separating the first and last names. Based on this finding, we will select Space in the delimiter.

First Name, Last Name 4

Here’s a tip. Simply notice what characters or symbols are separating the names in the Data preview section, and then select them from the Delimiters section. If the delimiter is something other than Tabs, Semicolons, Commas, or Spaces, put it in the Other box.

5. After selecting the delimiter, the Data preview will show what the names will look like. The vertical dividing line indicates the separated texts and respective columns those separated names will be put into. Make sure that the Treat consecutive delimiters as one is selected. Then click Next.

First Name, Last Name 5

6. Now, in this last Wizard window, the destination has to be selected. The default destination is always where you currently have your data. Keeping it as it is will remove those FullNames.

To change the destination so that your separated names are put in new columns, click the marked little button with the up arrow symbol shown in the picture below.

First Name, Last Name 6

7. You will be taken back to the sheet. Select the destination column by clicking on the first cell of that column. In this case, the names are to be separated into FirstName and LastName so two columns will occupy the separated names. We clicked on cell B2 as the destination as you can see in the picture.

After selecting the destination, click the marked (2) little button again to go back to the Wizard window.

 

First Name, Last Name 7

8. [OPTIONAL] If you want to separate names but need only one of FirstName or LastName, you can skip the unwanted part of the name here. In Data Preview, simply click the column that has the name parts that you do not want, and then click the Do not import column (skip) radio button under Column data format.

As you see in the picture below, we temporarily clicked on the LastName containing column and clicked the option to skip. It shows in the column header as Skip Column. If we finish the process here, the Text to Columns wizard will put only the FirstName column in the sheet.

First Name, Last Name 8

9. If you want to keep both parts of the name, you can click Finish here. Selecting the Column data format is optional. For only texts, keeping it General or selecting Text has the same effect.

First Name, Last Name 9

10. Sometimes, you may get a warning box that there is already data in your destination. Simply click OK if you selected an empty column or enough space as the destination in Steps 6-7.

First Name, Last Name 10

11. Now you will see that the names have been separated and put into separate columns according to your instructions!

First Name, Last Name 11

Easy, right?

2.2: First Name, Middle Name, Last Name

Now we will show how you can separate names that have three parts in them: FirstName, MiddleName, and LastName. The same Text to Columns option will be used for this one as well.

The dataset for this demonstration is shown below.

First Name, Middle Name, Last Name 1

Let’s see how to do it!

1. Select the names in the column. Then go to the Data ribbon’s Data Tools group. Next, click the Text to Columns button that can be located within that group.

First Name, Middle Name, Last Name 2

2. The Convert Text to Columns Wizard will open. Select Delimited and then click Next.

First Name, Middle Name, Last Name 3

3. Select the Delimiter(s) in this window. Make sure that the Treat consecutive delimiters as one is checked. Then click Next.

As three-part names in this demonstration are separated by spaces, we selected Space as the delimiter which shows in the Data preview that names have been separated in their space locations.

First Name, Middle Name, Last Name 4

4. Now choose the destination. Click the little button with the up arrow symbol as marked in the picture below.

First Name, Middle Name, Last Name 5

5. You will be taken back to your sheet. Now click on the first cell where you want those separated names. Then click the marked (2) button with the down arrow symbol.

First Name, Middle Name, Last Name 6

In this demonstration, we chose Columns H, I, and J to have the FirstName, MiddleName, and LastName respectively. Hence, we chose cell H2 so the empty cells horizontally and vertically from that cell will occupy the separated names.

6. Now select the Column data format and then click the Finish button.

First Name, Middle Name, Last Name 7

7. Click OK in the warning dialogue box.

First Name, Middle Name, Last Name 8

8. Now you will see that the names have been separated and are put in separate columns based on your selection of destination.

First Name, Middle Name, Last Name 9

That’s it!

2.3: Mixed Delimiter in Text to Columns

This is a special case that most Excel users encounter. When there is more than one type of delimiter and the requirement is to separate the names, it may not seem as easy. Look at the picture below. Specifically, notice the highlighted areas in the FullNames.

Mixed Delimiter in Text to Columns 1

You can see that there are not only spaces that are separating the names. And a name can have more than one delimiter, which is also shown above. So, how to tackle all the delimiters and separate those names with ease?

With the Text to Columns option, it is really effortless. Let’s check it out!

1. As always, select the names in the column that you want to separate. Then go to the Data ribbon and find the Data Tools group. Next, click the Text to Columns option in that Data Tools group.

Mixed Delimiter in Text to Columns 2

2. In the Convert Text to Columns Wizard, make sure that Delimited is selected under the Original data type and then click the Next button.

Mixed Delimiter in Text to Columns 3

3. Now, in this Step 2 of 3 wizard window, notice and find all the delimiters in the Data preview section and select them all in the Delimiters section. Until all the delimiters are selected or put manually in the Other box, you can see in the preview that names have not been separated. That is your clue to look and find the delimiters and select/add them one by one in the Delimiters section.

Mixed Delimiter in Text to Columns 4

4. In this step, be sure to spot any disorganization among separated names in the Data preview. And after selecting all the delimiters, put a check in the Treat consecutive delimiters as one box and then click Next.

Mixed Delimiter in Text to Columns 5

In this demonstration, we found that the names were separated by not only spaces but also semicolons and commas. We selected them all in the Delimiters section and scrolled through the Data preview section to make sure that all the names were separated. Then we clicked the Next button.

5. Now select the destination for the separated name columns. Click on the little up arrow symbol marked in the picture below.

Mixed Delimiter in Text to Columns 6

6. Now select the top-left cell of your intended range of destinations. Then click the little down arrow symbol marked (2) in this picture.

Mixed Delimiter in Text to Columns 7

7. The wizard window will expand again. Now select the Column data format and then click Finish. You can keep the data format General or select Text for the names.

Mixed Delimiter in Text to Columns 8

8. After clicking the Finish button in the previous step, you will see a warning dialogue box. Click the OK button.

Mixed Delimiter in Text to Columns 9

9. Now you will see that all the names have been separated perfectly despite having multiple types of delimiters.

Mixed Delimiter in Text to Columns 10

How cool is that? Also, if you have loved this method and use Office 365, DO NOT miss Method 5! Something even cooler is waiting for you there.

Method 3: Separating Names Using Formulas

This method is slightly complicated in terms of separating names in Excel. However, this is an extremely important method when you have hundreds or thousands of names that you need to separate. It involves using several functions and formulas with nesting them one inside another.

Nevertheless, we are here to make everything easy for you. Separating two names and separating three names requires a different set of formulas. But we will break down everything before, during, and after we use a formula in our demonstration. So, sit back, relax, and keep patience.

Let’s start with the discussion of separating names with formulas in the case of two-part names.

3.1: First Name, Last Name

We are going to use the RIGHT and LEFT functions to separate names in this “FirstName LastName” method.

1. Begin by clicking on a cell where you want to separate the first name into.

Separating Names Using Formulas 1

2. [FORMULA EXPLANATION] Now start writing the formula. As our text is in cell A2, we selected that cell as a cell reference for the text.

The formula for the LEFT function is =LEFT(text, number_of_characters). Here,

  • LEFT : The function.
  • text : Refers to the string of text from which a specific number of characters from the left will be extracted.
  • number_of_characters : refers to how many characters from that left side of the string will be extracted.

Separating Names Using Formulas 2

3. [FORMULA EXPLANATION] Now, not all the first names have the same number of characters in them. In the dataset that we are using, some first names have 6 characters while some have 7 and others may have 8 characters. So, the number of characters for a FirstName is not fixed in the column. It will vary from cell to cell. Therefore, we have to do something that will calculate the number of characters the FirstName has in the cell and put them in the number_of_characters (or num_chars) argument for the LEFT formula.

To calculate number_of_characters in respective FirstNames, the FIND function will be used to find out the value of the FirstName. It will search within the selected text for a space and define that space as the starting point for extracting all the characters left to it (FirstName, because both name parts are separated by a space only).

The formula for the FIND function here is =FIND(find_text,within_text,start_number). Here,

  • FIND : The function.
  • find_text : The character that is needed to be found within the text (in this case, the space).
  • start_number : It defines at which of the found characters the FIND function will start counting the value. In this case, the space will be found for one instance so the start_number will be 1, as you will find out soon.

Separating Names Using Formulas 3

4. After writing the FIND function, put a space character within quotations to indicate the text that is to be searched for. The whole formula at this point should look like the one in the picture below.

Reminder: A2 is the cell that contains the FullName.

Separating Names Using Formulas 4

5. At this point of writing the FIND formula, the cell that contains the FullName should be selected again as the location where the space will be searched for. As the starting number, put 1. Then close the bracket and then put a “-1” to exclude calculating the space itself. The final formula for separating the FirstName will be like the one in the picture below.

Separating Names Using Formulas 5

To recap, the whole FIND formula calculates how many characters the selected text has (cell A2 here) before the space character, and then subtracts the space character itself because it is only a separator or delimiter and not a part of the FirstName.

6. Now you will see that the FirstName from your referenced cell has been extracted.

Separating Names Using Formulas 6

7. Now fill the column with the formula by clicking and dragging the Fill Handle found in the right bottom corner of the cell that contains the formula. And you will see that the whole column is now showing the extracted FirstNames from the column that has all the FullNames.

Separating Names Using Formulas 7

8. [FORMULA EXPLANATION] Now it’s time to extract the LastNames from those full names. For this, we are going to use the RIGHT function. It is exactly similar to the LEFT function and has the same arguments. You can see this in the picture below.

Separating Names Using Formulas 8

The RIGHT formula is =RIGHT(text,number_of_characters). Here,

  • RIGHT : The function.
  • text : Refers to the string of text from where the mentioned number of characters will be extracted.
  • number_of_characters (or num_chars) : Refers to the value of the characters that will be extracted from the right side of the string.

9. [FORMULA EXPLANATION] After selecting the cell where the text is (in this case, cell A2), now is the time to find out the number of characters the last name has which will extract the LastName.

We will use the LEN function for this one. It will find the total number of characters in the text. Then we will subtract the number of characters the first name has from the total characters. This will tell us the number of characters the last name on the RIGHT has.

Separating Names Using Formulas 9

In the picture above, LEN(A2) is the total length of the text in cell A2. Subtracting the number of characters the FirstName has that is found through the use of FIND(“ ”,A2) will calculate the rest of the character on the right, which happens to be the LastName.

The complete formula should look like this: =RIGHT(A2,LEN(A2)-FIND(“ ”,A2)). Here,

  • A2 is the cell that has the FullName.
  • LEN(A2) is the length of the whole text.
  • FIND(“ ”,A2) is the length of the FirstName. Subtracting the length of the FirstName from the full length of the text gives us the LastName.

After writing the complete formula, hit Enter.

10. Now you will see that the formula has extracted the LastName from the FullName in your referenced cell.

Separating Names Using Formulas 10

11. Now fill the column with the formula by dragging the Fill Handle and see that all the LastNames have been extracted.

Separating Names Using Formulas 11

It may seem too complicated and intimidating. But if you are one of those Excel users who love working with functions and formulas, this method is your thing!

3.2: First Name, Middle Name, Last Name

This time, we will demonstrate how you can separate names using formulas when there are three parts in the name: FirstName, MiddleName, and LastName.

The dataset that we are going to use here is the one in the picture below.

Separating Names Using Formulas 12

Now we will show you how to separate the first name first, the last name second, and finally extract the middle from the three-part names.

1. To find the first name, we are going to use the same formula as shown in Steps 2-6 of Method 3.1. If you need an explanation, please go to the mentioned steps in Method 3.1 which has been discussed just before.

Although there are two spaces in the names here because it is a three-part name now, the first instance of a space character is mentioned within the FIND formula and thus, this formula still works without changing anything.

Separating Names Using Formulas 13

2. To find the right name, this time, we are still going to use the RIGHT function. However, as this name has three parts with two spaces separating the parts, we cannot simply subtract the length of the FirstName from the total length of the text to find the length of the LastName as done in Method 3.1. We have to include the middle name in subtraction as well.

Start writing the formula “=RIGHT(G2,” (here, G2 is the cell reference), and then move to the next step.

Separating Names Using Formulas 14

3. [FORMULA EXPLANATION] This is slightly complicated, and we are going to explain it by breaking down the formula used in this step.

Whole formula for this step: =RIGHT(G2,LEN(G2)-FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))) .

Let’s do a recap. We are using the RIGHT formula to find out the LastName from the FullName here. The technique is to find it through the calculation of text length. If we can find out the total length of the text (using LEN) and then subtract the length of both FirstName+MiddleName from the original text (using FIND) then we get the length of the LastName.

However, there are two spaces in the text; one space is after the FirstName and the other is after the MiddleName (before the LastName). To make the calculation easier by making the delimiters look unique from each other, we are going to tell Excel to find the second space that is before the LastName and replace that space character with a symbol (using SUBSTITUTE). Then we will instruct Excel to calculate all the characters to the left of the symbol.

Separating Names Using Formulas 15

In our demonstration, we used the pipe (“|”) symbol to replace the second space in the name. It can be found in the Backslash key on your keyboard. You can use @ (at) or hash (#) or any other symbol as well that is not in the names to replace the second space.

To reiterate, the intention of the calculation is LastName=FullName-(FirstName+MiddleName) .

And the formula looks like this: =RIGHT(G2,LEN(G2)-FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))) . Here,

  • RIGHT : The function.
  • G2 : The referenced cell that has FullName.
  • LEN(G2)-FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))) calculates the number of characters the LastName has in referenced cell G2.

LEN(G2) finds out the entire length of the FullName in cell G2,

FIND(“|”,SUBSTITUTE(G2,” “,”|”,2)) calculates the length of the FirstName+MiddleName and subtracts them from the total number of characters found using the LEN(G2) formula.

4. After entering the formula in the cell, fill the column with the formula by using the Fill Handle found in the right-bottom corner of the cell. You will see that the column is now showing all the LastNames from the referenced column having FullNames.

Separating Names Using Formulas 16

5. [FORMULA EXPLANATION] Now onto separating the MiddleName. For this, the MID function is going to be used. Let’s break it down.

The formula for this function is =MID(text,start_number,number_of_characters). Here,

  • MID : The function.
  • text : The string of text in question.
  • start_number : Value that defines where the middle part begins in the given string of text.
  • number_of_characters : Defines how many characters will be shown from the start_number as the MID result.

For instance, if I write a formula =MID(“HOLLYWOOD”,3,5), where the text is HOLLYWOOD, start_number is 3, and number_of_characters is 5, Excel will count the MID from the 3rd character in the text and take a total 5 characters from that count. It will give me “LLYWO” as a result.

Separating Names Using Formulas 17

The example is shown in the picture above as well.

6. [FORMULA EXPLANATION] We are going to use the MID function just like explained. But first, let’s lay down the whole formula of this demonstration.

The formula is=MID(G2,FIND(” “,G2)+1,FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))-FIND(” “,G2)-1).

After referencing the cell G2 that contains FullNames, we will find the first character of the middle name by locating the first space character in the name. It is between the FirstName and MiddleName. Therefore, adding +1 to the count from the space makes it the first character of the MiddleName, making the “FIND(” “,G2)+1” argument find out this start_number value for the MID function.

And then the “FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))-FIND(” “,G2)-1” argument in this formula calculates the total number_of_characters the MiddleName contains from the declared start_number. To do so, the “SUBSTITUTE(G2,” “,”|”,2)” converts the second space character into a pipe (“|”) symbol. Then, the complete “FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))” part of the argument calculates the number of characters the text has from that converted symbol to the front.

In this case, the result would be the length of FirstName+MiddleName. Therefore, if we subtract the length of the FirstName from this length of FirstName+MiddleName, we get the number of characters only the MiddleName has. So, we subtract the length of the FirstName with “FIND(” “,G2)-1” from the length of FirstName+MiddleName.

Separating Names Using Formulas 18

And it gives you the entire formula,=MID(G2,FIND(” “,G2)+1,FIND(“|”,SUBSTITUTE(G2,” “,”|”,2))-FIND(” “,G2)-1) .

You can simply copy-paste the formula but in cell references (G2 in the given formula here), replace the cell number with the one where you have your text. And finally, press the Enter button.

7. Afterward, you will see that the MiddleName from your FullName has been extracted in the cell.

Separating Names Using Formulas 19

8. Click and drag the Fill Handle to fill up the column with the formula to separate all the MiddleNames from the respective FullNames.

Separating Names Using Formulas 20

And just like that, you now have separated even three-part names using a few Excel functions and formulas!

Method 4: Utilizing the Flash Fill Option

If you are using Excel 2013 or a later version, you are in for a treat! The Flash Fill option in Excel makes your work easier. This option is also known by its shortcut, CTRL+E. Be sure to check our guide on the various uses of CTRL E in Excel and improve your expertise.

The Flash Fill option works by recognizing patterns and then filling up the cells following that pattern. You will see in our demonstration how effective it is. We are going to discuss how you can separate both two-part and three-part names in Excel by using this majestic option. And if this option does not seem to work, it is probably disabled in your Excel. Check the step-by-step process for enabling Flash Fill in the last part of this method.

Now let us go through the process of separating names with the Flash Fill in Excel!

4.1: First Name, Last Name

First, we are going to show how to separate two-part names in Excel by using the Flash Fill option.

1. To begin with, you need two empty columns adjacent to the right. The separated FirstNames and LastNames will occupy those columns after the process. If they are not empty, simply select those adjacent columns to the right of the column with FullNames, and then click the Insert button in the Cells group under the Home ribbon. It will insert two new empty columns for extracting the names.

Then click on the first cell of the first column. For the Flash Fill option to work, the column has to be touching the source column. In this demonstration, Column A has the FullNames, and we are going to separate and extract FirstNames and LastNames in Columns B and C respectively.

Utilizing the Flash Fill Option 1

2. After selecting the cell, simply write down the first name from the column on the left. Then press Enter to move to the next cell down.

Utilizing the Flash Fill Option 2

3. Now write the following FirstName from the cell on the left. As soon as you write a few letters from the name, you will see the rest of the names suggested by Flash Fill. The cells down will also show a grayed-out suggestion of all the FirstNames from the column on the left. Press the Enter button or the Tab button on your keyboard (whichever works for you).

Utilizing the Flash Fill Option 3

You will see that all the FirstNames have been extracted from the FullNames in the column you are in!

4. Now go to the second empty column to separate the LastNames from the FullNames. Follow the same procedure and you will see that the Flash Fill has picked up your pattern and is suggesting all the LastNames extracted from the source column. Simply press Enter or Tab on your keyboard to fill the column with all those LastNames.

Utilizing the Flash Fill Option 4

5. Now you have both FirstNames and LastNames extracted from the FullNames!

Utilizing the Flash Fill Option 5

6. At this point, if you don’t see the suggestions to fill out your columns initiated by the Flash Fill, there is another way to call up this system. Simply write down one or two separated names in cells. Then press Enter to move to the next cell (see the picture below). Keep the cell selected and don’t do anything else before following the next steps.

Utilizing the Flash Fill Option 6

7. Now go to the Editing group under the Home ribbon. Click the marked (1) Fill button and then from the drop-down list, click the Flash Fill option. Alternatively, you can press the CTRL+E shortcut buttons.

Utilizing the Flash Fill Option 7

8. As soon as you do it, you will see that Flash Fill has filled up your column with the separated names.

Utilizing the Flash Fill Option 8

How fast was it? We say it was pretty rapid!

4.2: First Name, Middle Name, Last Name

Now, when you have the three-part names to separate into FirstName, MiddleName, and LastName, the process is still the same as shown above. Make sure to do one column after another so that the pattern picked up by the Flash Fill is used effectively. Do the FirstNames first, MiddleNames second, and LastNames last.

1. Begin by selecting the empty cell adjacent to the first FullName on your dataset.

Utilizing the Flash Fill Option 9

2. Now write down a few names till Flash Fill shows you suggestions to fill up the column with the extracted FirstNames. Then press the Enter or Tab button on your keyboard.

Utilizing the Flash Fill Option 10

If no suggestions are shown at this point, press Enter after you are finished writing the FirstName for the second cell. It will move you to the next cell down. Then press the CTRL+E buttons on your keyboard for the shortcuts of Flash Fill.

3. Now you will see that the column has been filled with the separated FirstNames.

Utilizing the Flash Fill Option 11

4. Repeat the same process for the MiddleNames.

Utilizing the Flash Fill Option 12

5. And the same process for the LastNames. This time, you can even use CTRL+E after completing the first cell. Because the Flash Fill has picked the pattern already and is going to work even after simply filling the first cell!

Utilizing the Flash Fill Option 14

6. Now you will see that all the names have been separated into the respective columns.

Utilizing the Flash Fill Option 15

This is a fast method that always delivers. If you get inaccurate results, try to fill up one or two more cells before initiating the Flash Fill. In this case, it will recognize patterns with more accuracy.

Enabling Flash Fill in Excel

If Flash Fill does not work in your Excel, there may be two reasons behind it.

  • Either your Excel is older than Excel 2013. You must update your Excel to Excel 2013 or any succeeding version.
  • Or, the Flash Fill option is not enabled in your Excel Options. In this case, follow the procedure to enable the Flash Fill from below.

Enabling the Flash Fill is a two-step process.

1. In your Excel, go to the File tab and then click Options.

Enabling Flash Fill 1

2. When the Excel Options window pops open, click the Advanced tab first. Then make sure that the Enable AutoComplete for cell values option is checked and within it, the Automatically Flash Fill option is also checked (shown in the picture below with the options highlighted). Finally, click OK to complete the process.

Enabling Flash Fill 2

Now the Flash Fill in your Excel will start working!

Method 5: Using the TEXTSPLIT Function

Separating names with the TEXTSPLIT function in Excel is the latest method. This is a dynamic function that works best among all the methods you can find. And this TEXTSPLIT function is available only in Office 365. It works much like the Text to Columns option discussed in Method 2.

Most importantly, the TEXTSPLIT function is dynamic! It splits texts by following the delimiters mentioned in the argument within the formula. It also lets you split texts into not only columns but rows as well. All in all, this has been a tremendous addition to Excel.

The dynamic nature of this function means that even if the names are changed in the source, the formula will work the same. It was impossible in Text to Columns. Therefore, the TEXTSPLIT function has lessened the workload incredibly.

We will break down and discuss the formula for the TEXTSPLIT function as we move through the process. The mandatory arguments in the TEXTSPLIT formula are text and column_delimiter. There are other optional arguments in the formula as well. However, since we will not be using any of those optional arguments in this method, we will only include and discuss the two mandatory arguments within the formula.

The dataset that we are going to use is the one below.

Using the TEXTSPLIT Function 1

Let us now jump into the new world of TEXTSPLIT, shall we?

5.1: First Name, Last Name

In this method, we will demonstrate how to separate names in Excel with the TEXTSPLIT function. Here, we are dealing with two-part names only. So, the goal is to separate FirstNames and LastNames from the FullNames.

1. Click on an empty cell where you want to separate and extract the FirstName from the source text.

Using the TEXTSPLIT Function 2

2. [FORMULA EXPLANATION] Now start writing the formula for the TEXTSPLIT function. As we are going to use only the text and column delimiter, the formula structure would be: =TEXTSPLIT(text,column_delimiter) . Here,

  • TEXTSPLIT : The function.
  • text : The string of text (FullNames, in this case) that will be separated based on declared delimiters.
  • column_delimiter : The characters based on which the text will be separated.

Now let’s look at the picture below. After writing the =TEXTSPLIT , we referenced the cell where the text is (in this case, cell A2).

Using the TEXTSPLIT Function 3

3. Now put the column delimiter in the formula within double quotations. In this demonstration, space characters are separating the FirstName and LastName. Therefore, a space character is a delimiter for all the names. And that’s what has been used as shown and highlighted in the picture below.

After writing the formula, press the Enter button.

Using the TEXTSPLIT Function 4

4. Now you will see that the names have been separated based on the declared delimiter within the formula.

Using the TEXTSPLIT Function 5

5. Now click on the Fill Handle in the right-bottom corner of the selected cell and drag it downwards to fill the column with the formula. Basically, in just two steps, you now have all the names separated into FirstNames and LastNames.

Using the TEXTSPLIT Function 6

Simply awesome, isn’t it?

5.2: First Name, Middle Name, Last name

Now we will show how to separate names when you have three-part names to deal with. That is, you have to separate the FullName into FirstName, MiddleName, and LastName.

1. Click on an empty cell to select the cell where you will be writing the formula.

Using the TEXTSPLIT Function 7

2. Now write the formula as shown in the picture below. Here, TEXTSPLIT is the function, G2 is the referenced cell that has the text to be split or separated, and the highlighted space character “ ” is the delimiter.

Finally, hit Enter to complete the process.

Using the TEXTSPLIT Function 8

3. Afterward, you will find that the names have been separated into the desired three parts and are put inside the respective columns!

Using the TEXTSPLIT Function 9

4. Now use the Fill Handle to fill up the columns with the formula and see the TEXTSPLIT magic unfolds right before your eyes!

Using the TEXTSPLIT Function 10

Can you think of an easier and faster way to separate names in Excel?

5.3: Mixed Delimiter in TEXTSPLIT Function

In Method 2.3 (Mixed Delimiter in Text to Columns), we discussed how to separate names when there is more than one type of delimiter in the text. We had to select the delimiters in the Convert Text To Columns Wizard in that case. But what to do when it’s about the TEXTSPLIT function?

Look at the picture of the dataset below. There are not only spaces separating the names. There are semicolons and commas as well.

Using the TEXTSPLIT Function 11

To tackle this situation, we are going to use the ARRAY CONSTANT to include all the delimiters by putting them all within curly brackets and separating them by commas.

Let’s go through the practical demonstration below.

1. First, select the cell for writing the formula.

Using the TEXTSPLIT Function 12

2. [FORMULA EXPLANATION] Now write the entire TEXTSPLIT formula as discussed in Methods 5.1 and 5.2. In the picture below, notice how all the column delimiters are put within individual quotation marks, and all of them are kept within curly brackets. The curly brackets indicate the ARRAY CONSTANT in Excel where more than one value is included for a single argument.

Using the TEXTSPLIT Function 13

The entire formula in this step is: =TEXTSPLIT(G19,{“ ”,“,”,“;”}) . Here,

  • TEXTSPLIT : The function.
  • G19 : The referenced cell that has the text.
  • {“ ”,“,”,“;”} : ARRAY CONSTANT that includes all the delimiters inside of it.

After finishing the formula, press the Enter button.

3. You will see that the FullName has been separated in the locations of the delimiters and then is put inside respective columns for their FirstName, MiddleName, and LastName.

Using the TEXTSPLIT Function 14

4. Now fill the columns with the formula by using the Fill Handle on the cell where you have written the formula.

Using the TEXTSPLIT Function 15

Despite having various delimiters, that didn’t take long! Now imagine the minutes and hours you are going to save when there are usually thousands of names to separate.

Pretty amazing!

Concluding Words

Separating names in Excel is always a hefty task. Finding the right way to do more in less time is the way to go. We always maintain this policy whenever we create guides for you. This was no exception either. That’s why we discussed 5 different methods for various requirements. Our guide covers all the bases in terms of separating names in Excel.

Being a complete Excel user means knowing everything about any possible requirement of an Excel task. For this, we strongly recommend that you learn all five methods discussed here. However, if you want something quick, Method 4 is what you are looking for.

But if you are a perfectionist and want to use a technique for separating thousands of names, Method 3 and Method 5 (if you have Office 365) can be your picks. And if you want to avoid using formulas, you can rely on Method 2 which utilizes the old boy Text to Columns option. It is still as functional and relevant.

Be sure to practice your chosen method a good couple of times. You can use the practice sheet that we have shared with this article for this purpose. And with enough practice, both your knowledge and confidence will grow further. Now go seize the power of Excel and be unstoppable everywhere!

You may also read:

Leave a Comment