Excel VBA

css navigation by Css3Menu.com

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.

© 2002-2016

Updated:  11/16/2016 19:50
This page added:  19 September 2002