Split text among columns by using functions

Text functions are useful for manipulating strings in your data, for example, distributing the first, middle, and last names from a cell into three separate columns.

This article demonstrates how to use combinations of the following text functions to extract and copy name components into separate cells.

Function Syntax
LEFT LEFT(text, num_chars)
MID MID(text,start_num,num_chars)
RIGHT RIGHT(text, num_chars)
SEARCH SEARCH(find_text,within_text,start_num)
LEN LEN(text)

Extracting name components

The key to distributing name components when you use text functions is the position of each character within a text string. The positions of the spaces within the text string are important because they indicate the beginning or end of name components in a string.

For example, in a cell that contains only a first and last name, the last name begins after the first instance of a space. Some names in your list may contain a middle name, in which case, the last name begins after the second instance of a space.

This article shows you how to extract various components from a variety of name formats. Click an example name in the following table to see the formulas for extracting the components from that name.

 Note   The formulas for each example may be easier to understand if you copy them to a blank worksheet.

ShowHow to copy an example

  1. Create a blank workbook or worksheet.
  2. Select the example text and formulas in the table.

 Note   Do not select the row or column headers.

Selecting an example from Help

  1. Press CTRL+C.
  2. In the worksheet, select cell A1, and then press CTRL+V.
  3. To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Formulas tab, in the Formula Auditing group, click the Show Formulas button.

Example name Description First name Middle name Last name Suffix
1 Jeff Smith No middle name Jeff   Smith  
2 Eric S. Kurjan One middle initial Eric S. Kurjan  
3 Janaina B. G. Bueno Two middle initials Janaina B. G. Bueno  
4 Kahn, Wendy Beth Last name first, with comma Wendy Beth Kahn  
5 Mary Kay D. Andersen Two-part first name Mary Kay D. Andersen  
6 Paula Barreto de Mattos Three-part last name Paula   Barreto de Mattos  
7 James van Eaton Two-part last name James   van Eaton  
8 Bacon Jr., Dan K. Last name and suffix first, with comma Dan K. Bacon Jr.
9 Gary Altman III With suffix Gary   Altman III
10 Mr. Ryan Ihrig With prefix Ryan   Ihrig  
11 Julie Taft-Rider Hyphenated last name Julie   Taft-Rider  

Example 1: Jeff Smith


In this example, there are only two components: first name and last name. A single space separates the two name components.

 
1
2
A B C
Full name First name Last name
Jeff Smith =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 A2, starting from the left.


Formula for extracting the first name of Example 1: Lisa Smith

Use the SEARCH function to find the value for num_chars:

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

Last name

The 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 the last name of Example 1: Lisa Smith

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

Callout 1 Search for the numeric position of the space in A2, starting from the left. (5)
Callout 2 Count the total length of the text string, and then subtract the number of characters from the left to the first space, as found in step 1. (10 - 5 = 5)

Top of Page Top of Page

Example 2: Eric S. Kurjan

In this example, there are three components in the full name: first name, middle initial, and last name. A space separates each name component.

 
1
2
A B C D
Name First Name (Eric) Middle Name (S.) Last Name (Kurjan)
Eric S. Kurjan =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

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 extracting the first name of Example 2: John Q. Public

Use the SEARCH function to find the value for num_chars:

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

Middle name

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.


Formula for extracting the middle name of Example 2: John Q. Public

Use the SEARCH function to find the value for start_num:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (5)
Callout 2 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:

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

Last name

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.


Formula for extractin the last name of Example 2: John Q. Public

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

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

Top of Page Top of Page

Example 3: Janaina B. G. Bueno

In this example, there are two middle initials. The first and third instances of space separate the name components.

 
1
2
A B C D
Name First Name (Janaina) Middle Name (B. G.) Last Name (Bueno)
Janaina B. G. Bueno =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 3: J. R. T. Robins

Use the SEARCH function to find the value for num_chars:

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

Middle name

The 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 extracting the middle name of Example 3: J. R. T. Robins

Use the SEARCH function to find the value for start_num:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (8)
Callout 2 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:

Callout 3 Search for the numeric position of the first space in A2, starting from the first character from the left. (8)
Callout 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)
Callout 5 Search for the second space in A2, starting from the ninth position (B) found in step 4. (11)
Callout 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)
Callout 7 Search for the third space in A2, starting at the twelfth position found in step 6. (14)
Callout 8 Search for the numeric position of the first space in A2. (8)
Callout 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.

Last name

The 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 extracting the last name of Example 3: J. R. T. Robins

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

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (8)
Callout 2 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)
Callout 3 Search for the second space in A2, starting from the ninth position (B) found in step 2. (11)
Callout 4 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)
Callout 5 Search for the third space in A2, starting at the twelfth position (G) found in step 6. (14)
Callout 6 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)

Top of Page Top of Page

Example 4: Kahn, Wendy Beth

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

 
1
2
A B C D
Name First Name (Wendy) Middle Name (Beth) Last Name (Kahn)
Kahn, Wendy Beth =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =LEFT(A2, SEARCH(" ",A2,1)-2)

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 4: Kahn, Wendy Beth

Use the SEARCH function to find the value for start_num:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
Callout 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:

Callout 3 Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
Callout 4 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)
Callout 5 Search for the second space in A2, starting from the seventh position (W) found in step 4. (12)
Callout 6 Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
Callout 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 seventh position found in step 2. (12 - 6 = 6)

Middle name

The 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 extracting the middle name of Example 4: Kahn, Wendy Beth

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

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
Callout 2 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)
Callout 3 Search for the second instance of space in A2 starting from the seventh position (W) found in step 2. (12)
Callout 4 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)

Last name

The 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 extracting the last name of Example 4: Kahn, Wendy Beth

Use the SEARCH function to find the value for num_chars:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (6)
Callout 2 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)

Top of Page Top of Page

Example 5: Mary Kay D. Andersen

In this example, there are two parts of the first name: Mary Kay. The second and third spaces separate each name component.

 
1
2
A B C D
Name First Name (Mary Kay) Middle Name (D.) Last Name (Andersen)
Mary Kay D. Anderson =LEFT(A2, 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)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 5: Mary Kay A. Wilde

Use nested SEARCH functions to find the value for num_chars:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (5)
Callout 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)
Callout 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)

Middle name

The 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 extracting the middle name of Example 5: Mary Kay A. Wilde

Use nested SEARCH functions to find the value for start_num:

Callout 1 Search for the numeric position of the first space in A2, starting from the first character from the left. (5)
Callout 2 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)
Callout 3 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)
Callout 4 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:

Callout 5 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)
Callout 6 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)
Callout 7 Search for the numeric position of the character after the second space (D). The result is the beginning position of the middle name. (10)
Callout 8 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)

Last name

The 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 extracting the last name of Example 5: Mary Kay A. Wilde

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

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (5)
Callout 2 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)
Callout 3 Search for the second space in A2, starting from the sixth position (K) found in step 2. (9)
Callout 4 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)
Callout 5 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)
Callout 6 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)

Top of Page Top of Page

Example 6: Paula Barreto de Mattos

In this example, there are three parts of the last name: Barreto de Mattos. The first space marks the end of the first name and the beginning of the last name.

 
1
2
A B D
Name First Name (Paula) Last Name (Barreto de Mattos)
Paula Barreto de Mattos =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 6: Job van den Wildenberg

Use the Search function to find the value for num_chars:

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

Last name

The 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 extracting the last name of Example 6: Job van den Wildenberg

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

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 2 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)

Top of Page Top of Page

Example 7: James van Eaton

In this example, there are two parts of the last name: van Eaton. The first space marks the end of the first name and the beginning of the last name.

 
1
2
A B D
Name First Name (James) Last Name (van Eaton)
James van Eaton =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 7: Michael St. James

Use the SEARCH function to find the value for num_chars:

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

Last name

The 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 extracting the last name of Example 7: Michael St. James

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

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 2 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)

Top of Page Top of Page

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.

 
1
2
A B C D E
Name First Name (Dan) Middle Name (K.) Last Name (Bacon) Suffix (Jr.)
Bacon Jr., Dan K. =MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ", A2,1)+1,(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-2)-SEARCH(" ",A2,1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 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)
Callout 3 Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)
Callout 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:

Callout 5 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)
Callout 6 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)
Callout 7 Search for the numeric position of the character after the second space (D). The result is the beginning position of the first name. (12)
Callout 8 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)

Middle name

The 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:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 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)
Callout 3 Search for the second space in A2, starting from the seventh position (J), found in step 2. (11)
Callout 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)
Callout 5 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)
Callout 6 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)

Last name

The 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:

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

Suffix

The 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:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 2 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:

Callout 3 Search for the numeric position of the first space in A2, starting from the left. (6)
Callout 4 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)
Callout 5 Search for the numeric position of the second space in A2, starting from the seventh character found in step 4. (11)
Callout 6 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)
Callout 7 Search for the numeric position of the character after the first space (J), also found in steps 3 and 4. (7)
Callout 8 Search for the numeric position of the character after the first space (J), also found in steps 3 and 4. (7)
Callout 9 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)

Top of Page Top of Page

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 of the name. The formula to use for the name components is similar to Example 2, where the first name can be extracted by using the LEFT function, the last name can be extracted by using the MID function, and the suffix can be extracted by using the RIGHT function.

 
1
2
A B C D
Name First Name (Gary) Last Name (Altman) Suffix (III)
Gary Altman III =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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 extracting the first name of Example 9: James Edwards III

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

Last name

The 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 extracting the last name of Example 9: James Edwards III

Use the SEARCH function to find the value for start_num:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (5)
Callout 2 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:

Callout 3 Search for the numeric position of the first space in A2, starting from the left. (5)
Callout 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)
Callout 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)
Callout 6 Search for the numeric position of the character after the first space (A), also found in steps 3 and 4. (6)
Callout 7 Search for the numeric position of the character after the first space (A), also found in steps 3 and 4. (6)
Callout 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)

Suffix

The suffix starts three characters from the right. This formula involves nesting SEARCH to find the positions of the spaces.


Formula for extracting the suffix of Example 9: James Edwards III

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

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (5)
Callout 2 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)
Callout 3 Search for the second space in A2, starting from the sixth position (A), found in step 2. (12)
Callout 4 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)

Top of Page Top of Page

Example 10: Mr. Ryan Ihrig

In this example, the full name is preceded by a prefix. The formulas to use for the name components are similar to Example 2, where the first name can be extracted by using the MID function and the last name can be extracted by using the RIGHT function.

 
1
2
A B C
Name First Name (Ryan) Last Name (Ihrig)
Mr. Ryan Ihrig =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The first name starts at the fifth character from the left (R) and ends at the ninth character (the second space). This formula involves nesting SEARCH to find the positions of the spaces.

The formula 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:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (4)
Callout 2 Add 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:

Callout 3 Search for the numeric position of the first space in A2, starting from the left. (4)
Callout 4 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)
Callout 5 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)
Callout 6 Search for the numeric position of the character after the first space (R), also found in steps 3 and 4. (5)
Callout 7 Search for the numeric position of the character after the first space (R), also found in steps 3 and 4. (5)
Callout 8 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)

Last name

The 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:

Callout 1 Search for the numeric position of the first space in A2, starting from the left. (4)
Callout 2 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)
Callout 3 Search for the second space in A2, starting from the fifth position (R), found in step 2. (9)
Callout 4 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)

Top of Page Top of Page

Example 11: Julie Taft-Rider

In this example, the last name is hyphenated. A space separates each name component.

 
1
2
A B C
Name First Name (Julie) Last Name (Taft-Rider)
Julie Taft-Rider =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 Note   In the following graphics, the highlight in the full name shows the character that the matching SEARCH formula is looking for.

First name

The 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:

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

Last name

The 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:

Callout 1 Search for the numeric position of the space in A2, starting from the first character from the left. (6)
Callout 2 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)

Top of Page Top of Page

 
 
Applies to:
Excel 2007