To add leading zeros before a number in excel cells.
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.
Here we are trying to add leading zeros to make the length equal to 5 for all the numbers in column A.
- Change the format of the column to text,where we need to add leading zero.
- Get the last cell in that column where data is found
- Loop through each cell in the column till last populated cell
- Check if length of the string is less than 5, if so, then add a zero at the beginning
- Keep adding the zeros till length becomes 5
- Move to next cell.
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.
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.
Post you may like