Pad Number of Digits
I received a long thing with lots of cases and code that was not working. This man wanted to pad out numbers that are 4 to 6 characters to 10 by adding leading zeros.
Sub Zeroes()
Dim colIndex
For colIndex = 3 To 102 'To choose colums T3 through T102
If Worksheets("Home Sheet").Cells(1, colIndex) = Empty Then
Exit Sub
End If
Select Case Len(Cells(colIndex, 1)) 'Find out how long the number is
Case Is < 10 'If less than 10 digits
Cells(1, colIndex).NumberFormat = "000000000#" 'Expand to 10 with zeros
Case Else
End Select
Next 'To select the next cell in the T column
End Sub
The one thing about this is that the number is not really changed, if you click on it, it just displays zeros on the spreadsheet. To truly add leading zeros, you would need to convert to text.
|