Table of Contents
This tip is about the how to Add Leading Zeros in Google Sheets. So read this free guide, How to Add Leading Zeros in Google Sheets. If you have query related to same article you may contact us.
How to Add Leading Zeros in Google Sheets – Guide
If you’re trying to write a number starting with zero (0) in Excel or Google Sheets, you won’t be able to because of the default settings. In that case, you should follow this tutorial to insert zero before a number in Google Sheets and Microsoft Excel. No third-party add-ons are needed to do this.
Microsoft Excel and Google Sheets are two lifesaving tools that help you create a spreadsheet with different data. Like other devices, they also have some limitations or issues. For example, users cannot type a number like this – 00123 or 000012 or anything like that. It doesn’t matter which row or column you choose; You cannot enter these numbers into any tool. However, sometimes you may need to do this while typing in the zip code, rating, or something else. For that you must follow these tricks mentioned by us here. There are basically two different ways to enter zero before a number in Google Sheets and Microsoft Excel.
Adding leading zeros using text format in Google Sheets
There are two methods (add/prefix zeros manually).
How to Add leading zeros without losing number format in Google Sheets
To make leading zeros appear automatically as you type (only after pressing the Enter key) in Google Sheets, follow the four steps below.
steps to follow
Leading zeros will be added to the selected range. Keeps the numeric format. You can verify this by summing the numbers in the cells where the above formatting has been applied.
How to Add a hyphen to separate every four digits?
You may have seen credit card (CC) numbers automatically hyphenated as you type them into online forms. We can do this using numeric formatting as above in Google Sheets. In the third step above, use the following custom number formatting instead of 6 zeros.
0000-0000-0000-0000
Enter the number first. Then format the cell or range as above.
The downside of number formatting
There is a downside to this method. As we are using the number format, the formatting will only apply to the numbers entered. You cannot add leading 0s to text strings or alphanumeric characters as above. In that case, format the range to text and manually enter the zeros as mentioned at the beginning, or follow the formula approach below.
Pad Zeros for existing text using Rept, Len and ampersands
Suppose you already have strings of text or numbers in a column. You want to add 0s in front of them. Let’s see how we can do this using the REPT, LEN and ampersand (&) combination formula in Google Sheets.
First, decide the total length of the string or the number of digits. I’m considering the string length as 10 or 10 digits. The range to fill 0s for numbers/text string/alphanumeric characters is A2:A11. Enter this formula in cell B2 and copy it.
=rept(0,10-len(A2))&A2
Alternatively, we can rewrite the above formula as an array formula to pad a certain number of zeros in front of existing text strings or numbers. In cell B2, type this array formula.
=ArrayFormula(rept(0,10-len(A2:A11))&A2:A11)
If you happen to see the #REF! error, be sure to exclude any values in the range B3:B because array formulas need blank (array) cells to fill the results.
How can we remove leading zeros in Google Sheets?
If leading zeros in Google Sheets are added using the custom number format as above, follow the steps below.
Removing zeros added using custom number format (by undoing formatting)
Select the range/array that contains the formatted numbers. Then go to Format > Number and select “Automatic”. This will clear formatting (leading zeros) from the selected range. How about removing leading zeros in a formatted range of text?
Removing zeros added using text format (using Regex)
At the beginning, I explained two methods of formatting text to get leading zeros. It doesn’t matter which method you use to get leading zeros. You cannot unformat it.
The only solution is to use a formula in another column to remove the leading zeros. I’m using the popular REGEXEXTRACT text function here. The Regex formula for the range A2:A:
=ArrayFormula(REGEXEXTRACT(“0″&A2:A,”0+(.*)”))
Want the above Regex for a single cell, for example cell A2? Then remove the ArrayFormula function (including open/close brackets) and change the reference A2:A to A2 in the above formula.
If there are no alphanumeric characters in the range and they only contain text formatted numbers, you can use the formula below instead of using Regex.
=ArrayFormula(A2:A5*1)
Final note
I hope you like the guide How to Add Leading Zeros 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.