Microsoft KB Archive/72573

= Parsing Names in a Worksheet Without Data Parse in Excel =

Article ID: 72573

Article Last Modified on 11/16/2006

-

APPLIES TO


 * Microsoft Excel 97 Standard Edition
 * Microsoft Excel 95 Standard Edition
 * Microsoft Excel 5.0 Standard Edition

-



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(&quot; &quot;,A1) C1: =SEARCH(&quot; &quot;,A1,SEARCH(&quot; &quot;,A1)+1) D1: =LEFT(A1,B1-1) E1: =IF(ISERROR(C1),&quot; &quot;,MID(A1,B1+1,C1-B1)) F1: =IF(ISERROR(C1),RIGHT(A1,LEN(A1)-B1),RIGHT(A1,LEN(A1)-C1))


 * 1) The formula in cell B1 returns the position of the first space in the name.
 * 2) The formula in cell C1 returns the position of the second space in the name, if one exists. Otherwise #VALUE! is returned.
 * 3) The first name is placed in cell D1.
 * 4) If a middle initial exists (that is, C1 does not have an error), it is placed in cell E1.
 * 5) 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:


 * 1) Select cells D1:F2.
 * 2) On the Edit menu, click Copy.
 * 3) From the Edit menu, click Paste Special.
 * 4) Click to select Values and click OK.
 * 5) Delete columns A through C.

