How to Add Leading Zeros in Excel

Use-the-RIGHT-function-to-add-leading-zeros

To add leading zeros in Excel, you can use the RIGHT or ZFILL function, which keeps the zeros before the numeric values.

If you work with data cleaning tasks in Excel more seriously, you’ve likely encountered the following problem: How do you add leading zeros to a number? This task, which might seem trivial at first glance, becomes much more complex on the second attempt. The challenge is that you can only add leading zeros to a numeric value if you convert the entire expression to text.

How to add leading zeros in Excel

Steps to add leading zeros:

  1. Open Excel
  2. Type =RIGHT(“00000000″&C3,8)
  3. Press Enter
  4. The formula returns the string with the specified length.

Example

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-character strings (including leading zeros), so if the length of the source value is less than 8, add leading zeros.

Formula:

=RIGHT(“00000000″&C3,8)

Use-the-RIGHT-function-to-add-leading-zeros

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.

ZFILL Formula to add Leading Zeros in front of a number

  1. Use the =ZFILL(A1,10) formula
  2. Add the text you want to append; in this case, A1.
  3. Type 10, which is the length of the string (with zeros)
  4. Press Enter to add zeros in front of a number

Arguments:

The functions use the following arguments:

  • string1: The original text or number you want to modify.
  • fillLength: The total length you want the final string to be.
  • fillCharacter (optional): The character you want to add to the string. By default, this is “0”.
  • rightToLeftFlag (optional): If False (or not provided), the fill character is added to the beginning of the string. If True, it is added to the end.

How the ZFILL function works

The ZFILL function is a custom function that adds characters (in most cases, zeros) to a string until it reaches a specified length. The function uses two required and two optional arguments.

The function checks the length of the original string. If it’s shorter than ‘fillLength‘, it keeps adding the fillCharacter until the string reaches the desired length. The function returns the modified string, filled to the specified length with the chosen character.

Example

In this example, I want to create a ten-character-long string from the number in cell B3. The task involves two steps. First, determine the length of the value in the cell, which is 3 in this case. Since the required length is 10, the function will add seven zeros before the number.

Formula:

=ZFILL(B3,10)
total length

Here is the result:

Steps to Add Leading Zeros in front of a number

Download the Workbook containing the ZFILL function.

If you want to dive into the source code, you can do it here:

' Function to add a specific character (default is "0") to a string until it reaches a specified length
Public Function ZFILL( _
    ByVal string1 As String, _  ' The original string or numeric value to be filled
    ByVal fillLength As Byte, _  ' The desired total length of the final string
    Optional ByVal fillCharacter As String = "0", _  ' The character to fill with, default is "0"
    Optional ByVal rightToLeftFlag As Boolean) _  ' Determines whether to fill from the left (False) or right (True)
As String

    ' Loop until the length of the string reaches the specified fill length
    While Len(string1) < fillLength
        ' Check if we are filling from the left (default)
        If rightToLeftFlag = False Then
            ' Prepend the fillCharacter to the beginning of the string
            string1 = fillCharacter + string1
        Else
            ' Append the fillCharacter to the end of the string
            string1 = string1 + fillCharacter
        End If
    Wend  ' End of the loop, continue until the string is the desired length
    
    ' Return the modified string after it has been filled to the required length
    ZFILL = string1

End Function

Additional resources: