Undo Removal of Trailing Zeros

[NetFlash]

Hello Guys,

I've stumbled upon a "feature" in Excel i can't exclude.
1000 0.188
2000 -0.401
3000 0.000
4000 -1.102

When you set the display format for numeric 2digits, Excel changes the Display not the underlying Value
Display Value
0.19 0.19
-0.40 -0.4
0.00 0
-1.10 -1.1

How do i STOP excel from removing trailing zeros.
I tried Tools>Options>Calculations>Precision as Displayed.
This works well with numbers not ending on 0. But numbers ending with 0 still get truncated.

I do Not want to convert the values to text. They have to stay numeric as they have to be rounded off automatically, and used to generate code.
so lets say if i fill in 0.40267 -> rouds off to 0.40
And what i get now is 0.4 which is unusable for the generated code.

Any ideas??

 

[R Pelle]

Please note that Display to two (or more digits) does not Round off your input. It only displays x digits.
Try the following

Excel Workbook
	A	B	C
1	You type in	Round off 4 digits	Apply display format to show 4 digits
2	0.4023456	0.4012	0.4012
3
4
5	You type in	Round off 2 digits	Apply display format to show 4 digits
6	0.4023456	0.4	0.4000
Sheet1

Excel 2010

Cell Formulas
Range	Formula
B2	=ROUND(A2,4)
B6	=ROUND(A6,2)
C2	=ROUND(A2,4)
C6	=ROUND(A6,2)

In Col C I applied Custom format 0.0000.

Rob

 

[NetFlash]

Hey Rob,

The problem is not the Display value(colC) but rather the actual rounded value(colB).
For my code to work, i need B6 (from your example) to be 0.40 instead of 0.4.
Excel automatically removes the trailing zero, and that is what i'm trying to counter.

 

[Rick Rothstein]

For my code to work, i need B6 (from your example) to be 0.40 instead of 0.4.
Excel automatically removes the trailing zero, and that is what i'm trying to counter.

I think you may be looking at this backwards... you shouldn't try to force a trailing 0 on a number (numbers cannot retain trailing zeroes, only text can); rather, you should adapt the location where you are using the number to work with the number itself. Tell us how you are trying to use these values, where that trailing zero is necessary, and we'll try to show you how to modify it to work with the number as is.

 

[pgc01]

For my code to work, i need B6 (from your example) to be 0.40 instead of 0.4.

Hi

This is not clear.

In your code how have you dimensioned the variable where you write the value in B6?

 

[NetFlash]

I have a table like this:
<table border="0" cellpadding="0" cellspacing="0" width="128"><colgroup><col style="width:48pt" width="64"> <col style="width:48pt" width="64"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt;width:48pt" height="17" width="64">Weight</td> <td class="xl25" style="width:48pt" width="64">Index</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl26" style="height:12.75pt" align="right" height="17">200</td> <td class="xl27" align="right">0.164</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">1000</td> <td class="xl28" align="right">0.187</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">2000</td> <td class="xl28" align="right">0.442</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">3000</td> <td class="xl28" align="right">-0.701</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">4000</td> <td class="xl28" align="right">-1.103</td> </tr> <tr style="height:12.75pt" height="17"> <td style="height:12.75pt" align="right" height="17">5000</td> <td class="xl28" align="right">0.002</td> </tr> </tbody></table>
And wish to create an output string to put in a single cell (F3) that looks like this:
1/Code/INS/200/0.16*1000/0.19*2000/0.44*3000/0.70-*4000/1.10-*5000/0.00

The entry starts with: 1/Code/INS
The values from one row are concated with: /
The values of the multiple rows are concated with: *
The (-) for any negative values should be placed behind the values
The decimal points for the index should be 2.

I do Not want to convert the values to text. They have to stay numeric as they have to be rounded off automatically, and are also used in formulas for other cells.
I tried Tools>Options>Calculations>Precision as Displayed.
This works well with numbers not ending on 0. But numbers ending with 0 still get truncated.

Sub Code
Dim Rng As Range, Dn As Range, Txt As String
Dim colB As String
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
Txt = "1/Code/INS/"
    For Each Dn In Rng
        colB = IIf(Dn.Offset(, 1) < 0, Abs(Dn.Offset(, 1)) & "-", Dn.Offset(, 1))
        Txt = Txt & Dn & "/" & colB & "*"
    Next Dn
[F3] = Left(Txt, Len(Txt) - 1)
End Sub

 

[pgc01]

Hi

Try formatting the value when you read it:

colB = Format(Abs(Dn.Offset(, 1)), "0.00") & IIf(Dn.Offset(, 1) < 0, "-", "")

 

[Rick Rothstein]

Try formatting the value when you read it:

colB = Format(Abs(Dn.Offset(, 1)), "0.00") & IIf(Dn.Offset(, 1) < 0, "-", "")

@PGC and NetFlash,

I think this would be a better way to code that line...

colB = Format(Dn.Offset(, 1), "0.00;0.00-;0.00")

@NetFlash,

This is what I meant about your approaching the problem backwards... you don't change the values at the source, rather, you transform a copy of them within your code to make it look like you want/need.

 

[pgc01]

@PGC and NetFlash,

I think this would be a better way to code that line...

colB = Format(Dn.Offset(, 1), "0.00;0.00-;0.00")

Much better (and also not dumb ).

BTW, you don't need the third section, zero defaults to the positives.

colB = Format(Dn.Offset(, 1), "0.00;0.00-")

 

[NetFlash]

Thx 4 all the solutions guys. You all really are MVP.
I'm new to VBA and don't quite get the short notation yet,
but i'll look it up.

@Rick
I understand now what you mean with changing the format just before you use it for code generation, and not changing the source.

@PGC, Rick and all others
Thans 4 the support.