How to Split First And Last Name in Excel (6 Easy Ways)

In Excel, you can split your full name into separate columns. There are a couple of ways to split first name and last name. You can use Excel inbuilt features as well as different formulas to split a full name into first and last names. In this article, I’m going to explain how to split first and last name in Excel.

To make the demonstration more understandable, I’m going to use a sample dataset of a particular bank. The dataset contains two columns that are Full Name and Account Number.

Sample Dataset to Split First And Last Name in Excel


Download to Practice


6 Ways to Split First And Last Name in Excel

1. Using Text to Columns to Split First And Last Name

You can use the Text to Columns feature to Split First and Last Name.

To begin with, select the cell or cell range from where you want to Split First and Last Name.

➤ I selected the cell range B4:B13.

Now, open the Data tab >> from Data Tools >>  select Text to Columns

Using Text to Columns to Split First And Last Name in Excel

A dialog box will pop up. From there Choose the file type that best describes your data.

⏩ I selected Delimited as my data has space character.

Then, click Next.

Another dialog box will pop up. From there select the Delimiters that your data has.

⏩ I selected Space as my data has space characters.

Then, click Next.

Using Text to Columns to Split First And Last Name in Excel

Again, another dialog box will pop up. From there select the Destination to place your split data.

⏩ I selected the D4 cell to place the separated first and last names.

Using Text to Columns to Split First And Last Name in Excel

Finally, click Finish.

⏩ A warning message will appear.

Click, OK if you are 100% sure to place the separated texts.

Using Text to Columns to Split First And Last Name in Excel

⏩ Hence, you will get the first and last name from the full name.


2. Using Flash Fill to Split First And Last Name

You also can use the Flash Fill feature to Split First and Last Name.

While using the Flash Fill command you will need to create a pattern. If Excel detects the pattern then it Fills the data automatically.

As I want to split first and last name I added two new columns one for First Name another for Last Name.

Now, type the name part of the Full Name that you want to extract in the first cell.

I typed the name Adam in the First Name column.

Using Flash Fill to Split First And Last Name

➤ Then, in the second cell type the first name of the B5 cell. In most cases, Excel senses a pattern, if the pattern is detected then it will populate the first names in all other cells automatically.

Hence, you can see that Excel detected the pattern and showed all the First Names.

⏩ Now, you will have to press ENTER to Fill all the First Names automatically.

Using Flash Fill to Split First And Last Name in Excel

Follow the procedure explained for the First Name to split the Last Name from the Full Name.

Then, you will see that Excel detected the pattern and showed all the Last Names.

⏩ Now, press ENTER to Fill all the Last Names automatically.

Using Flash Fill to Split First And Last Name in Excel

In case your Flash Fill feature is not enabled by default. Then, to use the Flash Fill feature click on the Data tab >> from the Data Tools group >> select Flash Fill.

If it still doesn’t work, then go to File >> open Options >> go to Advanced >> select Flash Fill box (Also make sure the Automatically is selected under Editing options)

Related Content: How to split text into multiple cells in Excel


3. Using Flash Fill to Split First And Last Name When Middle Name Exists

In case your Full Name contains the First, Last, and Middle names you will be able to use the Flash Fill command to Split First and Last Name ignoring the Middle name.

To demonstrate the procedure, I’ve taken a dataset given below that contains First, Last, and Middle names.

Using Flash Fill to Split First And Last Name When There Are Middle Name

As I want to split first and last name I added two new columns one for First Name another for Last Name.

Now, type the name part of the Full Name that you want to extract in the first cell.

➤ I typed the name Adam in the First Name column.

Instead of using the default Flash Fill option, I will use the Flash Fill feature from the ribbon.

To start with, first, select the cell where you stirred the pattern to follow.

➤ I selected cell D4.

Now, open the Data tab >> from the Data Tools group >> select Flash Fill

⏩ Therefore, you will get the First Name from the Full Name.

Using Flash Fill to Split First And Last Name When There Are Middle Name

Again, I selected cell E4 to split Last Name from Full Name while ignoring Middle Name.

Now, open the Data tab >> from the Data Tools group >> select Flash Fill

⏩ As a result, you will get the Last Name from the Full Name.

Using Flash Fill to Split First And Last Name When There Are Middle Name

Read More: Splitting Text in Excel Using Flash Fill


4. Using Functions to Split First And Last Name

To Split First and Last Name, you can use Excel functions. You can use the LEFT function, RIGHT function with the FIND function to separate First Name and Last Name from Full Name when the names are separated with space characters.


4.1. Using LEFT & FIND Function to Split First Name

By using the LEFT function and the FIND function, you can split the First Name from the Full Name.

To start with, select any cell to place the First Name.

➤ I selected the D4 cell.

⏩ In cell D4, type the following formula.

=LEFT(B4,FIND(" ",B4,1)-1)

Using LEFT & FIND Function to Split First Name in Excel

Here, in the LEFT function, I selected the cell B4 as text and FIND(” “,B4,1)-1 as  num_chars.

Next, in the FIND function, I used ” ” (space) as find_text, selected cell B4 as within_text, and used 1 as start_num.

Formula Breakdown

FIND(” “,B4,1)—> will find the position of the first space character.
     Output: 5

FIND(” “,B4,1)-1 —> becomes
     5-1
     Output: 4

LEFT(B4,FIND(” “,B4,1)-1)—> This will return the First Name from the Full Name column.
     LEFT(B4, 4)
     Output: Adam
     Explanation: Extracted the First 4 letters from the Full Name.

⏩ Press the ENTER key and you will get the First Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Using LEFT & FIND Function to Split First Name in Excel

 

Read More: How to Separate Words in Excel Using Formula (Ultimate Guide)


4.2. Using RIGHT & FIND Function to Split Last Name

To split the Last Name from the Full Name you can use the RIGHT function with the FIND function and the LEN function.

To start with, select any cell to place the Last Name.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

=RIGHT(B4,LEN(B4)-FIND(" ",B4,1))

Using RIGHT & FIND Function to Split First Name in Excel

Here, in the RIGHT function, I selected the cell B4 as text and LEN(B4)-FIND(” “,B4,1) as  num_chars.

Next, in the LEN function, I selected the B4 cell as text to get the length of the value of the B4 cell.

Then, in the FIND function, I used ” ” (space) as find_text, selected cell B4 as within_text, and used 1 as start_num.

Formula Breakdown

FIND(” “,B4,1)—> will find the position of the first space character.
     Output: 5

LEN(B4)—> will return the number of characters in the text string.
     Output: 10

LEN(B4)-FIND(” “,B4,1) —> becomes
     10-5
     Output: 5

RIGHT(B4,LEN(B4)-FIND(” “,B4,1))—> This will return the Last Name from the Full Name column.
     RIGHT(B4, 5)
     Output: Smith
     Explanation: Extracted the Last 5 letters from the Full Name.

⏩ Press the ENTER key and you will get the Last Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Using RIGHT & FIND Function to Split First Name in Excel


5. Using Function to Split First And Last Name with Comma

In case you have Full Name separated with a comma then you also can use the Excel functions to Split First and Last Name.

To demonstrate the procedure, I’m going to use the dataset given below where I separated names with commas.

Using Function to Split First And Last Name with Comma in Excel


5.1. Using LEFT & SEARCH Function to Split First Name

You can use the LEFT function and the SEARCH function to split the First Name from the Full Name.

To start with, select any cell to place the First Name.

➤ I selected the D4 cell.

⏩ In cell D4, type the following formula.

=LEFT(B4,SEARCH(" ",B4)-2)

Using LEFT & SEARCH Function to Split First Name in Excel

Here, in the LEFT function, I selected the cell B4 as text and SEARCH(” “,B4)-2 as  num_chars. Here, I subtracted 2 as I have two extra characters (comma & space).

Next, in the SEARCH function, I used ” ” (space) as find_text, selected cell B4 as within_text.

Formula Breakdown

SEARCH(” “,B4) —> will search the position of the first space character.
     Output: 6

SEARCH(” “,B4)-2 —> becomes
     6-2
     Output: 4

LEFT(B4,SEARCH(” “,B4)-2)—> This will return the First Name from the Full Name column.
     LEFT(B4, 4)
     Output: Adam
     Explanation: Extracted the First 4 letters from the Full Name.

⏩ Press the ENTER key and you will get the First Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Using LEFT & SEARCH Function to Split First Name in Excel


5.2. Using RIGHT & SEARCH Function to Split Last Name

To separate the Last Name from the Full Name you can use the RIGHT function with the SEARCH function and the LEN function.

To start with, select any cell to place the Last Name.

➤ I selected the E4 cell.

⏩ In cell E4, type the following formula.

=RIGHT(B4, LEN(B4) - SEARCH(" ", B4))

Using RIGHT & SEARCH Function to Split Last Name

Here, in the RIGHT function, I selected the cell B4 as text and LEN(B4) – SEARCH(” “, B4) as num_chars.

Next, in the LEN function, I selected the B4 cell as text to get the length of the value of the B4 cell.

Then, in the SEARCH function, I used ” ” (space) as find_text, selected cell B4 as within_text.

Formula Breakdown

SEARCH(” “, B4) —> will search the position of the first space character.
     Output: 6

LEN(B4) —> will return the number of characters in the text string.
     Output: 11

LEN(B4) – SEARCH(” “, B4) —> becomes
     11-6
     Output: 5

RIGHT(B4, LEN(B4) – SEARCH(” “, B4)) —> This will return the Last Name from the Full Name column.
     RIGHT(B4, 5)
     Output: Smith
     Explanation: Extracted the Last 5 letters from the Full Name.

⏩ Press the ENTER key and you will get the Last Name from the Full Name column.

⏩ Now, you use the Fill Handle to AutoFill the formula for the rest of the cells.

Using RIGHT & SEARCH Function to Split Last Name


6. Using Find and Replace to Split First And Last Name

You can use the Find & Replace feature with wildcards characters to Split First and Last Name.


6.1 Find The First Name

By using the Replace from Find & Select feature you can extract the First Name from the Full Name.

To begin with, copy all the names from Full Name to any new column.

➤ I selected the range B4:B13 and copied all the data to the First Name column.

Find The First Name in Excel

Next, select the cell range from which you want to extract only your First Name.

➤ I selected the cell range D4:D13.

Then, open the Home tab >> from Editing group >> go to Find & Select >> select Replace

A dialog box will pop up.

⏩ I used single Space then Asterisk(*) in Find what because I only want the values before space.

⏩ I kept the Replace with field Blank.

Then, click on Replace All.

Find The First Name in Excel

A message will pop up showing how many replacements occurred.

⏩ We made 10 replacements.

Then, click OK and close the dialog box.

⏩ Here, all the characters after space are replaced with Blank and only find the First Name.


6.2. Find The Last Name

You also can use the Replace from Find & Select feature to extract the Last Name from the Full Name.

To begin with, copy all the names from Full Name to any new column.

➤ I selected the range B4:B13 and copied all the data to the Last Name column.

Find The Last Name in Excel

Next, select the cell range from where you want to extract only Last Name.

➤ I selected the cell range E4:E13.

Then, open the Home tab >> from Editing group >> go to Find & Select >> select Replace

A dialog box will pop up.

⏩ I used Asterisk(*) then single Space in Find what because I only want the values after space.

⏩ I kept the Replace with field Blank.

Then, click on Replace All.

Find The Last Name in Excel

A message will pop up showing how many replacements occurred.

⏩ We made 10 replacements.

Then, click OK and close the dialog box.

⏩ Here, all the characters before space are replaced with Blank and you will get the Last Name.

Find The Last Name in Excel


Things to Remember

🔺 You only can use the Flash Fill feature in Excel 2013, 2016, 2019, and later versions.


Practice Section

I’ve provided a practice sheet in the workbook to practice these explained examples.


Conclusion

In this article, I have explained 6 ways to split first and last name in Excel. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

1 Comment
  1. Thanks, Shamina, This is a very useful and helpful article. I appreciate you taking the time to document these options. Regards, Steph (Sinclair)

Leave a reply

ExcelDemy
Logo