Macro 51: Padding Cells with Zeros
Many systems require unique identifiers (such as customer number, order number, or product num-
ber) to have a fixed character length. For instance, you frequently see customer numbers that look
like this: 00000045478. This concept of taking a unique identifier and forcing it to have a fixed length
is typically referred to as padding. The number is padded with zeros to achieve the prerequisite char-
acter length.
It’s a pain to do this manually in Excel. However, with a macro, padding numbers with zeros is
a breeze.
The problem with this solution is that the padding is cosmetic only. A quick glance at the
formula bar reveals that the data actually remains numeric without the padding (it does
not become textual). So if you copy and paste the data into another platform or non-Excel
table, you lose the cosmetic padding.
How it works
Say that all your customer numbers need to be 10 characters long. So for each customer number,
you need to pad the number with enough zeros to get it to 10 characters. This macro does
just that.
As you review this macro, keep in mind that you need to change the padding logic in Step 5 to match your
situation.
Sub Macro51()
‘Step 1: Declare your variables
Dim MyRange As Range
Dim MyCell As Range
‘Step 2: Save the Workbook before changing cells?
Select Case MsgBox(“Can’t Undo this action. ” & _
“Save Workbook First?”, vbYesNoCancel)
Case Is = vbYes
ThisWorkbook.Save
Case Is = vbCancel
Exit Sub
End Select
‘Step 3: Define the target Range.
Set MyRange = Selection
‘Step 4: Start looping through the range.
For Each MyCell In MyRange
‘Step 5: Pad with ten zeros then take the right 10
If Not IsEmpty(MyCell) Then
MyCell.NumberFormat = “@”
MyCell = “0000000000” & MyCell
MyCell = Right(MyCell, 10)
End If
‘Step 6: Get the next cell in the range
Next MyCell
End Sub
1. Step 1 declares two Range object variables, one called MyRange to hold the entire target
range, and the other called MyCell to hold each cell in the range as the macro enumerates
through them one by one.
2. When you run a macro, it destroys the undo stack, meaning that you can’t undo the changes
a macro makes. Because we are actually changing data, we need to give ourselves the option
of saving the workbook before running the macro. This is what Step 2 does. Here, we call up
a message box that asks if we want to save the workbook first. It then gives us three choices:
Yes, No, and Cancel. Clicking Yes saves the workbook and continues with the macro. Clicking
Cancel exits the procedure without running the macro. Clicking No runs the macro without
saving the workbook.
3. Step 3 fills the MyRange variable with the target range. In this example, we use the selected
range — the range that was selected on the spreadsheet. You can easily set the MyRange
variable to a specific range such as Range(“A1:Z100”). Also, if your target range is a
named range, you can simply enter its name: Range(“MyNamedRange”).
4. Step 4 starts looping through each cell in the target range, activating each cell.
5. After a cell is activated, Step 5 uses the IsEmpty function to make sure the cell is not empty.
We do this to save a little on performance by skipping the cell if there is nothing in it.
The macro then ensures that the cell is formatted as text. This is because a cell formatted as a
number cannot have leading zeros — Excel would automatically remove them. On the next
line, we use the NumberFormat property to specify that the format is @. This symbol indi-
cates text formatting.
Next, the macro concatenates the cell value with 10 zeros. We do this simply by explicitly
entering 10 zeros in the code, and then using the ampersand (&) to combine them with the
cell value.
Finally, Step 5 uses the Right function to extract out the 10 right-most characters. This
effectively gives us the cell value, padded with enough zeros to make 10 characters.
6. Step 6 loops back to get the next cell. After all cells in the target range are activated, the
macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11 on your keyboard.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code.
