Are you wondering how to stop Google Sheets from removing or deleting leading zeros?
For instance, when you input a number like “0123” into Google Sheets, it automatically removes the “0” at the beginning.
This is because, in most cases, numbers are assumed to have no leading zeros.
However, there are a few methods to stop Google Sheets from removing them.
In this tutorial, you will learn how to stop Google Sheets from removing leading zeros, step-by-step.
Also read: (opens in new tab)
How to Highlight a Word in Google Sheets
How to Share Only One Sheet/Tab in Google Sheets
Character Count in Google Sheets [With and Without Spaces]
Group Sheets in Google Sheets
How to Stop Google Sheets from Removing Leading Zeros?
To prevent Google Sheets from removing leading zeros, add an apostrophe (‘) at the start, enter the number, and press Enter. The apostrophe will be removed automatically, keeping the leading zero.
1. Click inside the cell where you want to enter the number with leading zeros
The first step is to open the spreadsheet in Google Sheets.
Next, click inside the cell where you want to enter the number with leading zeros.
2. Type an apostrophe (‘)
Now, type an apostrophe (‘) inside the cell.
Refer to the screenshot above.
3. Enter the number, including the leading zeros
After typing the apostrophe (‘), start typing the number, including any leading zeros.
For example, if you want to enter the number 01234 into the cell, first enter the apostrophe and then the number.
The number in the cell would look like this: ‘01234
4. Press the Enter key
The final step is to press the “Enter” key on your keyboard.
You will notice that the apostrophe at the beginning of the number is automatically removed, while the leading zero(s) are preserved.
This is the simplest method to prevent Google Sheets from removing leading zeros in a number.
Another easy approach is to format the cell as text before entering the number.
To do this, select a cell and click on Format > Number > Plain text.
Now enter the number into the same cell.
After pressing the Enter key, you will notice that the leading zero is not deleted.
This is because Google Sheets now treats the number as text.
Conclusion
- Google Sheets automatically removes leading zeros from numbers by default.
- Adding an apostrophe (‘) at the beginning of a number stops Google Sheets from removing leading zeros.
- Formatting the cell as text before entering the number is another effective method to preserve leading zeros.
- The apostrophe method is fast and simple, but it can become inconvenient when entering numerous numbers with leading zeros.