Leading Zeroes

[Thomas]

Hello all,

I know you can use the little ' to turn a numberfield,
with leading zero's into a text field, but I have several
1000 lines where I need this added. Is there a simple way
to add these to cells with number values already in them?

Thanks,
Thomas

 

[Jim Rech]

Select all the cells and do a Format, Cells, Number, Text. Then all
subsequent entries will be text and retain leading zeros.

 

[ThomasN]

Thanks Jim. I had tried that, but it still didn't change.. turned out
the person who emails me these sheets had a custom format on the cells
that made it look like there were some extra zero's there. Kinda hard
to explain, but thanks
Thomas

 

[alex robkin]

if you create the csv file with ="00300",This will display initial zeros!!

include the equal signs surrounded by quotes....

 

[Peo Sjoblom]

So what's the point of doing that?

You can open the csv file using
data>import external data, select all file types and import the column as
text.

 

[David McRitchie]

Sounds like it might be helpful as... You only have a choice
if you change the file extension to .txt instead of .csv

This way another program could generate the .csv file and
it could be opened in Excel without intervention. Of course
it would be nicer if the entire column were text so that new
entries added would be text as well (I don't put too much
reliance on the Extend Formulas and Formats).

 

[Peo Sjoblom]

No, you are wrong. No need to change the file extension.
Try it.. Do data>import external data>import data,
in the dialogue box files of type select all files or type *.*
find the csv file and open it and the text import wizard will
start.

--

Regards,

Peo Sjoblom

Sounds like it might be helpful as... You only have a choice
if you change the file extension to .txt instead of .csv

This way another program could generate the .csv file and
it could be opened in Excel without intervention. Of course
it would be nicer if the entire column were text so that new
entries added would be text as well (I don't put too much
reliance on the Extend Formulas and Formats).
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

You can open the csv file using
data>import external data, select all file types and import the column as
text.

 

[David McRitchie]

Hi Peo,
Thanks for reiterating that, hadn't really paid enough
attention to your answer or to the options on the menu
even though I say one of the best ways to learn Excel
is to go through the menus.

In any case a direct open of a file is easier, and that
includes double-clicking to open so if the formula is
picked up automatically from a .csv file that would
certain make computer generated data from say
mainframes a lot easier to read into Excel.

 

[Peo Sjoblom]

I agree, plus if you use the method I posted you have import it into a
workbook,
I just wanted to show that there is way, albeit less useful.
--

Regards,

Peo Sjoblom

Hi Peo,
Thanks for reiterating that, hadn't really paid enough
attention to your answer or to the options on the menu
even though I say one of the best ways to learn Excel
is to go through the menus.

In any case a direct open of a file is easier, and that
includes double-clicking to open so if the formula is
picked up automatically from a .csv file that would
certain make computer generated data from say
mainframes a lot easier to read into Excel.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

No, you are wrong. No need to change the file extension.
Try it.. Do data>import external data>import data,
in the dialogue box files of type select all files or type *.*
find the csv file and open it and the text import wizard will
start.