XL: Parsing Names in a Worksheet Without Data Parse in Excel

ID: Q72573

The information in this article applies to:

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))


  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.



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

Additional query words: 97 2.00 2.01 2.10 2.20 2.21 3.00 4.00 


Keywords          : 
Version           : WINDOWS:2.x,3.x,4.x,5.0,7.0,97
Platform          : 
Issue type        :

Last Reviewed: March 21, 1999