custom number formats

[Guest]

I have a table of data which I need to get into Access in a certain format.
One column of data is giving me trouble.

This column of data is formatted with a custom format, 0000000000. (10
zeroes). I want to change the format of these cells to text, so that if the
text string shown in the cell is, say, 0000037009, then the value shown in
the formula bar when that cell is selected shows 0000037009.

Sounds simple, right? Well, when I select this cell and apply the text
format to it, the leading zeroes are chopped off. It becomes: 37009.

Any thoughts?

 

[Ron Rosenfeld]

I have a table of data which I need to get into Access in a certain format.
One column of data is giving me trouble.

This column of data is formatted with a custom format, 0000000000. (10
zeroes). I want to change the format of these cells to text, so that if the
text string shown in the cell is, say, 0000037009, then the value shown in
the formula bar when that cell is selected shows 0000037009.

Sounds simple, right? Well, when I select this cell and apply the text
format to it, the leading zeroes are chopped off. It becomes: 37009.

Any thoughts?

If your data is in column A, for example, in a "helper" column, enter the
formula: =TEXT(A1,"0000000000") and copy/drag down.

Then
select the Helper column range
Edit/Copy
select A1
Edit/Paste Special Values
delete the helper column.


--ron

 

[Guest]

If you have formatted a cell containing 37009 as General 0000000000 then the
leading zeros as only a visual effect in the cell, not in the formula bar.

if A1 contains 37009
in B1 put:
=TEXT(A1,"0000000000")
copy B1 and paste/special/as value in C1 to see:
0000000560
Now the leading zeros are real characters in a character string.

 

[JERRYDES]

Select the column.
Control 1
Format Cells
Custom "0000000000"@

Good luck
jerrydes