Help Number formatting of actual data not just the view
 

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

Help Number formatting of actual data not just the view
 

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

Help Number formatting of actual data not just the view
 

Also untested.... does this do what you want?

For Each x In ActiveSheet.Range("E4:E" & _
ActiveSheet.Cells(Rows.Count, "E").End(xlUp).Row)
If Len(x.Text) = 3 Then
x.Text = "'0" & Replace(x.Text, "'", "")
ElseIf Len(x.Text) = 4 Then
x.Text = "'" & Replace(x.Text, "'", "")
Else
x.Text = ""
End If
Next

Rick


"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

Help Number formatting of actual data not just the view
 

Does the Zero need to be a leading Zero? Do they need to be treated as
numbers for calculation purposes or text (ie for coding)

You could probably use a custom format to ensure that all cells are 4
numbers wide.

I came across this problem many years ago and we were forced to use Lotus
123 as it allowed the download to be shown as text rather that a number. I
would guess that updates within Excel would've now caught up.

Let me know how you get on.

"JC" wrote:

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