custom number formats


G

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?
 
Ad

Advertisements

R

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
 
G

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.
 
Ad

Advertisements

J

JERRYDES

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

Good luck
jerrydes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top