how to control trailing zeroes

[Plasmech]

If I enter 0.020 in the formula bar, I would like Excel to show 0.020 in the cell.

If I enter 0.02 into the same cell, I would like Excel to show 0.02.

How would I go about setting the cell's custom number format?

Thank you for any replies.

 

[iggydarsa]

change the cell format to Text

 

[Plasmech]

change the cell format to Text
View attachment 122344

Thanks. That worked.

For the sake of learning, is there a way to do it via custom number formatting?

 

[Cubist]

Select the cells -> Ctrl + 1 -> Choose number of decimal places.
This will keep the data as number rather than text.

 

[Joe4]

Select the cells -> Ctrl + 1 -> Choose number of decimal places.
This will keep the data as number rather than text.
View attachment 122345

Hmmm, I don't think that is going to help them, as the number of decimal places does not appear to be constant.
In their example, the first entry had 3 and the second had 2.
It appears that the want it to match their entry at run-time, i.e.
- if they enter 0.020, they want it to show 3 decimals
- if they enter 0.02, they want it to show 2 decimals

There is only two ways that I can think of to do what they want:
1. Change the format to TEXT, as shown in the first reply.
2. Use VBA to dynamically adjust the format of each cell as values are being entered into it.

Plasmech, if you are open to a VBA solution, please let us know.

 

[Cubist]

decimal places does not appear to be constant.

Missed that but wouldn't recommend the OP doing that as it's muddy and inconsistent. Although, I don't have the context.

 

[Joe4]

A VBA option might be a little trickier than I first thought. That is because Excel immediately drops the trailing zeroes once you enter the values.
So to try to use that value to determine the number of decimals after data entry is too late.
You would probably need something like an entry form, or entering values via VBA through input boxes.

I think the Text option recommended in the first reply is probably the best way to go.

 

[Scott Huish]

A VBA option might be a little trickier than I first thought. That is because Excel immediately drops the trailing zeroes once you enter the values.
So to try to use that value to determine the number of decimals after data entry is too late.
You would probably need something like an entry form, or entering values via VBA through input boxes.

I think the Text option recommended in the first reply is probably the best way to go.

It can be done in VBA, but you have to change the Target cell to Text first. The only thing I haven't been able to figure out is if you select a cell in the range but don't change anything that would leave it as text.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range, x As Integer
Set d = Intersect(Target, Range("A1:A1000"))
If d Is Nothing Then Exit Sub
If d.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
    If d <> "" Then
        x = InStr(1, d.Text, ".")
        x = Len(Mid(d.Text, x + 1))
        d = Val(d)
        d.NumberFormat = "0." & String(x, "0")
    End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set d = Intersect(Target, Range("A1:A1000"))
If d Is Nothing Then Exit Sub
d.NumberFormat = "@"
End Sub

 

[Joe4]

It can be done in VBA, but you have to change the Target cell to Text first. The only thing I haven't been able to figure out is if you select a cell in the range but don't change anything that would leave it as text.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim d As Range, x As Integer
Set d = Intersect(Target, Range("A1:A1000"))
If d Is Nothing Then Exit Sub
If d.CountLarge > 1 Then Exit Sub
Application.EnableEvents = False
    If d <> "" Then
        x = InStr(1, d.Text, ".")
        x = Len(Mid(d.Text, x + 1))
        d = Val(d)
        d.NumberFormat = "0." & String(x, "0")
    End If
Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set d = Intersect(Target, Range("A1:A1000"))
If d Is Nothing Then Exit Sub
d.NumberFormat = "@"
End Sub

Yeah, I started trying to go down track too, and things got a little messy.
When they did not respond, I did not think it was worth the effort to continue.