Parsing Names in a Worksheet Without Data Parse in Excel (72573)
The information in this article applies to:
- Microsoft Excel 97 for Windows
- Microsoft Excel for Windows 95
- Microsoft Excel for Windows 5.0
This article was previously published under Q72573 SUMMARY
You can parse data into separate cells by entering formulas into the first
row of a worksheet and filling those formulas down. In those cases where
using the Data Parse command isn't working properly, and you do not want
to write a macro, this may be preferable.
MORE INFORMATION
The following example will accept names and parse the names into three
columns (first name, middle initial, last name), skipping the middle
initial column if no middle initial exists.
NOTE: The Microsoft Excel version 3.0 and 4.0 macro add-in, FLATFILE.XLA,
Data Smart Parse command will parse in a similar way, except that it does
not skip any columns.
Example
Enter the following names into the appropriate cells:
A1: Mary J. Smith
A2: Joshua Johnston
Enter the following formulas into the appropriate cells:
B1: =SEARCH(" ",A1)
C1: =SEARCH(" ",A1,SEARCH(" ",A1)+1)
D1: =LEFT(A1,B1-1)
E1: =IF(ISERROR(C1)," ",MID(A1,B1+1,C1-B1))
F1: =IF(ISERROR(C1),RIGHT(A1,LEN(A1)-B1),RIGHT(A1,LEN(A1)-C1))
- The formula in cell B1 returns the position of the first space in the
name.
- The formula in cell C1 returns the position of the second space in
the name, if one exists. Otherwise #VALUE! is returned.
- The first name is placed in cell D1.
- If a middle initial exists (that is, C1 does not have an error), it
is placed in cell E1.
- The last name is placed in cell F1.
Select cells B1:F2 and click Fill Down on the Edit menu. Cells D1:F2
will look as follows:
D1: Mary E1: J. F1: Smith
D2: Joshua E2: F2: Johnston
This works well and easily if the customer doesn't want to use a macro,
or if the data doesn't parse easily. After parsing the data this way, you
will want to remove all the formulas by following these steps:
- Select cells D1:F2.
- On the Edit menu, click Copy.
- From the Edit menu, click Paste Special.
- Click to select Values and click OK.
- Delete columns A through C.
REFERENCES
For more information about the Search function, click Contents And Index
on the Help menu, click the Index tab in Excel 97 Help, type the following
text
SEARCH worksheet function
and then double-click the selected text to go to the "SEARCH" topic. If
you are unable to find the information you need, ask the Office Assistant.
"Function Reference," version 4.0, pages 248-253, 274-274, 373-374
"Function Reference," version 3.0, pages 135-138, 151-152, 208-209
Modification Type: | Minor | Last Reviewed: | 8/15/2005 |
---|
Keywords: | KB72573 |
---|
|