Add leading zeros in a number – VBA Excel formatting | debugvba.com

Add leading zeros in number vba

Objective

To add leading zeros before a number in excel cells.

Approach

In this VBA excel automation we are adding leading zeros in the numeric values. Here, we intend to make the length of all the values in column A to be 5. First, we are changing the format of the column A as “Text”. Then we loop through all the filled cells. If the length of value in the cell is less than 5 ,then we add the leading zero.

Steps

Here we are trying to add leading zeros to make the length equal to 5 for all the numbers in column A.

  1. Change the format of the column to text,where we need to add leading zero.
  2. Get the last cell in that column where data is found
  3. Loop through each cell in the column till last populated cell
  4. Check if length of the string is less than 5, if so, then add a zero at the beginning
  5. Keep adding the zeros till length becomes 5
  6. Move to next cell.

Code

Sub AddleadingZeros()
Dim MyRowCount, MyLastRow As Integer

Worksheets(1).Columns("A").NumberFormat = "@"

'Find last row in  column which has data
MyLastRow = Cells(Rows.Count, "A").End(xlUp).Row

'Looping through each cell in column
    For MyRowCount = 1 To MyLastRow
'We want the total length of string in column A to be 5
    While Len(Range("A" & MyRowCount).Value) < 5
       Range("A" & MyRowCount).Value = "0" & Range("A" & MyRowCount).Value
    Wend
    Next MyRowCount
MsgBox "Leading zeros are added"

End Sub

As we can see in the image below that there were 6 numbers in column A, and they did not have the leading zeros. Length of numbers is different, and there is no leading zeros before we execute the VBA code.

Add leading zeros in number vba
Once the VBA Excel code is executed, leading zeros were added in the column.As we can see that values are stored as text now, that is why we are seeing the warning in the cells that have numeric values ,which are formatted as text.

Add leading zeros in number vba

 

Post you may like

How to change colour of table header in PowerPoint by VBA excel