How to parse names in a Excel for Mac worksheet without data text to columns (303384)

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(" ",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 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.