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.

12 ABCDENameFirst 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.

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) 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) Search for the second space in A2, starting from the seventh position (J), found in step 2. (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)

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

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) 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) Search for the second space in A2, starting from the seventh position (J), found in step 2. (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) 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) 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.

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

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) 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) 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) Search for the numeric position of the second space in A2, starting from the seventh character found in step 4. (11) 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) Search for the numeric position of the first space. (6) After finding the first space, add 1 to find the next character (J), also found in steps 3 and 4. (7) 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)

	Created by faulkner.j over 10 years ago

A-Levels Excel (Functions) Note on Split text into columns using Functions, created by faulkner.j on 20/07/2013.

Split text into columns using Functions