Google Sheets treats leading zeros as formatting, not data

Google Sheets automatically removes leading zeros from cells because it interprets them as unnecessary formatting on a number. When you type 0123 into a cell, Sheets converts it to 123. This happens whether you're entering product codes, ZIP codes, account numbers, or any other data that needs to start with zero. The spreadsheet is trying to be helpful — it assumes you don't want the zero — but it deletes information you actually need.

The fix depends on what you're doing with the data. If you're just storing and displaying the numbers, formatting the cell as text before you enter the data is the fastest solution. If you need to use the numbers in calculations or import them from another source, you'll need a different approach. The method you choose affects how the data behaves later, so it's worth understanding which one fits your situation.

Key Takeaways

  • Format cells as text before entering data with leading zeros, or the zeros will disappear when ready.
  • An apostrophe at the start of an entry ('0123) forces Sheets to treat it as text and keeps the zero, though the apostrophe itself won't display.
  • If data is already entered without zeros, you cannot recover them — you'll need to re-enter the values or import them correctly from the source.
  • Imported data loses leading zeros unless the source file is formatted as text before import, or you use a formula to add them back.
  • Calculations and sorting work differently on text-formatted numbers than on actual numbers, so choose the format that matches how you'll use the data.

Format the cell as text before entering data

This is the most reliable method if you're starting fresh. Right-click the cell or range where you want to enter numbers with leading zeros. Select Format cells from the menu. In the dialog that opens, click the Number tab, then choose Text from the category list on the left. Click OK. Now type your data into those cells — the leading zeros will stay.

You can format an entire column this way before you start entering data. Click the column header letter to select the whole column, then follow the same steps. This prevents the problem before it starts and is faster than fixing individual cells later. The downside is that once cells are formatted as text, any math you do with those numbers will treat them as text, not values — so if you need to add, subtract, or sort numerically, this method has limits.

Use an apostrophe to force text format on individual entries

If you've already started entering data and only some cells need leading zeros, you can fix them one at a time. Click the cell, type an apostrophe (') before the number, then type the rest. For example, type '0123. The apostrophe tells Sheets to treat the entry as text, and it won't display in the cell — you'll only see 0123. Press Enter and the zero stays.

This method works for fixing a handful of cells, but it's slow if you have dozens or hundreds of entries. You also have to remember to add the apostrophe each time, which is straightforward to forget. The apostrophe approach is best for occasional corrections or when you're working with data that's mostly fine and only needs a few fixes.

Recover lost zeros with a formula

If you've already entered numbers and Sheets deleted the leading zeros, you can't get them back by formatting — the data is gone. But you can add them back with a formula if you know how many digits the original number should have. Use the TEXT function to pad the number with zeros on the left.

In an empty column, type this formula: =TEXT(A1,"0000") — replace A1 with the cell containing your number, and replace the four zeros with however many digits your number should have. If your ZIP codes should be five digits, use five zeros. If your product codes should be eight digits, use eight zeros. Press Enter, then copy the formula down for all rows. The result will be text with leading zeros. Copy the results, paste them as values into a new column, then delete the original column if you don't need it anymore.

This method only works if you know the correct length. If some numbers should be five digits and others should be six, you'll need a more complex formula or to fix them manually. It also converts the numbers to text, so sorting and calculations will behave differently than they would with actual numbers.

Import data correctly to preserve leading zeros

If you're bringing data from another source — a CSV file, an Excel spreadsheet, or a database export — the leading zeros often disappear during import. To prevent this, format the source file as text before you import it. In Excel or a CSV editor, select the column that contains numbers with leading zeros, format it as text, save the file, then import it into Sheets. The zeros will come through intact.

If you've already imported the data and lost the zeros, you can delete the column and re-import it with the correct formatting. Alternatively, use the TEXT formula method described above to add them back. Some data sources let you export as a different format — for example, exporting as a text file instead of a spreadsheet — which sometimes preserves leading zeros better. Check your source system's export options if the standard import doesn't work.

Understand how text-formatted numbers behave differently

Once you format cells as text or use an apostrophe, the numbers behave like text, not like numbers. Sorting will arrange them alphabetically instead of numerically — 0100 comes before 020 in alphabetical order, even though 20 is smaller than 100. Calculations won't work: if you try to add a text-formatted number to another number, Sheets will return an error or treat it as zero. Filtering and searching still work, but some functions that expect numbers will fail.

This matters only if you plan to do math with the data or sort it numerically. If you're just storing and displaying codes, ZIP codes, or account numbers that happen to start with zero, text formatting is fine and actually the right choice. Think about what you'll do with the data before you decide which method to use. If you need both — to keep the leading zeros and do calculations — you may need two columns: one formatted as text for display, and one with the numbers as actual values for math.

Frequently Asked Questions

Why does Google Sheets delete my leading zeros?

Sheets interprets leading zeros as unnecessary formatting on a number. The spreadsheet assumes you want the number 123, not 0123, so it removes what it sees as extra zeros. This is automatic behavior that happens the moment you enter the data, unless you've already formatted the cell as text or started the entry with an apostrophe.

Can I undo a deleted zero after I've entered the data?

No. Once Sheets deletes the zero, it's gone — formatting the cell as text afterward won't bring it back. You'll need to re-enter the data with the correct formatting in place first, or use a formula to add the zeros back if you know the correct length.

What's the difference between using an apostrophe and formatting as text?

Both force Sheets to treat the entry as text and preserve leading zeros. The apostrophe is faster for individual cells, while formatting is better for entire columns or ranges. The apostrophe doesn't display, but formatting the cell doesn't require any special character — you just type the number normally.

Will text-formatted numbers work in calculations?

No. Text-formatted numbers won't add, subtract, or multiply correctly. If you need to do math with the data, keep it as actual numbers in one column and use a formula to create a text version with leading zeros in another column for display.

How do I fix leading zeros in data I've already imported?

Use the TEXT formula to add them back: =TEXT(A1,"0000"), replacing the zeros with the correct number of digits. Copy the results, paste them as values into a new column, then delete the original. Alternatively, delete the imported data and re-import it after formatting the source file as text first.