Often there are situations where entering numbers with leading zeros is of utmost importance for your data.

You might be working with Employee IDs and want the entire data of the same size, you might be working with phone numbers with an additional zero at the beginning, or you have zip codes with extra zeros at the start of it.

May the situation be any of these, but you surely want to find ways of adding leading zeros in your numbers.

The problem with google sheets is, even if you try typing a number with leading zeros, it will not keep them.

This article will teach you nine methods that can help you add leading zeros to your numeric values.

Get the example workbook with the above link to follow along with the post.

Let’s try to decode each of these methods to become proficient in this task.

Format as Text Method to Add Leading Zeros

Google Sheets removes leading zeros if you type a number with them inside any cell.

A common practice most of the users follow is changing the format of cells. If you change the format of the cells as text, you can easily store the numbers with leading zeros as the system will consider them as text inside those cells.

To change the format of the cells to text, follow the steps below:

1. Select the number of cells you want to change the format of.
2. Click on the Format menu button from the ribbon.
3. Select Number from the options. This opens all the number formatting options.
4. From the available options, select the Plain text option. This method changes the cell formatting for A3:A6 to text.

Now when you type numbers with leading zeros inside these cells, the system will keep them. Since we formatted the cells as text, any values within them are text from the system’s point of view. See the above illustration.

Note: This method is fragile. You are literally storing numbers as text. It changes the data format and associated data properties.

Tip 1: Make sure you change the formatting of the cell before entering the numbers in it.

Tip 2: If you copy and paste numbers to the ranges formatted as text, make sure you paste those as values. This method will only work in that case. Otherwise, you might see some inconsistent results.

Using Custom Format to Add Leading Zeros

It is always possible to use the custom formatting with spreadsheets, and you might have worked with it in Excel. Google Sheets are no different. You can use custom formatting to make these numbers appear with leading zeros.

Using custom formatting, you can set the length of your numbers. If you set the custom formatting to show numbers with six digits, the system will try to amend the length by adding zeros at the start of that number.

For example, if you have a number 159, the system will add three leading zeros to make it a six-digit number. The number will look like 000159.

Follow the steps below to apply a custom format to the cells to add leading zeros.

1. The first thing to do is add the data in cells. I have added data across A10:A13. Select this entire range to apply a custom format to it.
2. Click on the Format menu through the ribbon.
3. Select the Number option.
4. Next, choose the More formats at the bottom.
5. There are three options in that submenu. Click on the Custom number format option.

6. The Custom number formats window will appear as shown above.

7. Put the number format as 000000. It will apply the six-digit number format to cells A10:A13.

8. Finally, click the Apply button, and you are done!

Once you hit the Apply button, you can see the format of cells is now changed; those numbers that are less than six digits have leading zeros added to them.

Note 1: If your number is less than six digits, the system will add the leading zeros. Otherwise, for a number greater than six digits, nothing will happen. However, you can change the number of zeros inside the Custom number formats to more than six. This way, you will have the leading zeros to numbers with more than six digits.

Note 2: The system will only change the appearance of the number. It will not change the way that number is stored in the backend. If your number is 159, and it appears as 000159. In the backend, it is still held as 159 and not 000159. Just the appearance is different and not the data type.

Note 3: This method only works with numbers. Don’t expect a leading zero if you are entering a text inside those cells.

Using Apostrophe to Add Leading Zero

This is by far the simplest method I came across to add leading zeros inside the Google Sheets.

You need to put the apostrophe before typing any number with leading zeros inside the cell. The apostrophe converts the number into text and, by doing so, keeps the leading zeros intact.

If you want to add three leading zeros to the number 159, type ‘000159 inside the cells, and you are through.

To add leading zeros to your numbers using the apostrophe, click on any cell from your sheet. Type the apostrophe in the cell and then the number with leading zeros.

This way, the number will hold leading zeros inside the cell.

Using the TEXT Function to Add Leading Zeros

If you want a dynamic way of adding leading zeros to your numbers, the TEXT function should be the solution.

The TEXT function takes a cell reference as an argument and then converts the cell value into text. Additionally, it also can apply a specific format to the value.

Syntax for the TEXT Function

The TEXT function allows you to format numbers as text using a given format.

TEXT ( number, format )

• number – specifies the numeric value. Most of the time, it will be the cell reference containing the number value.
• format – specifies a format that you want to apply to the number values.

Add Leading Zeros with the TEXT Function

To add leading zeros using the TEXT function in your number, follow these steps.

Consider we have data in column A as shown in the screenshot above.

= TEXT ( A20, "000000" )

In cell B20, insert the TEXT function. Input the number and format parameters as A20 and “000000”, respectively.

The format “000000” means the number should be of six digits and if it is not, add leading zeros to make it six digits.

Hit the Enter button, and you can see that four leading zeros are added before the number 12. It is also noteworthy that the alignment is on the left-hand side now in column B. This indicates the data has been converted to the text.

Drag the formula down or select the cells and hit Ctrl + D to expand this formula across other cells.

This is how you can add leading zeros to the numbers using the TEXT function. You can directly use the number inside the function and then use the format to return the desired output.

= TEXT ( 12, "000000" )

For Example, the above formula will also return the same output as shown in cell B20.

Using the RIGHT Function to Add Leading Zeros

In the previous method, I showed you a dynamic way of adding leading zeros by using the TEXT function. However, there is another method that is toe-to-toe with this method.

Using the RIGHT Function can also give you leading zeros. It is more dynamic in that you can add the leading zeros to text values as well.

Syntax for the RIGHT Function

The RIGHT function allows you to extract a given number of characters from the right side of a text string.

RIGHT ( string, [count] )

• string – specifies a mandatory argument and represents the text or a cell containing text from which the rightmost portion is extracted from.
• count – is an optional argument representing the number of characters you want to extract from the right. If not specified, the first character from the right will be pulled.

Add Leading Zeros with the RIGHT Function

Consider we have the data as shown in the screenshot above. The data is of mixed type. You have the text as well as numeric values.

= RIGHT ( "000000" & A27, 6 )

In cell B27, insert the RIGHT function and input the values for the string as well as the count argument as “000000”&A27 and 6, respectively.

Hit the Enter button, and you can see four leading zeros are added before the number 12.

The RIGHT function takes six zeros and the value in cell A27 as string input. Then, since we have mentioned the count argument as 6, the function extracts six characters from the right to return a string with four leading zeros followed by 12.

Drag the formula down until B30 or select cells and hit Ctrl + D button to expand this formula across the remaining cells.

Note: If you use this formula on a blank cell, you will get a text with all six zeros.

Using the CONCATENATE Function to Add Fixed Number of Leading Zeros

The methods we have used are adding leading zeros based on the predefined size of the text inside the cells.

What if we want to add a specific number of leading zeros irrespective of the length of cells?

You can use the CONCATENATE function or the operator ampersand (&) operator to add a particular number of leading zeros to the cell value.

Syntax for the Function

The CONCATENATE function allows you to join multiple text strings into a single text string.

CONCATENATE ( string1, [string2, …] )

• string1 – first string as a required argument to provide inside the function.
• string2 – an optional string argument that, if added, can be appended with string1.

Add Leading Zeros with the CONCATENATE Function

The data for this demo is as shown above.

= CONCATENATE ( "0", A34 )

Inside cell B34, insert the CONCATENATE function. The string1 input is “0” and string2 input is the cell reference A34.

Hit the Enter button to see the output as shown above.

Drag the formula down or hit the Ctrl + D button to populate this result across the remaining cells and see the output as shown above.

Using the BASE Function to Add Leading Zeros

We can also use the BASE function in Google Sheets to add leading zeros to our numbers.

Syntax for the BASE Function

The BASE function is a part of the Mathematical and Trigonometry Function family and converts a given numeric value to a specific base that can be binary, decimal, hexadecimal, etc.

BASE ( value, base, [min_length] )

• value – is a required argument and specifies a numeric value that you want to convert to a specific base. The number should be an integer.
• base – is a required argument and specifies the base to which you want to convert. The value for this argument can be between 2 to 36. It is a radix that is used in the traditional numbering system. For Example, 2 stands for binary, 10 stands for decimal, etc.
• length – is an optional argument that can be used to determine the minimum string length of the output. If not specified, the default value is 1.

Add Leading Zeros with the BASE Function

The data for this demo is as shown above.

= BASE ( A41, 10, 6 )

Inside cell B41, insert the BASE formula and add the arguments as A41 (value), 10 (decimal base), and 6 (string should be 6 characters in length).

The function converts the number to a string in a decimal system then adds three leading zeros to it so that the length should be a minimum of 6 characters.

To see the output, hit the Enter button.

Finally, drag the formula down to see the formula being populated across the cells. You can also use the keyboard shortcut Ctrl + D.

Using the QUERY to Add Leading Zeros

You can also use the most advanced Google Sheet QUERY function to add the leading zeros to your numbers.

The function works like magic in many situations, and if you haven’t used it yet you can check out our definitive guide to the QUERY function.

The FORMAT clause from the QUERY function helps us apply custom format on our numbers to add leading zeros.

We will use the above data as a demo to add leading zeros in the numbers using the QUERY function.

= QUERY ( A48:A51, "SELECT * FORMAT A '000000'" )

Put the function mentioned above inside cell B48.

Now, since the function itself is developed in a way that it could work with the arrays, you don’t need to copy the formula down.

Just hit the Enter button and see the formula automatically populating across B48:B51.

The formula first takes range A48:A51 as a data argument. Then inside the double quotes, format “000000” gets applied on the entire column A. Finally, the SELECT * statement returns all rows from column A as an output with the new format.

Using the App Script to Add Leading Zeros

If we are talking about Google Sheets, it wouldn’t be complete without talking about this beautiful automation feature of the tool.

You can also develop an App Script to add the leading zeros to the numbers automatically. Let us see how we can do it.

If you’re new to Google Apps Scripts, then check out this beginner’s guide that will teach you everything you need to know!

Suppose you have the data as shown above inside column C.

1. Click on the Tools menu inside the ribbon.
2. Click on the Script editor option to open App Script editor in a new tab.

The App Script interface looks like above.

Rename the project from Untitled project to Leading Zeros through the Rename Project tab. Hit the Rename button to change the name.

function leadingZeros(){
  var sheet=SpreadsheetApp.getActive()
  var activeSheet=sheet.getActiveSheet()
  var range=activeSheet.getDataRange()
  range.setNumberFormat('00000#') 
}

Now, copy and paste the script above inside the App Script interface.

Code Explanation

• The first line of the code defines the function leadingZeros. To define a function, we use the function keyword.
• The variable sheet is defined with SpreadsheetApp.getActive() method and helps you to get the active Google Sheet.
• You get the active sheet from the spreadsheet on the third line of the code using the getActiveSheet() method. The results are stored inside the activeSheet variable.
• Then you use the getDataRange() method to get the data range in which the data is present. You store it into the range variable.
• Finally, you call the setNumberFormat(‘00000#’) method on the range variable to change the appearance of the numbers. The numbers will now be a minimum of six characters, where leading zeros will pad the text.

Click on the Run button from the upper ribbon to run this script.

If you are trying to run the App Script for the first time after your previous session, you’ll require authorization permission to be provided. However, since I already have previously run the code in this session, I don’t need to authorize it again.

As soon as you hit the Run button, the system starts executing this query, and you can see the process inside the Execution log, as shown above.

If you are receiving the Execution completed message, as shown above, you are good. The code has run successfully. If you have some errors in the code, you will instead see an error message there.

Navigate towards the Google Sheet to see the output with leading zeros.

Conclusion

There are various methods to add leading zeros to your numbers.

The Format to Text method allows us to convert the range into text. Then anything we can put inside those cells will hold as it is, and we can put the numbers with leading zeros.

You can use the Custom Number Formats to add leading zeros to your numbers.

The simplest method to add leading zeros is adding the apostrophe before the number.

The TEXT, RIGHT, CONCATENATE, and BASE are great functions you can use to add leading zeros.

It’s also possible to add leading zeros using the all-powerful QUERY function along with the FORMAT clause.

Finally, you can develop an App Script of your own to add leading zeros. The advantage of this method is, you can automate it based on some triggers, and then the system will automatically add the zeros to numbers next time you input values.

What are your favorite ways among all of these? Let us know in the comment section. If you know any other methods, comment those out as well.