Parsing Names in a Worksheet Without Data Parse in Excel

 Last reviewed: April 29, 1997
Article ID: Q72573
The information in this article applies to:
  • Microsoft Excel for Windows, version 2.x, 3.x, 4.x, 5.0
  • Microsoft Excel for Windows NT, version 5.0
  • Microsoft Excel for Windows 95, version 7.0
  • Microsoft Excel 97 for Windows

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 desirable.

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 choose Fill Down from 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 doing the following:

    1. Select cells D1:F2.

    2. From the Edit menu, choose Copy.

    3. From the Edit menu, choose Paste Special.

    4. Select Values and choose OK.

    5. Delete columns A through C.

    REFERENCES

    For more information about the Search function, 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.

    "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 7.00 2.00 2.01 2.10 2.20 2.21 3.00 4.00 5.00

    THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

    Last reviewed: April 29, 1997
    © 1998 Microsoft Corporation. All rights reserved. Terms of Use.