Not tested...
'--
Const strStart As String = "'"
Const strFiller As String = "'0"
Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select

z = Selection.Address
For Each x In ActiveSheet.Range(z).Cells
If Len(x.Value) = 3 Then
x.Value = strFiller & x.Value
ElseIf Len(x.Value) = 4 Then
x.Value = strStart & x.Value
End If
Next 'x
'--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"JC"
wrote in message
need to format from range E4 to E2003
numbers to be four characters if only three characters
add a zero and a ' for hyperion to recognize any ideas anyone what I had

Range("E4").Select
Range(Selection, Selection.End(xlDown)).Select

Z = Selection.Address 'get the address
For Each X In ActiveSheet.Range(Z) 'Do while
If Len(X) 0 Then 'Find cells with data
X.FormulaR1C1 = Chr(39) & X.Text '39 is code for tick
Else
X.FormulaR1C1 = "" 'If empty do not put tick

End If

takes too long to format help