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:
- Insert two rows above the data.
- In the first row, in column B, type the number of characters
you want in each partition of the parse going across.
- 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).
- 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:
- Select the range B2:E6 (or a larger range if you need to).
- From the Edit menu, choose Fill Down.
- 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:
- Select B2:E6 (or a larger range if applicable)
- From the Edit menu choose Copy.
- Select the range you selected in step 1.
- From the Edit menu, choose Paste Special and select the Values option.
This pastes the values over the formulas.
- Delete column A and rows 1 and 2.
|