How To Add Leading Zeros In Google Sheets

by

Whether you’re organizing spreadsheets for your business, school, or personal needs, it’s crucial to maintain the format consistency of the data you’re working with. At times, you may need to add leading zeros to numbers or strings in Google Sheets. In this blog, we will walk you through several methods on how to accomplish this.

Method 1: Using the ‘Format Cells’ Feature

The easiest way to add leading zeros in Google Sheets is by using the built-in ‘Format Cells’ feature. Here’s how to do it:

  1. Select the cell or range of cells where you want to add the leading zeros.
  2. Click on Format in the menu, then select Number, followed by More Formats at the bottom of the drop-down menu.
  3. Select Custom number format.
  4. Now, type in the format that suits your needs. For example, typing “00000” will format your number to 5 digits, adding leading zeros where needed.
  5. Press Apply.

Method 2: Using a Formula

If you need a more flexible or automated way of adding leading zeros, you can use Google Sheets’ formulas. The TEXT formula is especially useful for this.

To use this formula, type in =TEXT(A1,”00000″) in the cell where you want the formatted number to appear. Replace A1 with the reference to the cell that contains the number you want to format.

Method 3: Using Google Apps Script

If your work involves complex or large-scale data manipulation, using Google Apps Script can be a powerful tool. Here is a simple script that adds leading zeros to the selected range:

            function addLeadingZeros() {
                var range = SpreadsheetApp.getActiveRange();
                var values = range.getValues();
                for (var i = 0; i < values.length; i++){
                    for (var j = 0; j < values[i].length; j++){
                        if(values[i][j] !== ""){
                            values[i][j] = ('00000' + values[i][j]).slice(-5);
                        }
                    }
                }
                range.setValues(values);
            }

To use this script, click on Extensions, then Apps Script. From there, you can paste the script into the script editor and run it.

Conclusion

Adding leading zeros in Google Sheets can be accomplished easily using the Format Cells feature, applying a formula, or by utilizing Google Apps Script. Each method has its own scenarios where it could be more suitable, depending on the complexity of your spreadsheet and the scale of the data you’re working with.