Excel: Data Parse Command Does Not Parse Numbers

Last reviewed: November 2, 1994
Article ID: Q50293
The information in this article applies to:
  • Microsoft Excel for Windows, versions 2.x, 3.0, and 4.0
  • Microsoft Excel for OS/2, versions 2.2 and 3.0

Summary:

In Microsoft Excel you cannot parse numbers with the Data Parse command. Data Parse is intended to parse text only. When you attempt to parse a number with the Data Parse command, the data parse line will be blank.

More Information:

To get the sections of the number you want, use the MID text function. To perform a manual parse, follow the steps below to create a formula that uses the MID function:

  1. Insert two rows above the data.

  2. In the first row, in column B, type the number of characters you want in each partition of the parse going across.

  3. In the row below, in column B, create a running total of the number of characters in the previous column and the previous running total.

        Note: This running total should start with cell A1 (see the formulas
        below).
    

  4. In cell B3, enter the formula that can be filled down and filled across (see below).

          A1: # of Chars: B1: 3                 C1: 4      D1: 3
          A2: run total:  B2: 1                 C2: =B1+B2 D2: =C1+C2
          A3: 1234567891  B3: =MID($A3,B$2,B$1) C3:        D3:
          A4: 1235465767  B4:                   C4:        D4:
          A5: 1234567865  B5:                   C5:        D5:
          A6: 5434232123  B6:                   C6:        D6:
    
    

  5. Select the range B2:E6 (or a larger range if you need to).

  6. From the Edit menu, choose Fill Down.

  7. From the Edit menu, choose Fill Right.

This formula will adjust according to the position of the dollar signs in the formula, so be sure the dollar signs are in the correct place.

In the example the first three numbers are placed in column B, the next four numbers in column C, and the next three numbers in column D. It yields the following values:

   A1: # of Chars: B1: 3                 C1: 4      D1: 3
   A2: run total:  B2: 1                 C2: 4      D2: 8
   A3: 1234567891  B3: 123               C3: 4567   D3: 891
   A4: 1235465767  B4: 123               C4: 5465   D4: 767
   A5: 1234567865  B5: 123               C5: 4567   D5: 865
   A6: 5434232123  B6: 543               C6: 4232   D6: 123

To permanently store the data only:

  1. Select B2:E6 (or a larger range if applicable)

  2. From the Edit menu choose Copy.

  3. Select the range you selected in step 1.

  4. From the Edit menu, choose Paste Special and select the Values option. This pastes the values over the formulas.

  5. Delete column A and rows 1 and 2.


KBCategory: kbusage
KBSubcategory:

Additional words: 2.0 2.00 2.01 2.1 2.10 2.2 2.20 2.21 3.0 3.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: November 2, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.