The following function takes a value as the first parameter, then a length
of the final string with left zero padding, checks that it does not
underflow and returns the padded value in LefdtZeroPad....

Function LeftZeroPad(xValue As Variant, sLength As Integer)
LeftZeroPad = xValue
If Len(xValue) = sLength Then LeftZeroPad = String(sLength -
Len(xValue), "0") & xValue
End Function

' a test sub for the function above
Sub test()
MsgBox LeftZeroPad(1234, 8)
'returns 00001234
End Sub
--
Cheers
Nigel



"jfcby" wrote in message
oups.com...
Hello,

I have 75 worksheets in my workbook. Each worksheet has 17 columns with
various numbers of rows ranging from 1 to 1200. There are data in some
rows and numbers in other rows. In column D there are 2 to 4 numbers in
each cell like so

75
789
8956
56
22
7234
709
3458
98

I was wondering if the code below can be modifed to add one or two
zeros in front of the numbers in the cells that do not have 4 numbers
in column D in multiple worksheets.

example:
0075
0789
8956
0056
0022
7234
0709
3458
0098

The code:

Sub Shorten2()
Dim ColID As Integer
Dim Iloop As Double
Dim NumRows As Double
Dim sh As Worksheet
Application.ScreenUpdating = False
ColID = InputBox("Enter column number you wish to convert.")

For Each sh In ActiveWorkbook.Worksheets
sh.Select
'Your format code
NumRows = Cells(65536, ColID).End(xlUp).Row
For Iloop = 1 To NumRows
If Len(Cells(Iloop, ColID)) = 2 Then
Cells(Iloop, ColID) = "0" & Left(Cells(Iloop, ColID), 4)
Else
If Len(Cells(Iloop, ColID)) = 3 Then
Cells(Iloop, ColID) = "0" & Left(Cells(Iloop, ColID), 4)
End If
End If
Next Iloop

Application.ScreenUpdating = True
Next sh

End Sub

I made some modifications to the code to get it to work but it does not
add the zero's.

Thank you for your help in advance,
jfcby