Leading zeros in text numbers

  • Thread starter Thread starter Sue_at_VLS
  • Start date Start date
S

Sue_at_VLS

Hi. I've extracted records containing numbers from our
database and dumped the extract into excel. I need all
numbers to be ten digits long with leading zeros. Not
just to display that but to be that.

I've tried changing the data type to text but I have
thousands of records to manipulate that way.

Once the excel sheet works then I need to import it into
Access.

Any suggestions - any time saving suggestions?

Thank you

Sue
 
Could you first copy the extracted data to notepad and save it as a text
file, then just open the text file in excel and
the text import wizard will open, there in step 3 you can select the column
data format, if you select text it will keep
the leading zeros
 
If the data is already in excel, you can insert a helper column and use a
formula:

=text(a1,"0000000000")
(and copy down the range)

Then edit|copy, Edit|paste special|values and throw away the original data.)
 
If I understand what you're trying to do (do you still want the data to be
numbers?), wouldn't it be the easiest if you just formatted your cells with:

0000000000

Right-click the cell, 'Format Cells...', 'Number' tab, 'Custom', enter 10 zeros
in the 'Type:' box as shown above.

This way any number you enter in the cell will be preceeded by zeros if it is
less than 10 on it's own.

Hope this helps.
 
Back
Top