Google Sheets deletes leading zeros by default, but you can preserve them by formatting cells as text before you enter the data

When you type a number that starts with zero — like 01234 or 00567 — into a Google Sheet, the application treats it as a mathematical value and removes the leading zeros automatically. This happens because Google Sheets interprets anything in a cell as a number unless you tell it otherwise. The zero disappears the moment you press Enter, and you cannot get it back by formatting the cell afterward.

The fix is to format the cell as text before you type anything into it. Once a cell is formatted as text, Google Sheets will keep whatever you type exactly as you type it — zeros and all. This works for ZIP codes, product codes, employee IDs, phone numbers with leading zeros, and any other data where the leading zero matters.

Key Takeaways

  • Format cells as text before entering data, not after — formatting an already-entered number will not bring back the deleted zeros.
  • Right-click the cell or column, select Format Cells, choose Text, and then type your number with the leading zero.
  • An apostrophe before the number (like '01234) also preserves leading zeros, but formatting the cell is cleaner and easier to remember.
  • If you have already entered numbers without leading zeros, you will need to re-enter them after formatting the cells as text.

Format a single cell as text

Right-click on the cell where you want to enter a number with a leading zero. A menu will appear. Click Format cells. In the window that opens, look for the format options on the left side. Click Text. Then click Done.

Now type your number into that cell — for example, 01234 — and press Enter. The leading zero will stay. You can repeat this for each cell that needs to preserve a leading zero, or you can format multiple cells at once by selecting them all before you right-click.

Format an entire column as text

If you are entering many numbers with leading zeros in the same column, format the whole column at once. Click the column header (the letter at the top) to select the entire column. Right-click anywhere in the selected column and choose Format cells. Select Text and click Done.

Now every cell in that column will keep leading zeros. This is faster than formatting cells one at a time, especially if you are working with a long list of ZIP codes, product IDs, or similar data.

Use an apostrophe as a quick alternative

If you forget to format the cell first, you can type an apostrophe directly before the number: '01234. The apostrophe tells Google Sheets to treat what follows as text, not a number. When you press Enter, the apostrophe will not appear in the cell — only the number 01234 will show — but the leading zero will be preserved.

This method works in a pinch, but it is easy to forget the apostrophe, and if you copy the data elsewhere, the apostrophe might reappear. Formatting the cell as text first is more reliable for data you plan to use repeatedly or share with others.

What to do if you already entered numbers without leading zeros

If you have already typed numbers into cells and the leading zeros disappeared, formatting those cells as text will not bring the zeros back. The data is already gone. You will need to re-enter the numbers after formatting the cells as text.

Select the cells that need fixing, format them as text, and then type the correct values with leading zeros. If you have a large amount of data to fix, consider using a formula to add the leading zeros back — for example, the TEXT function can reformat numbers with a pattern like =TEXT(A1,"00000") to add leading zeros to a five-digit code. Paste the results as values into a new column, then delete the original column.

Why Google Sheets removes leading zeros in the first place

Google Sheets follows the standard behavior of spreadsheet software: it assumes anything that looks like a number is a number, and numbers do not have leading zeros in mathematics. The number 01234 and 1234 are the same value, so the application strips the leading zero to keep the data "clean."

This makes sense for most data — prices, quantities, ages — where leading zeros are meaningless. But for codes and identifiers where the leading zero is part of the actual data, you have to override this behavior by telling Google Sheets the cell contains text, not a number.

Frequently Asked Questions

Will formatting as text affect how I can use the numbers in formulas?

Yes. If a cell is formatted as text, Google Sheets will treat it as text in formulas, not as a number. If you need to do math with the value, you may need to convert it back to a number first using the VALUE function. For data like ZIP codes or product codes where you do not need to calculate, formatting as text is fine.

Can I format cells as text after I paste data in?

Formatting as text after pasting will not restore deleted leading zeros — they are already gone. You need to format first, then enter or paste the data. If you have already pasted, you will need to delete the data, format the cells as text, and paste again.

Does the apostrophe method work in all spreadsheet programs?

The apostrophe method works in Google Sheets, Microsoft Excel, and most other spreadsheet software. However, the apostrophe may appear when you export or copy the data, so formatting as text is usually the better choice for long-term use.

What if I want to keep leading zeros but still do math with the numbers?

Store the number in a text cell to preserve the leading zero, and create a separate column with the VALUE function to convert it to a number for calculations. For example, if A1 contains '01234 as text, use =VALUE(A1) in column B to get the numeric value 1234 for formulas.