Restoring Missing Leading Zeros in Google Sheets

When you import data into Google Sheets using copy and paste, unless you format the destination cells as Plain Text, sheets will convert your data into numbers and lose the leading zeros. 0000123 will become 123. This will also happen if you leave the Convert to numbers option ticked during a file import. Read our piece on keeping leading zeros for more on that.

But what if you’ve already done the import, or have a sheet with numbers that need leading zeros adding?

You have 2 options. Use either a formula to fix the data or a custom number format to add them back to the display. We’ll cover how to do both.

Restoring leading zeros with a custom number format

Let’s say you have the following numbers in your sheet.

The numbers were originally 7 digit numbers, and they all had leading zeros to make them exactly 7 digits. There’s an easy way to ensure that no matter what number is in the cell, it has zeros added to make it display with 7 digits. You need to use a custom number format. It is key to note that this method will only change the display of the data, it will not really put the digits back into the values. For that, skip ahead to using a formula.

First, select your data, 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 will add the correct number of leading zeros.

There are many things you can do with custom number formats. You can add as many or as few as you need and you can display them as spaces, or not display them at all. They are called insignificant zeros in the documentation. Here are the relevant 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.

And here are some examples of how you can use those characters in a number format and what they would do:

Number Format	Cell Value	Display
0000000	123	0000123
########	123	123
00?????	123	00 123

As I mentioned already, custom number formats only affect the display of cell values. If you copy a formatted cell that displays 0000123 but has a value of 123, and paste it into another cell, the destination cell may display 0000123 but it will also only contain the value 123.

To get the values to have leading zeros, you need to actually put them back. The quickest way to do that is using a formula.

How to restore leading zeros to data using a formula

If you really need to put leading zeros back into your data, you need to use a formula. We have 3 ways to do this listed below, look at each. You may learn something new, but the clear winner is the TEXT function.

If your data is missing the same number of leading zeros uniformly, then you could simply add them back by concatenating the data value with a string containing zeros. There are 2 ways to do this, using the CONCAT function, or using the string concatenation operator &. Look at rows 3 and 4 in the image example above to see this in action.

As we normally use leading zeros for making fixed width fields, you may have to add a different number of zeros for each row in your data. If that’s the case, then the best option is to use the TEXT function.

TEXT(number, format). TEXT accepts 2 parameters, firstly a number to be formatted, and secondly the custom number format string to format it with. It will return a number, converted into text using the given number format.

We won’t cover number formats in depth here (see custom number formats above for more on that). But to create a number with seven digits that will display any missing digits as zeros, the format is simply “0000000“.

Here’s a step by step to get your leading zeros back by adding a new column, fixing the data and copying it back:

1. Insert a new column in your sheet next to the column with the missing leading zeros
2. Paste the formula =TEXT(A1,"0000000") into the new empty cell next to the top cell with missing leading zeros
3. Edit the formula, replace A1 with the location of the top cell in your sheet with missing leading zeros and 0000000 with your desired number format
4. The top cell in your new column should now have the correct number of leading zeros
5. Copy this formula to all other cells in the new column with missing leading zeros
6. Now select the new column and copy Ctrl+c (⌘+c on Mac)
7. Paste the fixed data back over your original data (paste values only) Ctrl+Shift+v (⌘+Shift+v on Mac)
8. Delete the newly added column
9. DONE!