Page 19 of 23PREVNEXT

Use formulas to edit, correct, and proofread text

Separating first name with FIND and LEFT functions

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

Imagine that you are preparing a mailing list by separating first and last names from one cell into cells of their own, so that either the first name or the last name can be used in a form letter's salutation.

Unlike order ID numbers, the lengths of the first names vary, so the formula using a specified number of characters wouldn't work here. To separate a first name, you would type this formula in cell B2:

=LEFT(A2,FIND(" ",A2)-1)

The space between the quotation marks tells the FIND function to look for the space in cell A2. (The -1 argument deletes the space itself from the result.) Then the LEFT function uses the result to display the text to the left of that space. That displayed text is the first name.

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

Page 19 of 23PREVNEXT