Leading zeros in Google Sheets refer to the leading zeros in a number.
These zeros do not affect the value of a number itself, but they can affect how the number is displayed and interpreted.
Leading zeros are particularly relevant when working with numbers that have a specific format, such as phone numbers, postal codes, or product numbers, where keeping leading zeros is important for the accuracy of the information. For example, in many cases you might want to display a phone number like "012-345-6789" with the leading zero intact instead of "12-345-6789" without the leading zero.
However, by default, Google Sheets can remove leading zeros from numbers when they're entered into cells. This behavior can sometimes cause problems when it is necessary to preserve leading zeros.
This post will discuss how to remove or keep leading zeros in Google Sheets and some practical applications.
Continue reading to learn more.
How to add/retain leading zeros in Google Sheets
Keeping leading zeros in Google Sheets is relatively easy, and there are several ways to do it.
Below we review the three most prominent methods.
Method 1 – Add an apostrophe to remove leading zeros
If you enter numbers into a cell in Google Sheets, add leading zeros at the beginning, and press Enter, you'll notice that the zeros disappear and only the numbers remain. However, if you add an apostrophe before entering the leading zeroes, you can keep them.
Below are the steps to preserve leading zeros.
Step 1 - Select a cell
Click in a cell to which you want to add a number with a leading zero. In our example, this will be cell A1.
Step 2 - Add an Apostrophe Before the Leading Zeros
Enter an apostrophe before typing the numbers with leading zeros that you want to add to your digits. For our example, we will have three leading zeros for each number.
Step 3 – Press Enter
After pressing Enter, the Google Sheets document should have leading zeros.
Method 2 – Use Text Formatting to Keep Leading Zeros in Google Sheets
The following method involves formatting the entire column that you want to keep with leading zeros using the text format.
Below are the steps on how this works.
Step 1 - Select a column
Select the column where you want to enter your numbers and check it as shown below.
Step 2: Go to "Format > Number > Plain Text" to change the text format.
After highlighting the column, go to the menu section, click onFormat, hover overNumberand selectplain text.
Step 3: Enter leading zeros and numbers
You should now be able to enter the number of leading zeroes you want for numbers without them disappearing after pressing Enter.
Also note that leading zeros will only work in this column. If you want to keep the leading zeros in another column, repeat the process and change the format of the text in the new column.
Method 3: Use a custom number format to automatically add and preserve leading zeros in Google Sheets
and organizenumeric formatallows you to enter a specific number of digits as a custom number format, where any number you enter automatically adds leading zeros until it is the number of digits specified by your format.
For example, if your custom number format indicates a 5-number output, if you enter 32, the format includes 3 leading zeros to form a 5-digit number.
Here are the steps to create a custom number format.
Step 1 - Select a column
We'll choose column A for our example, but it could be any column, depending on your application.
Step 2 – Highlight the Column
Place your cursor at the top of the column header section and click it to highlight the entire column. The cursor should be changed to a support arm.
Step 3 Go to "Format > Numbers > Custom Number Format" to customize the number format
After highlighting the column, go to the menu section on the top bar and click onFormat. Then hover overNumberoption to open a secondary menu option and scroll to the bottom.
The last option should becustom number formatand click on it.
Step 4 - Define your custom number format by typing 5 zeros
A new menu dialog appears when you click the buttoncustom number formatpossibility. Click in the text field at the top of the new menu item and type 5 zeros.
Also note that the 5 zeroes are for demo purposes only in this example and could be any custom number you are trying to create.
Step 5 – Click Apply
Click Apply to apply the custom.numeric formatfor your column. Now, if you enter a number with less than 5 digits, the corresponding number of leading zeros will be added automatically to bring the number of digits to 5, as shown below.
As you can see, keeping leading zeros in Google Sheets is not that complicated and there are several ways to implement it depending on your application.
How to remove leading zeros in Google Sheets
On the other hand, you can also remove leading zeros from numbers in Google Sheets using these simple methods.
Method 1: Invert the leading zero entry
Interestingly, the same methods that help insert and maintain leading zeros in Google Sheets can also be used to remove them if they were originally inserted using these techniques.
If you need to remove leading zeros and ensure data consistency, these methods will help.
- restore format: If you used the "Format cells as text" method to keep leading zeros and need to revert it, it's as simple as changing the format back to "Automatic"o"Number"format. Select range, go toFormat > From,and this time chooseNumberformat the pattern"Automatic”, and the leading zeros disappear.
- Get rid of the apostrophe: Remember that clever apostrophe trick? If you've already used it and want to remove the leading zeros, just edit the cell. Remove the apostrophe and the number will adjust accordingly.
- Adjust custom number format: If you have applied custom numeric formatting to leading zeros, you can easily remove them without sacrificing numeric value. just go to"Format > Number > Automaticto reset the number format.
Knowing how to remove leading zeros added by these methods is just as important as preserving them. With a few simple steps, you can change your data without compromising its accuracy. The key is to understand which method you used and use the correct removal approach.
So whether you're fixing errors, standardizing data, or improving readability, the techniques that once protected the accuracy of your data can now help you fine-tune it.
Method 2 – Using Cutoff Value Formula to Remove Leading Zeros in Google Sheets
When you already have a Google Sheet with a section that contains leading zeros, using the Cutoffs formula is the most convenient and efficient way to remove leading zeros.
In our example below, a worker tries to get employee ID numbers without the leading zeros. Here's a more detailed look at the steps.
Step 1 - Select a cell
First, select the cell where you want the formula to return non-zero values. In our example, we select cell D2.
Step 2: Enter the formula
This is the formula you need to remove leading zeros
Step 3 – Press Enter
After pressing Enter, the first employee ID number without the leading zeros should auto-populate the cell. A popup should also appear asking you to confirm that you want to fill in the rest of the cell, which you can click Yes.
Otherwise, the first cell with a non-zero employee ID number should have a little node that you can drag down to auto-populate the rest of the cells, as shown below.
Method 3 – Use Value Text Formula to Get Rid of Leading Zeros in Google Sheets
Unlike our previous example, the text-to-value formula takes a number in Google Sheets with leading zeros, converts the values to text, and then returns them to numeric values, ignoring the zeros.
We'll use the same Google Sheets document from the previous example to demonstrate how the value text formula works. These are the steps you must follow.
Step 1 - Select a cell
As in the previous example, you start by identifying the cell where you want to enter your formula and the results that will be returned. This will be cell D2.
Step 2: Enter the formula
Here is the formula to remove null values from the employee id section.
Step 3 – Press Enter
After pressing enter, the non-zero employee identification number should appear in the cell. To automatically complete the rest of the cells, click the small button in the active cell with the first result and drag it down.
This should reveal the remaining non-zero employee ID numbers, as shown below.
Leading zeros allow you to maintain consistent formatting when working in Google Sheets for a more professional appearance. However, Google Sheets doesn't use leading zeros by default, and you need to know how to use them most effectively for what you're doing.
You may also have an existing Google Sheets document that has leading zeros that can be awkward to work with and you want to remove them. So knowing how to remove or keep leading zeros in Google Sheets is an important skill you can use to streamline your workflow when working with these data management programs.
We hope this post helped you understand how to remove and keep leading zeros using apostrophes, formulas, and custom number formatting. Now get out there and use Google Sheets for yours.data management and analysis, knowing that you can also remove or keep the leading zeros.
What are leading zeros in Google Sheets?
Leading zeros are zeros that appear at the beginning of a number before other significant digits. They are often used to preserve format or convey specific meanings, such as postal codes or identification numbers.
Can I remove leading zeros from a range of cells at once?
You can remove leading zeros from a range of cells by applying the VALUE function to the entire range. For example, if your range is A1:A10, you can use the formula: =ARRAYFORMULA(VALUE(A1:A10)).
When should I remove leading zeros?
Consider removing leading zeros when they are irrelevant to the meaning of the data or when the data is used in calculations where leading zeros might affect the results.
When should I continue with leading zeros?
It is advisable to retain leading zeros where they are significant, such as in codes, identifiers, or other data where it is essential to maintain a particular format.
My formulas to remove/retain leading zeros don't work. What could be wrong?
Check your formulas for typos and correct cell references. Make sure your data is in the expected format and uses the proper functions.
Will removing or keeping leading zeros affect my calculations?
Changing leading zeros can affect calculations, especially if they have a specific meaning. Always review your data and the impact of formatting changes on your calculations.
While working with Excel and Google sheets, we are able to add or remove leading zeros by changing the format, or by using functions such as TEXT, REPT or VALUE. There are three methods to add leading zeros: Format as text to keep zeros as we type. Convert to text using TEXT and REPT function.Why does my zero keep disappearing on Google sheets? ›
Spreadsheets often format zip codes in inconvenient ways, often causing the "leading zero" to disappear (ie. 02354 turns into 2354). You are able to correct the formatting of a spreadsheet to fix this issue.How do you pad a number with leading zeros in Google sheets? ›
- Select the range of numbers.
- Open Format ➜ Number ➜ More Formats ➜ Custom Number Format.
- Enter 000 in the text field, where the number of 0s matches the desired length of your padded numbers.
- Click on Apply.
- Finish the rest of your spreadsheet!
To remove characters from the left of a cell, we can use the REPLACE function to replace the first characters with an empty string (“”). The REPLACE Function allows you to define which characters to replace.How do I automatically remove leading zeros in Excel? ›
Choose "Format Cells" and select the "Custom" category. In the "Type" textbox, enter the format code "#". This will tell Excel to display the value in the cell without any leading zeros. Click "OK" to apply the formatting to your cells.How do you remove leading zeros in Ltrim? ›
LTRIM(LTRIM(RTRIM(columnname)),0) - Use this when you want to trim the space from leading and trailing (inner ltrim and rtrim function), and then ltrim to remove the leading zero. LTRIM(columnname,0) - This would only remove the leading zero for the data contained in a particular source field.How do I remove zeros in Google sheets? ›
- Select all cells (Ctrl + A) click Format > Conditional Formatting.
- Use the dropdown to select Equal To and type 0.
- Tick the text tickbox and change the text color to white, this will effectively hide all your zero values.
We can replace #DIV/0 with 'N/A' (or anything else of our choosing) by updating the formula to =IFERROR((C5/B5 - 1),“N/A”) . IFERROR checks the value for (C5/B5) -1 , sees that the result is an error because you're trying to divide by zero, and so returns the text “N/A” .How do you keep leading zeros in number format? ›
Select the cell or range of cells that you want to format. Press Ctrl+1 to load the Format Cells dialog. Select the Number tab, then in the Category list, click Custom and then, in the Type box, type the number format, such as 000-00-0000 for a social security number code, or 00000 for a five-digit postal code.How do you remove leading zeros from a string number? ›
Use the inbuilt replaceAll() method of the String class which accepts two parameters, a Regular Expression, and a Replacement String. To remove the leading zeros, pass a Regex as the first parameter and empty string as the second parameter. This method replaces the matched value with the given string.
- Text to columns.
- Click the 'delimited' radio button.
- Click 'next'
- Set your delimiter to 'space'
- Click 'next'
- This is the key part! Change the 'column data format' radio button from 'general' to 'text' of any column where you want to retain leading zeros.
- Click 'finish'
First of all, there is a simple keyboard shortcut for the Clear Formatting in Google Sheets. Pressing CTRL + \ will clear the formatting.How do I stop number formatting in Google Sheets? ›
- Log in to your Google account.
- Select the cells for which you want to disable auto-format. ...
- Click the "123" icon on the Google Spreadsheets menu bar.
- Select the appropriate format for your range of cells. ...
- Repeat this procedure as needed on other ranges of cells.
Remove leading zeros by changing the cell format
If leading zeroes are shown in cells with a custom format, then change the cell format back to default General, or apply another number format that does not display preceding zeros.
This is achieved through REPLACE command which replaces all 0's with an empty blank. To perform this for more than one column, place a comma and then write multiple replace commands.How do I trim all leading zeros in SQL? ›
- Replace each 0 with a space – REPLACE([CustomerKey], '0', ' ')
- Use the LTRIM string function to trim leading spaces – LTRIM(<Step #1 Result>)
- Lastly, replace all spaces back to 0 – REPLACE(<Step #2 Result>, ' ', '0')