Page 20 of 23PREVNEXT

Use formulas to edit, correct, and proofread text

Separating last name with FIND and RIGHT functions

Callout 1 Formula as typed.
Callout 2 Formula in the worksheet.
Callout 3 Formula result in the worksheet.

When the lengths of the last names vary, how does Excel recognize a last name? By the fact that it follows a space. To separate the last name, you would type this formula in cell B2:

=RIGHT(A2,LEN(A2)-FIND(" ",A2))

The formula uses the LEN function to find the length of the text in cell A2: 13 characters. Then the FIND function locates the space (" ") in cell A2. Next, Excel subtracts the position of the space (6 characters from the left) from the length of the text in cell A2. The RIGHT function uses the result of that subtraction as a character count to display everything to the right of the space. So 13-6 equals "Davolio" (the last 7 characters in the cell).

To complete the group of names, you would copy the formula down the column and then delete the formulas, leaving only the results.

Page 20 of 23PREVNEXT