It’s quite important for a few spreadsheet users to display zero before the number. However, when you enter a number in Google Sheets with leading zeros, then it might result just in numbers by eliminating the zeros in Google Spreadsheet. Thus, to overcome this issue, Google Sheets allows its users to enter leading zeros in the spreadsheet without disturbing the format. In this article, let us learn everything about how to show leading zeros using Google Sheets Tips. Read further to find out more.
Adding Leading Zeros in Google Sheets Using Custom Number Format
One of the easiest methods to keep leading zeros in Google Sheets is to use the Custom Number format feature. The detailed steps on how to get this done in Google Sheets are given below:
- 1st Step: First, launch Google Sheets on your device.
- 2nd Step: On the homepage, click on the Format tab and choose “Number” from the drop down menu.
- 3rd Step: Choose “Custom Number Format” from the drop down box.
- 4th Step: In the custom number format, enter the number as “000“. Here I have added only 3 zeros since I need to add only one zero before the number. Depending on the number of leading zeros you need to add, you can calculate this formula.
- 5th Step: Press the “Apply” button and you will find the results as shown in the image given below.
Using Apostrophe To Add Leading Zeros in Google Sheets
Another easy method to add leading zeros in the Google Spreadsheet is to use an apostrophe. The detailed steps on how to use the apostrophe to add leading zeros in Google Sheets are given below:
- 1st Step: Launch Google Sheets on your device.
- 2nd Step: On the homepage, click on the cell where you want to add the leading zeros.
- 3rd Step: Enter the symbol ‘(apostrophe) and enter the number of zeros you want as shown here.
- 4th Step: Press the “Return” key and you will find the results as shown in the image given below.
Use the TEXT Function To Add Leading Zeros in Google Sheets
The TEXT function is another simple way to add leading zeros in a Google Spreadsheet. The detailed steps on how to use the TEXT function to add zeros in Google Sheets are given below:
- 1st Step: First, launch Google Sheets on your device.
- 2nd Step: Now on the homepage, move to the cell where you would like to add zeros.
- 3rd Step: Enter the formula “=TEXT (B2,”000″)“.
- 4th Step: Press the “Return” key and you will find the results shown in the image given below.
Using the RIGHT Function to Add Leading Zeros in Google Sheets
Apart from the TEXT function, we can also use the RIGHT function in Google Sheets, which helps us to add leading zeros. The detailed steps on how to add leading zeros in a spreadsheet using the RIGHT function are outlined below:
- 1st Step: First, launch Google Sheets on your device.
- 2nd Step: On the homepage, move to the cell where you want to use the RIGHT function.
- 3rd Step: Enter the formula “=RIGHT(“000” & B2,3)“.
- 4th Step: Press the “Return” key and you will find the results as shown in the image given below.
Using the CONCATENATE Function To Add Leading Zeros in Google Sheets
Most spreadsheet users would be familiar with the CONCATENATE function. If you are also one of them, then adding a leading zero is much easier in Google Sheets using the CONCATENATE function. The detailed steps on how to do this in Google Sheets are given below:
- 1st Step: Open the Google Spreadsheet on your device.
- 2nd Step: On the homepage, move to the cell where you want to use the CONCATENATE function.
- 3rd Step: Enter the formula “=CONCATENATE (“0,” “B2”)“.
- 4th Step: Press the “Enter” key and you will find the results as shown in the image given below.
Using the BASE Function To Add Leading Zeros in Google Sheets
One can also simply use the BASE function to add the leading zeros in the Google Spreadsheet. The detailed steps on how to do this are given below:
- 1st Step: Open the Google Spreadsheet on your device.
- 2nd Step: Move to the cell where you want to show the results of adding leading zeros.
- 3rd Step: Enter the formula “
" - 4th Step: Press the “Enter” key and you will find the results as shown in the image given below.
Using the QUERY Function to Add Leading Zeros in Google Sheets
We can also simply use the QUERY function in Google Sheets to add the leading zeros to the spreadsheet. The detailed steps are outlined below:
- 1st Step: Open the Google Spreadsheet.
- 2nd Step: On the homepage, click on the cell where you want to use the QUERY function.
- 3rd Step: Enter the formula “
=QUERY (B2:B17, "SELECT * FORMAT B '000'")“. - 4th Step: Press the “Return” key and you will find the results as shown in the image given below.
Adding Leading Zeros in Google Sheets Using Apps Script
One of the best methods to add leading zeros in Google Sheets automatically is to use Apps Script. The detailed steps on how to use Apps Scripts to add leading zeros in a Google Spreadsheet are given below.
- 1st Step: Open Google Sheets on your device and select the dataset to add leading zeros.
- 2nd Step: On the homepage, click on the “Extensions” tab and choose “Apps Scripts” from the drop down menu.
- 3rd Step: Copy and paste the following code into the code editor.
function leadingZeros(){
var sheet=SpreadsheetApp.getActive()
var activeSheet=sheet.getActiveSheet()
var range=activeSheet.getDataRange()
range.setNumberFormat('00000#')
}
- 4th Step: Click on the “Run” button. Once the execution is completed, navigate to the spreadsheet and you will see the results.
Based on the various methods through which you can add leading zeros to the spreadsheet, based on the requirements and time you have, use the method that fits your situation.