Excel VBA stop Auto Rounding

I have price column in which the prices are displayed in 3 decimals and 4 decimals places, for example 123.456 or 123.4357. So irrespective of 开发者_StackOverflowthe number of decimal places i want the value to be with only two decimals. So i am selecting the column and in the VBA i am using Selection.NumberFormat = "0.00" Which is resulting me the rounded value i.e when i format 123.456 and 123.4357 to 0.00 i am getting 123.46 and 123.44 but i want it to be 123.46 and 123.43.

So just wondering is there a way we can just trim the values instead of rounding. Please give me some sample examples.

Thank you in advance.

Excel has a built-in function, trunc, that should do the trick for you.

This is what I placed in a1:b2.

123.456  =trunc(A1,2)
123.4357 =trunc(A2,2)

This will display

123.456  123.45
123.4357 123.43

You could treat the result as a string and extract the number of characters you need as in:

dim a as double dim s as string a = 123.4357 s = MID(a,1,FIND(".",a))&MID(a,FIND(".",a)+1,2)

I don't believe there is a built in mask that will truncate without rounding, you could instead use another column containing =TRUNC(A1, 2).