To add leading zeros in Excel, you can use the ZFILL function, which keeps the zeros before the numeric values.
How to add leading zeros in Excel
- Type the ZFILL(A1, 10) formula
- Press Enter
- The formula pads the number with zeros
ZFILL function
ZFILL is a user-defined function that aims to speed up your work if you want to add leading zeros and convert a numeric value to a text value. With its help, you can write short, easy-to-understand formulas.
Syntax
=ZFILL(range, n)Arguments
The function uses two required and one optional argument.
- “string1”: the source cell that contains a number or text value
- “filLength”: the length of the string that you want to return after adding leading zeros
- “fillCharacter”: optional argument; by default, the function uses zeros, but if you want to change the default value, for example, you can use “-“ instead of zeros.
Example
In the example, we have the data set in the range of cells B3:B10. The range contains numeric values with variable lengths. The goal is to create an invoice number style list where the total length is 10. If the length of the given value is less than 10, fill the places with zeros until the total length reaches 10 characters.
Configure the ZFILL function arguments:
- “string1” = B3
- “filLength” = 10
- “fillCharacter”: we want to use zeros and exclude the optional argument.
Formula:
=ZFILL(B3,10)Result:
Use the RIGHT function to add leading zeros
In this example, we will only apply a simple but effective trick to perform the action using built functions. The goal is to create 8 characters strings (including leading zeros), so if the length of the source value is less than 8, add leading zeros.
Formula:
=RIGHT("00000000"&C3,8)
Explanation:
The formula concatenates the “0000000000” string with the original value using the “&” operator. After that, the RIGHT function strips the unnecessary zeros from the beginning of the text and returns the string with the specified length.
