Microsoft KB Archive/303384

= How to parse names in a Excel for Mac worksheet without data text to columns =

Article ID: 303384

Article Last Modified on 1/31/2007

-

APPLIES TO


 * Microsoft Excel 2004 for Mac
 * Microsoft Excel X for Mac
 * Microsoft Excel 2001 for Mac
 * Microsoft Excel 98 for Macintosh

-



This article was previously published under Q303384





SUMMARY
You have a data sheet with a Full Name (for example, Andrew P. Gray) in a single cell, and you want to convert it so that the First Name, Middle Initial, and Last Name are in separate cells. Microsoft Excel for Mac can parse data into separate cells by using formulas.



MORE INFORMATION
The following example explains how to parse a full name into First Name, Middle Initial, and Last Name in separate columns. The Middle Initial column is skipped if no Middle Initial exists.

Example
Type the following names into the specified cells:   A1:  Andrew P. Gray A2: Tom Johnston Type the following formulas into the specified 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))
 * 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 then click Fill Down on the Edit menu. Cells D1:F2 will look as follows:

  D1: Andrew      E1: P.         F1: Gray D2: Tom        E2:            F2: Johnston You can use this type of formula for parsing other types of data such as street addresses, or city/state/postal code entries.

After parsing the data, you may want to remove all the formulas and just keep the final parsed data. To do this, follow these steps:
 * 1) Select cells D1:F2.
 * 2) On the Edit menu, click Copy.
 * 3) On the Edit menu, click Paste Special.
 * 4) Click to select Values and then click OK.
 * 5) Delete columns A through C.

Another way to parse records is to write a VBA macro, if you are familiar with writing code in Visual Basic for Applications. There may be circumstances in which you can use the Text to Columns command on the Data menu. However, this may not place the information exactly where you want it.

