Split First Name Middle and Last Name in Excel

Split Name First Middle and Last name in Excel with formula. There are 4 Excel formulas that we can use to extract names in excel.

1- Left Formula

2 - Mid Formula

3 - Right Formula

4 - Len Formula



To split middle name in Excel, the main idea is to find the spaces between the first name and the last name. Once that is done then it become easy to split words or extract words in excel.





Extract First name in Excel



The first name starts with the first character from the left (E) and ends at the fifth character (the first space). The formula extracts the first five characters in A2, starting from the left.



Formula for separating a first and last name, plus middle initial

Use the SEARCH function to find the value for num_chars:



Search for the numeric position of the space in A2, starting from the left. (5)



Split  Middle name in Excel



The middle name starts at the sixth character position (S), and ends at the eighth position (the second space). This formula involves nesting SEARCH functions to find the second instance of a space.



The formula extracts three characters, starting from the sixth position.



Details of a formula for separating first, middle, and last names

Use the SEARCH function to find the value for start_num:



Search for the numeric position of the first space in A2, starting from the first character from the left. (5).



Add 1 to get the position of the character after the first space (S). This numeric position is the starting position of the middle name. (5 + 1 = 6)



Use nested SEARCH functions to find the value for num_chars:



Search for the numeric position of the first space in A2, starting from the first character from the left. (5)



Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)



Search for the second instance of space in A2, starting from the sixth position (S) found in step 4. This character number is the ending position of the middle name. (8)



Search for the numeric position of space in A2, starting from the first character from the left. (5)



Take the character number of the second space found in step 5 and subtract the character number of the first space found in step 6. The result is the number of characters MID extracts from the text string starting at the sixth position found in step 2. (8 – 5 = 3)



Split Last name in Excel



The last name starts six characters from the right (K) and ends at the first character from the right (n). This formula involves nesting SEARCH functions to find the second and third instances of a space (which are at the fifth and eighth positions from the left).



The formula extracts six characters in A2, starting from the right.



The second SEARCH function in a formula for separating first, middle, and last names

Use the LEN and nested SEARCH functions to find the value for num_chars:



Search for the numeric position of space in A2, starting from the first character from the left. (5)



Add 1 to get the position of the character after the first space (S). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)



Search for the second instance of space in A2, starting from the sixth position (S) found in step 2. This character number is the ending position of the middle name. (8)



Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second instance of space found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 – 8 = 6).







You can download this file from http://www.myeleson.org/downloads











To watch more videos and download the files visit http://www.myelesson.org

To Buy The  Full Excel Course visit . http://www.myelesson.org/product    or call 9752003788

Connect with us on Facebook - https://www.facebook.com/excelmadeasy/

Connect with us on Twitter - https://twitter.com/Excelmadeasy