Split Name In Excel

This topic has been explained in great detail in the video shown below, to learn this topic just play the video and the notes are mentioned below the video.


Split Name In Excel






Split Fist Name and Last Name In Excel

The Video above explains how Split Fist Name and Last Name In MS Excel. The Text below is from a Microsoft Excel Support Blog

Example 1: Separating Jeff and Smith

This example separates two components: first name and last name. A single space separates the two names.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Jeff Smith No middle name
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))
  1. First nameThe first name starts with the first character in the string (J) and ends at the fifth character (the space). The formula returns five characters in cell A2, starting from the left.Formula for extracting a first name

    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.

  2. Last nameThe last name starts at the space, five characters from the right, and ends at the last character on the right (h). The formula extracts five characters in A2, starting from the right.Formula for extracting a last name

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

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

  3. Count the total length of the text string, and then subtract the number of characters to the left of the first space, as found in step 1.

 

Example 2: Eric S. Kurjan: Extract first and last names, plus middle initial

This example uses a first name, middle initial, and last name. A space separates each name component.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Eric S. Kurjan One middle initial
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (middle initial)
‘=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1)) =MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1))
Formula Live Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
  1. First nameThe 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 a 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)

  2. Middle nameThe 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).

  3. 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)
  4. 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)
  5. 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)
  6. Search for the numeric position of space in A2, starting from the first character from the left. (5)
  7. 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)
  8. Last nameThe 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

  9. 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)
  10. 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)
  11. 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)
  12. 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).

 

Example 3: Janaina B. G. Bueno

Here’s an example of how to extract two middle initials. The first and third instances of space separate the name components.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Janaina B. G. Bueno Two middle initials
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (middle initials)
‘=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-SEARCH(” “,A2,1)) =MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-SEARCH(” “,A2,1))
Formula Live Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1))
  1. First nameThe first name starts with the first character from the left (J) and ends at the eighth character (the first space). The formula extracts the first eight characters in A2, starting from the left.Formula for separating first name, last name, and two middle initials

    Use the SEARCH function to find the value for num_chars:

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

  2. Middle nameThe middle name starts at the ninth position (B), and ends at the fourteenth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space in the eighth, eleventh, and fourteenth positions.The formula extracts five characters, starting from the ninth position.

    Formula for separating first name, last name, and two middle initials

    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. (8)

  3. Add 1 to get the position of the character after the first space (B). This numeric position is the starting position of the middle name. (8 + 1 = 9)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. (8)
  4. Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)
  5. Search for the second space in A2, starting from the ninth position (B) found in step 4. (11).
  6. Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third space. (11 + 1 = 12)
  7. Search for the third space in A2, starting at the twelfth position found in step 6. (14)
  8. Search for the numeric position of the first space in A2. (8)
  9. Take the character number of the third space found in step 7 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 ninth position found in step 2.
  10. Last nameThe last name starts five characters from the right (B) and ends at the first character from the right (o). This formula involves nesting SEARCH to find the first, second, and third instances of space.The formula extracts five characters in A2, starting from the right of the full name.

    Formula for separating first name, last name, and two middle initials

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

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

  11. Add 1 to get the position of the character after the first space (B). The result is the character number at which you want to start searching for the second instance of space. (8 + 1 = 9)
  12. Search for the second space in A2, starting from the ninth position (B) found in step 2. (11)
  13. Add 1 to get the position of the character after the second space (G). This character number is the starting position at which you want to start searching for the third instance of space. (11 + 1 = 12)
  14. Search for the third space in A2, starting at the twelfth position (G) found in step 6. (14)
  15. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space found in step 5. The result is the number of characters to be extracted from the right of the full name. (19 – 14 = 5)

 

Example 4: Kahn, Wendy Beth

In this example, the last name comes before the first, and the middle name appears at the end. The comma marks the end of the last name, and a space separates each name component.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Kahn, Wendy Beth Last name first, with comma
Formula Result (first name)
‘=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1)) =MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-SEARCH(” “,A2,1))
Formula Result (middle name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
Formula Live Result (last name)
‘=LEFT(A2, SEARCH(” “,A2,1)-2) =LEFT(A2, SEARCH(” “,A2,1)-2)
  1. First nameThe first name starts with the seventh character from the left (W) and ends at the twelfth character (the second space). Because the first name occurs at the middle of the full name, you need to use the MID function to extract the first name.The formula extracts six characters, starting from the seventh position.

    Formula for separating a last name followed by a first and a middle name

    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. (6)

  2. Add 1 to get the position of the character after the first space (W). This numeric position is the starting position of the first name. (6 + 1 = 7)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. (6)
  3. Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)Search for the second space in A2, starting from the seventh position (W) found in step 4. (12)
  4. Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
  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 seventh position found in step 2. (12 – 6 = 6)
  6. Middle nameThe middle name starts four characters from the right (B), and ends at the first character from the right (h). This formula involves nesting SEARCH to find the first and second instances of space in the sixth and twelfth positions from the left.The formula extracts four characters, starting from the right.

    Formula for separating a last name followed by a first and a middle name

    Use nested SEARCH and the LEN functions 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. (6)

  7. Add 1 to get the position of the character after the first space (W). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)
  8. Search for the second instance of space in A2 starting from the seventh position (W) found in step 2. (12)
  9. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space found in step 3. The result is the number of characters to be extracted from the right of the full name. (16 – 12 = 4)
  10. Last nameThe last name starts with the first character from the left (K) and ends at the fourth character (n). The formula extracts four characters, starting from the left.Formula for separating a last name followed by a first and a middle name

    Use the SEARCH function 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. (6)

  11. Subtract 2 to get the numeric position of the ending character of the last name (n). The result is the number of characters you want LEFT to extract. (6 – 2 =4)

 

Example 5: Mary Kay D. Andersen

This example uses a two-part first name, Mary Kay. The second and third spaces separate each name component.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Mary Kay D. Andersen Two-part first name
Formula Result (first name)
LEFT(A2, SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =LEFT(A2, SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
Formula Result (middle initial)
‘=MID(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-(SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)) =MID(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-(SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1))
Formula Live Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1))
  1. First nameThe first name starts with the first character from the left and ends at the ninth character (the second space). This formula involves nesting SEARCH to find the second instance of space from the left.The formula extracts nine characters, starting from the left.

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

    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)

  2. Add 1 to get the position of the character after the first space (K). The result is the character number at which you want to start searching for the second instance of space. (5 + 1 = 6)
  3. Search for the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the text string. (9)
  4. Middle nameThe middle name starts at the tenth position (D), and ends at the twelfth position (the third space). This formula involves nesting SEARCH to find the first, second, and third instances of space.The formula extracts two characters from the middle, starting from the tenth position.

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

    Use nested SEARCH functions 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)

  5. Add 1 to get the character after the first space (K). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)
  6. Search for the position of the second instance of space in A2, starting from the sixth position (K) found in step 2. The result is the number of characters LEFT extracts from the left. (9)
  7. Add 1 to get the character after the second space (D). The result is the starting position of the middle name. (9 + 1 = 10)Use nested SEARCH functions to find the value for num_chars:Search for the numeric position of the character after the second space (D). The result is the character number at which you want to start searching for the third space. (10)
  8. Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (12)
  9. Search for the numeric position of the character after the second space (D). The result is the beginning position of the middle name. (10)
  10. Take the character number of the third space, found in step 6, and subtract the character number of “D”, found in step 7. The result is the number of characters MID extracts from the text string starting at the tenth position found in step 4. (12 – 10 = 2)
  11. Last nameThe last name starts eight characters from the right. This formula involves nesting SEARCH to find the first, second, and third instances of space in the fifth, ninth, and twelfth positions.The formula extracts eight characters from the right.

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

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

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

  12. Add 1 to get the character after the first space (K). The result is the character number at which you want to start searching for the space. (5 + 1 = 6)
  13. Search for the second space in A2, starting from the sixth position (K) found in step 2. (9)
  14. Add 1 to get the position of the character after the second space (D). The result is the starting position of the middle name. (9 + 1 = 10)
  15. Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (12)
  16. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space found in step 5. The result is the number of characters to be extracted from the right of the full name. (20 – 12 = 8)

 

Example 6: Paula Barreto de Mattos

This example uses a three-part last name: Barreto de Mattos. The first space marks the end of the first name and the beginning of the last name.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Paula Barreto de Mattos Three-part last name
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (last name)
RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))
  1. First nameThe first name starts with the first character from the left (P) and ends at the sixth character (the first space). The formula extracts six characters from the left.Formula for separating a first name and a three-part last name

    Use the Search function to find the value for num_chars:

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

  2. Last nameThe last name starts seventeen characters from the right (B) and ends with first character from the right (s). The formula extracts seventeen characters from the right.Formula for separating a first name and a three-part last name

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

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

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

 

Example 7: James van Eaton

This example uses a two-part last name: van Eaton. The first space marks the end of the first name and the beginning of the last name.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
James van Eaton Two-part last name
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))
  1. First nameThe first name starts with the first character from the left (J) and ends at the eighth character (the first space). The formula extracts six characters from the left.Formula for separating a first name and a two-part last name

    Use the SEARCH function to find the value for num_chars:

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

  2. Last nameThe last name starts with the ninth character from the right (v) and ends at the first character from the right (n). The formula extracts nine characters from the right of the full name.Formula for separating a first name and a two-part last name

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

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

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

 

Example 8: Bacon Jr., Dan K.

In this example, the last name comes first, followed by the suffix. The comma separates the last name and suffix from the first name and middle initial.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Bacon Jr., Dan K. Last name and suffix first, with comma
Formula Result (first name)
‘=MID(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =MID(A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1,SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
Formula Result (middle initial)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)+1))
Formula Result (last name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (suffix)
‘=MID(A2,SEARCH(” “, A2,1)+1,(SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-2)-SEARCH(” “,A2,1)) =MID(A2,SEARCH(” “, A2,1)+1,(SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-2)-SEARCH(” “,A2,1))
  1. First nameThe first name starts with the twelfth character (D) and ends with the fifteenth character (the third space). The formula extracts three characters, starting from the twelfth position.Formula for extracting the first name of Example 8: Bacon Jr., Dan K.

    Use nested SEARCH functions to find the value for start_num:

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

  2. Add 1 to get the character after the first space (J). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)
  3. Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)
  4. Add 1 to get the character after the second space (D). The result is the starting position of the first name. (11 + 1 = 12)Use nested SEARCH functions to find the value for num_chars:Search for the numeric position of the character after the second space (D). The result is the character number at which you want to start searching for the third space. (12)
  5. Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the first name. (15)
  6. Search for the numeric position of the character after the second space (D). The result is the beginning position of the first name. (12)
  7. Take the character number of the third space, found in step 6, and subtract the character number of “D”, found in step 7. The result is the number of characters MID extracts from the text string starting at the twelfth position, found in step 4. (15 – 12 = 3)
  8. Middle nameThe middle name starts with the second character from the right (K). The formula extracts two characters from the right.Formula for extracting the middle name of Example 8: Bacon Jr., Dan K. 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 left. (6)

  9. Add 1 to get the character after the first space (J). The result is the character number at which you want to start searching for the second space. (6 + 1 = 7)
  10. Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)
  11. Add 1 to get the character after the second space (D). The result is the starting position of the first name. (11 + 1 = 12)
  12. Search for the numeric position of the third space in A2, starting from the left. The result is the ending position of the middle name. (15)
  13. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the third space, found in step 5. The result is the number of characters to be extracted from the right of the full name. (17 – 15 = 2)
  14. Last nameThe last name starts at the first character from the left (B) and ends at sixth character (the first space). Therefore, the formula extracts six characters from the left.Formula for extracting the last name of Example 8: Bacon Jr., Dan K.

    Use the SEARCH function to find the value for num_chars:

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

  15. SuffixThe suffix starts at the seventh character from the left (J), and ends at ninth character from the left (.). The formula extracts three characters, starting from the seventh character.Formula for extracting the suffix of Example 8: Bacon Jr., Dan K.

    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 left. (6)

  16. Add 1 to get the character after the first space (J). The result is the starting position of the suffix. (6 + 1 = 7)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 left. (6)
  17. Add 1 to get the numeric position of the character after the first space (J). The result is the character number at which you want to start searching for the second space. (7)
  18. Search for the numeric position of the second space in A2, starting from the seventh character found in step 4. (11)
  19. Subtract 1 from the character number of the second space found in step 4 to get the character number of “,”. The result is the ending position of the suffix. (11 – 1 = 10)
  20. Search for the numeric position of the first space. (6)
  21. After finding the first space, add 1 to find the next character (J), also found in steps 3 and 4. (7)
  22. Take the character number of “,” found in step 6, and subtract the character number of “J”, found in steps 3 and 4. The result is the number of characters MID extracts from the text string starting at the seventh position, found in step 2. (10 – 7 = 3)

 

Example 9: Gary Altman III

In this example, the first name is at the beginning of the string and the suffix is at the end, so you can use formulas similar to Example 2: Use the LEFT function to extract the first name, the MID function to extract the last name, and the RIGHT function to extract the suffix.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Gary Altman III First and last name with suffix
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (last name)
‘=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-(SEARCH(” “,A2,1)+1)) =MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-(SEARCH(” “,A2,1)+1))
Formula Result (suffix)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
  1. First nameThe first name starts at the first character from the left (G) and ends at the fifth character (the first space). Therefore, the formula extracts five characters from the left of the full name.Formula for separating first name, last name, and title

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

  2. Last nameThe last name starts at the sixth character from the left (A) and ends at the eleventh character (the second space). This formula involves nesting SEARCH to find the positions of the spaces.The formula extracts six characters from the middle, starting from the sixth character.

    Formula for separating first name, last name, and title

    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 left. (5)

  3. Add 1 to get the position of the character after the first space (A). The result is the starting position of the last 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 left. (5)
  4. Add 1 to get the position of the character after the first space (A). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)
  5. Search for the numeric position of the second space in A2, starting from the sixth character found in step 4. This character number is the ending position of the last name. (12)
  6. Search for the numeric position of the first space. (5)
  7. Add 1 to find the numeric position of the character after the first space (A), also found in steps 3 and 4. (6)
  8. Take the character number of the second space, found in step 5, and then subtract the character number of “A”, found in steps 6 and 7. The result is the number of characters MID extracts from the text string, starting at the sixth position, found in step 2. (12 – 6 = 6)
  9. SuffixThe suffix starts three characters from the right. This formula involves nesting SEARCH to find the positions of the spaces.Formula for separating first name, last name, and title

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

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

  10. Add 1 to get the character after the first space (A). The result is the character number at which you want to start searching for the second space. (5 + 1 = 6)
  11. Search for the second space in A2, starting from the sixth position (A), found in step 2. (12)
  12. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space, found in step 3. The result is the number of characters to be extracted from the right of the full name. (15 – 12 = 3)

 

Example 10: Mr. Ryan Ihrig

In this example, the full name is preceded by a prefix, and you use formulas similar to Example 2: the MID function to extract the first name, the RIGHT function to extract the last name.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Mr. Ryan Ihrig With prefix
Formula Result (first name)
‘=MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-(SEARCH(” “,A2,1)+1)) =MID(A2,SEARCH(” “,A2,1)+1,SEARCH(” “,A2,SEARCH(” “,A2,1)+1)-(SEARCH(” “,A2,1)+1))
Formula Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,SEARCH(” “,A2,1)+1))
  1. First nameThe first name starts at the fifth character from the left (R) and ends at the ninth character (the second space). The formula nests SEARCH to find the positions of the spaces. It extracts four characters, starting from the fifth position.Formula for extracting the first name of Example 10: Mr. Ryan Ihrig

    Use the SEARCH function to find the value for the start_num:

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

  2. Use nAdd 1 to get the position of the character after the first space (R). The result is the starting position of the first name. (4 + 1 = 5)Use nested SEARCH function to find the value for num_chars:Search for the numeric position of the first space in A2, starting from the left. (4)
  3. Add 1 to get the position of the character after the first space (R). The result is the character number at which you want to start searching for the second space. (4 + 1 = 5)
  4. Search for the numeric position of the second space in A2, starting from the fifth character, found in steps 3 and 4. This character number is the ending position of the first name. (9)
  5. Search for the first space. (4)
  6. Add 1 to find the numeric position of the character after the first space (R), also found in steps 3 and 4. (5)
  7. Take the character number of the second space, found in step 5, and then subtract the character number of “R”, found in steps 6 and 7. The result is the number of characters MID extracts from the text string, starting at the fifth position found in step 2. (9 – 5 = 4)
  8. Last nameThe last name starts five characters from the right. This formula involves nesting SEARCH to find the positions of the spaces.Formula for extracting the last name of Example 10: Mr. Ryan Ihrig Use nested SEARCH and the LEN functions to find the value for num_chars:

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

  9. Add 1 to get the position of the character after the first space (R). The result is the character number at which you want to start searching for the second space. (4 + 1 = 5)
  10. Search for the second space in A2, starting from the fifth position (R), found in step 2. (9)
  11. Count the total length of the text string in A2, and then subtract the number of characters from the left up to the second space, found in step 3. The result is the number of characters to be extracted from the right of the full name. (14 – 9 = 5)

 

Example 11: Julie Taft-Rider

This example uses a hyphenated last name. A space separates each name component.

Copy the cells in the table and paste into an Excel worksheet at cell A1. The formula you see on the left will be displayed for reference, while Excel will automatically convert the formula on the right into the appropriate result.

Hint    Before you paste the data into the worksheet, set the column widths of columns A and B to 250.

Example name Description
Julie Taft-Rider Hyphenated last name
Formula Result (first name)
‘=LEFT(A2, SEARCH(” “,A2,1)) =LEFT(A2, SEARCH(” “,A2,1))
Formula Result (last name)
‘=RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(” “,A2,1))
  1. First nameThe first name starts at the first character from the left and ends at the sixth position (the first space). The formula extracts six characters from the left.Formula for extracting the first name of Example 11: Julie Taft-Rider

    Use the SEARCH function to find the value of num_chars:

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

  2. Last nameThe entire last name starts ten characters from the right (T) and ends at the first character from the right (r).Formula for extracting the full last name of Example 11: Julie Taft-Rider

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

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

  3. Count the total length of the text string to be extracted, and then subtract the number of characters from the left up to the first space, found in step 1. (16 – 6 = 10)

 








Our Other Services




Read Free Ebooks

myeboook.com

Myebook has 1000's of free ebooks for you. On productivity, health, online income, learning, music and many more, it's free of course!



Buy Excel Course

myelesson.org/product

Become an Excel Guru, Buy the best Excel course. Learn on your mobile, tv & laptop without internet. For COD, Call 9752003788



Create Best Looking Resume

http://www.makecv.org/

At Makecv.org you get to create a amazingly professional looking resume in just 3 steps with our intuitive resume builder!


Download Excel Crash Course


This Excel Crash Course contains the list of topics that cover the most important and used features and formula of Excel . This Excel Crash Course lists 43 major topics in Excel so that you can start using Excel like a pro in the shortest possible time.



Learn Photoshop Web Designing


You can Buy the Photoshop Course to learn Photoshop and start a career in Web Designing and Photo Editing. .



Learn HTML CSS


You can Buy the HTML CSS Course to learn HTML CSS and start a career in Web Designing and Photo Editing. .


Download Full list of Excel Shortcuts


This Excel Shortcuts Complete list is to help you become save time and use Excel like a Master level user. Excel is best used when you know the Keyboard shortcuts because the use of keyboard shortcuts in Excel helps you in completing tasks faster.

I have created a Excel Shortcuts complete list for you to use Excel better, this list has 242 excel keyboard shortcuts which means that it covers almost everything that can be done using shortcuts in Excel.




Full List of Excel Formulas

This is a complete alphabetical list of all the Formula in Excel with a description. You can learn all these formulas on www.Myelesson.org




Convert Numbers to Text in Excel

Now easily convert numbers to Text in Excel with this inbuilt sheet from myelesson.org .  Many times we need the amount in figures to be converted into words. This is a typical requirement for writing checks or any other financial reports. Microsoft Excel does not have standard function available for this requirement. Just download this sheet and then you can convert any number to text in Excel.




List of Most Commonly Asked Excel Questions in Interviews

Here is the list of most asked excel related questions in interviews.  Prepare for this excel questions and you would be able to most of the excel interviews. This list of excel questions covers excel formula, excel analysis, excel reporting, excel charts and more.





Myelesson © 2010 - 2014. All rights reserved.

Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok
Do You Want to Become a Expert in Excel ?
Buy The Full Excel Course Now
Enter Your Details & We Will Contact You
Great Choice
Thank You
Ok