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:

- Insert a new column in your sheet next to the column with the missing leading zeros
- Paste the formula
`=TEXT(`

into the new empty cell next to the top cell with missing leading zeros**A1**,"**0000000**") - Edit the formula, replace
with the location of the top cell in your sheet with missing leading zeros and`A1`

with your desired number format`0000000`

- The top cell in your new column should now have the correct number of leading zeros
- Copy this formula to all other cells in the new column with missing leading zeros
- Now select the new column and copy
`Ctrl`+

(⌘+c on Mac)`c` - Paste the fixed data back over your original data (paste values only)
`Ctrl`+`Shift`+`v`(⌘+Shift+v on Mac) - Delete the newly added column
- DONE!