# Microsoft Office Application Help - Excel Help forum > Excel General >  >  How to stop Excel deleting leading zeros

## ndong

Hi 

How do i stop Excel 2007 from deleting leading zeros permanently and not having to change the number type.

eg. 00123 -> 123

Also is there a way to stop Excel from change long numbers eg. 321312321303 to 3.21E+10.

Thanks.

----------


## carddards

To stop Excel from removing the zeros, you can convert the cell into TEXT format.

----------


## Craig 1985

put ' in front of the numbers e.g '000123

Hope this helps

----------


## ndong

Hi guys,

Thanks for the quick response.

I want excel to do it permanently not only when i convert to text or put ' infront. 

I have to copy and paste a large amount of numbers into spreadsheets and don't want excel to change the numbers in any way.

Is there an option to stop autoformatting numbers?

----------


## Shama

How are you C & P-ing the data?
Are you able to pre-format the cell range first?
You can also try creating a macro or VBA code on a cell change.

----------


## Cheeky Charlie

set the cell format to:
000000

HTH

----------


## whatev20

I have the EXACT same problem
Even went the extra mile and tried to trick excel with REPLACE option
(123=>00123, formatted as TEXT) but excel still fails

tried (123=>x00123> then replace X with nothing, but excel still deletes the leading zeros

all the table content is formatted as text, but still when copy/pasting excel deletes the leading zeroes, even if you PRE-FORMAT both the clipboard AND the empty table in which you paste

its EXTREMELY annoying and irritating problem
I cant even begin to imagine the jugheads that developed excel  :Mad: 

please give me ideas, this would save me a ton of work

p.s. the "0000" format thing also doesn't work

----------


## ChrisH106

Agreed jug heads!, you can use the approach you suggest x00123 using a formula like Mid(A1,2,100),
IE if the data is in column A put the formula in B drag down then copy and paste special as values and it will keep the leading zeros. Also depending where the data is coming from you could use a Text Qualified field in a text file and text to columns (setting the appropriate column to text).

I find it so infuriating that excel 'knows' better than I do what format my data should be! and that it doesn't respect my data!

If anyone has a more permanent solution please share with the class

----------

