If a 7 digit fixed width field contains a number less than 1 million, that number will normally have leading zeros, i.e. 0001234 (the 000 at the start being the leading zeros). A problem can arise in Google Sheets sometimes because without proper consideration, leading zero numbers can get formatted as regular numbers and 0000124 can get truncated, have a cell value and display of 1234.
You’ll see leading zeros in things like employee numbers, product codes, batch numbers, scientific data. They are common in many data types.
If you want to force a column to show leading zeros, but don’t want to type them in and don’t mind about them missing from the data, you need to use a custom number format to show them.
If you need the data from your copied and pasted data source to keep the leading zeros exactly as they were in the source, you’ll need to consider using a plain text format in the destination cells.
If you are importing data, you will need to ensure you don’t click Convert to numbers during the import process.
Let’s take a deeper look at all these scenarios.
How to add leading zeros with a custom number format
Let’s say you have the following numbers in your sheet.
And you want to display them all as 7 digit numbers and ensure that there are the correct number of zeros added no matter what number you enter. To do this, you need to use a custom number format.
First, select your data, or the column containing your data and then open the Custom number format window. You can get to this from the Format menu or from the number format drop-down button on the toolbar 
.
When you open this screen, you will see the entry box allowing the addition of new custom number formats. Enter 000000 into the box and click apply.
When you do this, you’ll see all your numbers displayed with 7 digits, sheets automatically added the correct number of leading zeros.
There are many things you can do with custom numbers formats. The official help docs call leading zeros insignificant zeros, and you can display them as spaces, or not display them at all. You can also add decimal points and commas to your format if you need them. Here are the five most useful custom number format characters and what they do.
| Character | Description |
|---|---|
| 0 | A digit in the number. An insignificant 0 will appear in the results. |
| # | A digit in the number. An insignificant 0 will NOT appear in the results. |
| ? | A digit in the number. An insignificant 0 will appear as a space in the results. |
| . (period) | Formats numbers with a decimal point. |
| , (comma) | Formats numbers with a thousands separator. |
And here are some examples of how they work. The following formats would display the following data:
| Number Format | Cell Value | Display |
|---|---|---|
0000000 | 123 | 0000123 |
######## | 123 | 123 |
0?????? | 123 | 0 123 |
000.0000 | 123 | 000.0123 |
0.000,000 | 123 | 0,000,123 |
How to keep leading zeros in imported data
Now, let’s explore importing data with leading zeros and keeping the zeros! We have the following data in our example .csv file. A product, a price and a product code.
If we use the Google Sheets import tool found in the menu under File > Import, and select our file sheets shows us the following import modal.
We can choose the Import location, where we want the data to go. The Separator type, in this case automatic detection, this will pickup that it is a csv file and detect this as a comma. And the last option is to ‘Convert text to numbers, dates and formulas‘.
The following result from running the import with the Convert box ticked and un-ticked. As you can see, if we don’t convert, then sheets interprets our leading zero field as text, and the values in the 3rd column get prefixed with a single quote and displayed exactly as they were in the source. So to import leading zero data, and keep the leading zeroes, you need to un-tick that option when importing.
ticked
How to keep leading zeros in copied and pasted data
When you copy data from a source using the Edit > Copy command or the shortcut Ctrl+c (⌘+c on Mac), how that data shows when pasted into a Google sheet, depends on how you paste it, and what number formatting applies to the destination cells.
| Source Data (Text) | Paste | Cell Number Format | Cell Value | Cell Display |
|---|---|---|---|---|
0000123 | Normal paste | Automatic | 123 | |
0000123 | Values only | Automatic | | 123 |
0000123 | Normal paste | 0000000 (Custom) | | 0000123 |
'0000123 | Normal paste | Automatic | '0000123 | 0000123 |
0000123 | Normal paste | Plain Text | 0000123 | 0000123 |
The key things to note here are that when you paste text into sheets from an external application, it will convert the data depending on the format of the destination cells. If the destination cells have a Plain text number format, it will keep your data intact. If they have the default format (Automatic) or another number format, it will convert your data to a number and you will lose the leading zeros.
But you can get them back, using a custom number format or a formula.