How to Keep Leading Zeros in Google Sheets Data

Dave | November 22, 2022 |

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.

A custom number format 0000000 keeping leading zeros
A custom number format leading zeros

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.

Data that needs leading zeros
Our sample data without leading zeros

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 .

Custom number format on the menu

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.

Leading zero custom number format.
Enter 0000000 for a 7 digit number with leading zeros

When you do this, you’ll see all your numbers displayed with 7 digits, sheets automatically added the correct number of leading zeros.

Sample data with leading zeros added using a custom number format
Data with zeros (notice the value in A3 is still a number)

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.

CharacterDescription
0A 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.
Custom number format documentation

And here are some examples of how they work. The following formats would display the following data:

Number FormatCell ValueDisplay
00000001230000123
########123123
0??????1230 123
000.0000123000.0123
0.000,0001230,000,123
Custom number format documentation

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.

CSV data in a .csv file
Our csv data source leading-zeros.csv

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.

Google sheets file import screen
The file import screen, to convert or not?

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.

Imported data with convert text to numbers ticked
Convert text to numbers
ticked
Imported data with convert text to numbers not ticked
Convert text to numbers NOT 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)
PasteCell Number FormatCell ValueCell Display
0000123Normal pasteAutomatic123123
0000123 Values onlyAutomatic123123
0000123Normal paste0000000 (Custom)1230000123
'0000123Normal pasteAutomatic'00001230000123
0000123Normal pastePlain Text00001230000123
Different types of source data and how they paste into sheets

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.

Dave

I'm a Google Product Expert and mainly post on the subject of Google Sheets.

I've been a software engineer for over 20 years. The constant through all that time? Spreadheets.... Even though I can write programs I use them.... a lot. Sometimes there's just no better alternative!

Find out more about me here.

Leave a Comment