I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

You can change the format of cell as "Text".
steps:- right click on cell/cells format cells Number Text Ok

Regards
Vikas Kumar.

"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

If you go into Format - Cell and on the Number tab scroll down the list
until you see 'custom'. In the text box you can add the appropriate amount of
zeros to bulk up the number

"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

If you need the zeros to actually exist, versus just displaying via a custom
format, there may be options for you, but you'll need to describe the source
file and explain how you're currently getting the data into the workbook
(importing, copy/paste, opening a CSV file, etc)

***********
Regards,
Ron

XL2002, WinXP


"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

But of course you need to do this *before* you put the numbers into the
cells.
--
David Biddulph

"Vikas Kumar" wrote in message
...
You can change the format of cell as "Text".
steps:- right click on cell/cells format cells Number Text Ok

"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of
the
cells for each line has a ten digit number, the majority of which start
with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop
excel
from doing this? I have tried formating the cell to text and using find
and
replace, and I have tried copying the column into word, changing the
numbers
and pasting back into excel, but it still loses the 0.

Thanks Ron. I do need the zeros to exist. I'm importing them and they arrive
in excel as a CSV file. Unfortunately I can't change the format of the cells
before I put the numbers in.
"Ron Coderre" wrote:

If you need the zeros to actually exist, versus just displaying via a custom
format, there may be options for you, but you'll need to describe the source
file and explain how you're currently getting the data into the workbook
(importing, copy/paste, opening a CSV file, etc)

***********
Regards,
Ron

XL2002, WinXP


"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

Hi

Adjust the file extension from .csv to .txt before you open it up in
Excel - this will make Excel open the Text Import Wizard and using this
you can tell Excel that the column containing the Account Number
information is to be treated as text (ie no truncation of leading
zeroes). You can rename the file in Windows Explorer, or you could
write a script in VBA to locate and amend the extension prior to
opening.

Richard


modlang wrote:
Thanks Ron. I do need the zeros to exist. I'm importing them and they arrive
in excel as a CSV file. Unfortunately I can't change the format of the cells
before I put the numbers in.
"Ron Coderre" wrote:

If you need the zeros to actually exist, versus just displaying via a custom
format, there may be options for you, but you'll need to describe the source
file and explain how you're currently getting the data into the workbook
(importing, copy/paste, opening a CSV file, etc)

***********
Regards,
Ron

XL2002, WinXP


"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.

Richard - you are a star. That has worked perfectly - my life is now complete.

Happy Christmas,

Damian.

"RichardSchollar" wrote:

Hi

Adjust the file extension from .csv to .txt before you open it up in
Excel - this will make Excel open the Text Import Wizard and using this
you can tell Excel that the column containing the Account Number
information is to be treated as text (ie no truncation of leading
zeroes). You can rename the file in Windows Explorer, or you could
write a script in VBA to locate and amend the extension prior to
opening.

Richard


modlang wrote:
Thanks Ron. I do need the zeros to exist. I'm importing them and they arrive
in excel as a CSV file. Unfortunately I can't change the format of the cells
before I put the numbers in.
"Ron Coderre" wrote:

If you need the zeros to actually exist, versus just displaying via a custom
format, there may be options for you, but you'll need to describe the source
file and explain how you're currently getting the data into the workbook
(importing, copy/paste, opening a CSV file, etc)

***********
Regards,
Ron

XL2002, WinXP


"modlang" wrote:

I've exported a large amount of info from an accounts package, and one of the
cells for each line has a ten digit number, the majority of which start with
0(zero). When i open this spreadsheet to look at it all the zeros at the
beginning of these numbers disappear. How do I put them back, or stop excel
from doing this? I have tried formating the cell to text and using find and
replace, and I have tried copying the column into word, changing the numbers
and pasting back into excel, but it still loses the 0.