Excel: Parse Command Doesn't Work with Numbers

Last reviewed: November 1, 1994
Article ID: Q70105

SUMMARY

The Microsoft Excel Parse command (on the Data menu) can only parse text entries. Numbers cannot be parsed correctly using this function.

MORE INFORMATION

To parse a number, use one of the following methods:

Method 1

The following functions can be used to parse a number:

          A                         B                               C
1       2345            =VALUE(MID(A1,1,2))     =VALUE(MID(A1,3,2))
2       1454            =VALUE(MID(A2,1,2))     =VALUE(MID(A2,3,2))
3       7654            =VALUE(MID(A3,1,2))     =VALUE(MID(A3,3,2))

These functions will return the following:

          A                         B                               C
1       2345                       23                              45
2       1454                       14                              54
3       7654                       76                              54

Method 2

For this example, place the first number of the column in cell A1.
In cell B1, type the following:
```
      =text(A1,"0")
```
From the Edit menu, choose Fill Down to fill down the cells from cell B1 to match column A.
Select column B and choose Copy from the Edit menu.
Select column B and choose choose Paste Special from the Edit menu (full menus).
Select Values and click OK.
Select column B.
From the Data menu, choose Parse and set the brackets as appropriate for the first cell and click OK.

Note that the result of both of these methods is one or more columns of numbers, not text.

KBCategory: kbother
KBSubcategory:
Additional reference words: noupd

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: November 1, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.