Formatting Problem

[John Calder]

Hi

I run Excel 2k

I have a column of numbers going from 001 through to 700

The numbers have been formatted as custom 000 so as to accomodate the
numbers 001 through to 099.

They all look as they should on the worksheet.

I use this column of data in a pivit table.

I found that the numbers 001 & 002 show up in the pivot table as 1 & 2.

I looked in the worksheet and noticed that when I select the cell (A1) with
the number 001, the edit bar shows it as 1. This also applies for the cell A2
where the number on the worksheet is 002 but in the edit bar it shows as 2.

All the other numbers show the same in both the worksheet and the edit bar (
003, 004, 005, etc etc)

I have tried reformatting these 2 cells but it still shows in the edit bar
as 1 and 2.

I have tried copying over these 2 cells with numbers that work ok (ie; 003,
004 etc) and then editing these number but still they show as 1 and 2.

Can anyone tell me how to get these 2 cells to hold the formatting of custom
000


Thanks

John

 

[Roger Govier]

Hi John

In the Pivot Table>double click the Field name>Number>Format and set as 000,
the same as your source data.
Right click on the PT>Table Options>uncheck Autoformat>check>Preserve
Formatting.

 

[AltaEgo]

An inverted comma before the 0's will force the value to be displayed as
text. The inverted comma will not be visible in cells. You will see 001 not
'001.

Excel can still perform mathematical calculations with numbers formatted as
text in this manner. The resulting calculation results in a number format
Example: 3 * '002 will return 6 (not '006).

The text-formatted numbers will increment in sequence using the fill handle
(e.g. enter '001 in A1, '002 in A2 and use the fill handle to drag the
values down).