How to Use the MID Function to Separate Last Name from First Name in Excel

Sometimes, you may have a column of data that contains both the first name and the last name of a person, separated by a space or a comma. For example, you may have a list of customers or employees like this:

In this case, you may want to split the full name into two separate columns, one for the first name and one for the last name. This can make it easier to sort, filter, or analyze the data based on the names.

One way to do this in Excel is to use the MID function. The MID function returns a substring from a text string, given a starting position and a length. For example, =MID("Hello",2,3) returns ell, which is the substring of “Hello” starting from the second character and having a length of three characters.

In this article, we will show you how to use the MID function to separate the last name from the first name in Excel, step by step. We will also provide a comprehensive explanation of the logic behind the formula, and some alternative approaches that you can use.

The MID Function

The syntax of the MID function is:

=MID(text, start_num, num_chars)

where:

• text is the text string that you want to extract a substring from.
• start_num is the position of the first character that you want to extract, starting from 1.
• num_chars is the number of characters that you want to extract.

For example, =MID("Hello",2,3) returns ell, as explained above.

The Formula

To use the MID function to separate the last name from the first name in Excel, we need to find the position of the space or the comma that separates the names, and then use that position to extract the substrings.

For example, if we have the full name “John Smith” in cell A2, we can use the following formula to get the last name:

=MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2))

This formula works as follows:

• FIND(" ",A2) returns the position of the space in A2, which is 5.
• FIND(" ",A2)+1 returns the position of the first character of the last name, which is 6.
• LEN(A2) returns the length of the text in A2, which is 10.
• LEN(A2)-FIND(" ",A2) returns the number of characters in the last name, which is 5.
• MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)) returns the substring of A2 starting from the 6th character and having a length of 5 characters, which is “Smith”.

Similarly, we can use the following formula to get the first name:

=MID(A2,1,FIND(" ",A2)-1)

This formula works as follows:

• FIND(" ",A2) returns the position of the space in A2, which is 5.
• FIND(" ",A2)-1 returns the position of the last character of the first name, which is 4.
• MID(A2,1,FIND(" ",A2)-1) returns the substring of A2 starting from the 1st character and having a length of 4 characters, which is “John”.

The Example

To demonstrate how to use the MID function to separate the last name from the first name in Excel, we will use the following example:

We want to split the full name into two columns, one for the first name and one for the last name, like this:

To do this, we can use the following steps:

1. In cell B2, enter the formula =MID(A2,1,FIND(" ",A2)-1) to get the first name of the first person.
2. In cell C2, enter the formula =MID(A2,FIND(" ",A2)+1,LEN(A2)-FIND(" ",A2)) to get the last name of the first person.
3. Copy the formulas in B2 and C2 and paste them down to B5 and C5 to get the first and last names of the rest of the people.
4. Format the cells as desired.

The result should look like this:

The Explanation

The logic behind the formula is to use the MID function to extract the substrings before and after the space or the comma that separates the names.

To do this, we need to find the position of the space or the comma using the FIND function. The FIND function returns the position of a specific character or text within another text string, starting from 1. For example, =FIND(" ",A2) returns the position of the space in A2, which is 5.

Once we have the position of the space or the comma, we can use it to calculate the starting position and the length of the substrings that we want to extract.

For the first name, the starting position is always 1, and the length is the position of the space or the comma minus 1. For example, for “John Smith”, the starting position is 1 and the length is 5 – 1 = 4.

For the last name, the starting position is the position of the space or the comma plus 1, and the length is the total length of the text minus the position of the space or the comma. For example, for “John Smith”, the starting position is 5 + 1 = 6 and the length is 10 – 5 = 5.

Then, we can use the MID function to extract the substrings using the calculated starting position and length.

The Alternatives

There are other ways to separate the last name from the first name in Excel, besides using the MID function. Here are some of them:

• Using the LEFT and RIGHT functions: The LEFT function returns the leftmost characters of a text string, given a number of characters. The RIGHT function returns the rightmost characters of a text string, given a number of characters. For example, =LEFT(A2,FIND(" ",A2)-1) returns the first name and =RIGHT(A2,LEN(A2)-FIND(" ",A2)) returns the last name.
• Using the Text to Columns feature: The Text to Columns feature allows you to split a column of text into multiple columns based on a delimiter, such as a space or a comma. To use this feature, select the column of text that you want to split, go to the Data tab, click on Text to Columns, choose Delimited, select the delimiter that separates the names, and click Finish.
• Using the Flash Fill feature: The Flash Fill feature automatically fills in values in a column based on a pattern that you provide. To use this feature, enter the first name of the first person in the first cell of the column, press Enter, enter the first name of the second person in the second cell of the column, and press Ctrl + E. Excel will fill in the rest of the column with the first names of the people. Repeat the same steps for the last names in another column.