How to show zero as a first number

**Paul Smith** · 03-08-2008, 06:44 AM

I am a beginner at Excel and would like to know how export numbers into another spreadsheet that begin with zero, at the moment it deletes them, I would like them to remain as they are bar codes, I have to import a thousand of lines at a time, some of which begin with a zero and some do not.

It seems that when I copy and paste, the numbers paste in OK but not the format, so the numbers that begin with a zero effectively lose that zero and the numbers that begin with any other number paste accross unchanged.

I can Format cells, then in custom change type to "0"# but this simply adds a zero to every number in column, if I could import the correct number into Excel to start with it should work. I realise this is no doubt quite straightforward but I just can't work it out, help!

Many thanks

Paul Smith

**MickG** · 03-08-2008, 09:39 AM

Hi, Copy this for future ref, It's on the Microsoft support website.
------------------
Using a custom number format to display leading zeros
In Microsoft Excel, you can use either of two methods for using a custom number format to display leading zeros. Use one method for fixed-length numbers and use the other method for variable-length numbers.
Method 1: Fixed-length Numbers
When you want to display leading zeros for a fixed-length number, create a custom format with the same number of zeros (0) as digits that you want to display. For example, if you want to display a fixed-length number with five digits, create a custom number format with five zeros.

Example 1:

Use the 00000 format to display a number with five digits. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays are listed in the following table.
Typed number Displayed number
--------------------------------
28217 28217
1234 01234
Example 2:

Use the 00000-0000 format when you want the number to appear as five digits followed by a hyphen and four more digits. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays are listed in the following table.
Typed number Displayed number
--------------------------------
8217 00000-8217
82170000 08217-0000
Method 2: Variable-length Numbers
When you want to display leading zeros for variable-length numbers, create a custom format with the same number of zeros (0) (in quotation marks) as leading zeros that you want to display, followed by the number sign (#). For example, if you want to format a variable-length number with three leading zeros, create the following custom number format:
"000"# Example 1:

Use the "0"# format when you want to display one leading zero. When you use this format, the numbers that you type and the numbers that Microsoft Excel displays are listed in the following table.
Typed number Displayed number
--------------------------------
123 0123
45 045
Example 2:

Use the "000"# format when you want to display three leading zeros. When you use this format, the numbers that you type and the numbers that Excel displays are listed in the following table.
Typed number Displayed number
--------------------------------
123 000123
45 00045
--------------------------
Regards Mick

**Paul Smith** · 03-08-2008, 12:52 PM

Thanks very much, appreciated

Paul

**ferdburfle** · 11-17-2010, 01:33 PM

It may have helped Paul, but not me. I've got variable numbers which also have a variable number of zeroes which could precede.

Formatting as text does not help, fixed length does not help, nor does variable. (Because variable has a FIXED number of zeroes attached.) Sigh....