Table of Contents
This tip is about the how to Keep Leading Zeros from Being Removed in Google Sheets. So read this free guide, How to Keep Leading Zeros from Being Removed in Google Sheets step by step. If you have query related to same article you may contact us.
How to Keep Leading Zeros from Being Removed in Google Sheets – Guide
When working with confidential numbers or text, such as ID numbers, security numbers, or postal codes, it is critical to keep leading zeros. To maintain a specific format, we may need to add leading zeros in some circumstances. When working with Excel and Google Sheets, we can change the format or use functions like TEXT, REPT and VALUE to add or delete leading zeros. Leading zeros are automatically eliminated when entering numbers in Google Sheets. This can be difficult when typing zip codes, phone numbers or IDs in a cell. Here it is how to preserve your leading zeros intact.
Add apostrophe (‘) before the number
This works well when you need to manually enter data into cells.
All you have to do is first type an apostrophe symbol (‘) and then type the number. So if you want the value in the cell to be 000123, you would enter ‘000123. Adding an apostrophe converts the entered number to text and leading zeros do not disappear. When you add an apostrophe to the beginning of the number, you will notice that it aligns to the left. This is because Google Sheets now considers it as text (by default, text is left-aligned and numbers are right-aligned in Google Sheets).
Note: Although adding an apostrophe changes the number to text, you can still use it in formulas and calculations as if it were a number.
Change cell format to Text
Another quick way to keep leading zeros is to change the cell format so that whatever you enter is considered text. Once you’ve done that, you can type a number of leading zeros (or copy and paste those values into those cells) and you won’t lose those numbers at the beginning. Below are steps to change the cell format to text:
Note that you must do this before inserting or copying/pasting numbers into cells. This won’t work if you already have numbers that used to have leading zeros and Google spreadsheets have already removed them. Although these cells have been formatted as text, once you have the numbers in these cells, you can also use them in calculations if you want.
Important: When copying and pasting numbers with leading zeros into cells formatted as text, be sure to paste those numbers as values. This will ensure that the text formatting is intact and works as expected.
Create a custom format with fixed number of digits
In Google Sheets, you can create a custom format where you force a cell to display a specific number of digits. For example, you can format cells so that they always show 6 digits. And if there are fewer digits, the custom format will show leading zeros to ensure that the result final have six digits. So if you enter the number 123 in the cell where this custom formatting has been applied, it will automatically change to 000123
Below are steps to apply this custom formatting in Google Sheets:
Now when you enter any number into these selected cells, it shows at least six digits. If the number is less than six digits, leading zeros will be added. And if the number has more than six digits, nothing will change. This will only affect the numbers. If you enter any text value in these cells, it will be displayed as expected.
Note: When you change a cell’s custom number format, it only changes the way the number is displayed. This does not change the underlying value. So if you enter 123, it will remain 123 in the cell, but it will show up as 000123.
So these are three simple ways you can keep the leading zeros in Google Sheets. Hope you found it tutorial useful.
Final note
I hope you like the guide How to Keep Leading Zeros from Being Removed in Google Sheets. In case if you have any query regards this article you may ask us. Also, please share your love by sharing this article with your friends.