[Excel VBA] How to Convert Numbers to Zero-Padded Strings (Left/Right Padding)

Overview

In Excel, you often encounter situations where you need to align numbers to a fixed number of digits by adding zeros, such as converting “45” to “00045” or “45000”. This is useful for various tasks like invoice numbers, IDs, code generation, and string formatting.

In this article, I will introduce two methods using VBA to add zeros to numbers: “Left Zero Padding” and “Right Zero Padding”.

Sample Code

Sub PadNumbersWithZeros()

    Dim padTemplate As String
    Dim totalLength As Long
    Dim cell As Range

    ' Create a 5-digit zero string (for padding)
    padTemplate = String(5, "0")
    totalLength = Len(padTemplate)

    ' Column C: Add zeros to the left to make it 5 digits (e.g., 48 -> 00048)
    For Each cell In Range("C3:C6")
        cell.Value = Right(padTemplate & cell.Value, totalLength)
    Next cell

    ' Column D: Add zeros to the right to make it 5 digits (e.g., 48 -> 48000)
    For Each cell In Range("D3:D6")
        cell.Value = Left(cell.Value & padTemplate, totalLength)
    Next cell

End Sub

Code Explanation

String(5, "0")

Creates a string concatenating five “0”s (“00000”).

The syntax String(number, character) is used to repeat an arbitrary character a specified number of times.

Right(padTemplate & Value, Length)

This achieves Left Padding. It adds zeros to the left of the value and then extracts the specified number of digits from the right side.

• Example: "00000" & 48 → "0000048" → Right(..., 5) → "00048"

Left(Value & padTemplate, Length)

This achieves Right Padding. It adds zeros to the right of the value and then extracts the specified number of digits from the left side.

• Example: 48 & "00000" → "4800000" → Left(..., 5) → "48000"

Application Examples

Important Notes

• With this method, the result is entered into the cell as a String. It cannot be used for subsequent numerical calculations.
• If the original cell contains a formula, it will be overwritten.
• To change the number of digits, simply change the 5 in String(5, "0") to your desired number.

Summary

In this article, I explained two patterns for converting numbers to zero-padded strings using Excel VBA.

Zero padding is widely used in practical work, such as organizing forms and formatting management numbers. Please utilize it in your daily tasks.