1

I have a cell with a value of 33,460.7971 which I would like to display as 33,460.79 without changing the actual value of the cell, I do need to maintain the 4 decimal places.

If I set the number format for the cell to show 2 decimal places it automatically rounds it to 33,460.80. If I use any of the functions such as TRUNC or ROUND it changes the value in the cell.

Is there a number format that I can use to achieve this?

Thanks

SarahH
  • 11
  • 2
  • 2
    I'm curious why you want this? As maybe there is an alternative workaround. It can be done with VBA, by creating a formula that dynamically sets the number format based on the value in the cell to a hardcoded string (E.g. `myRange.NumberFormat = """" & Int(myRange.Value*100)/100 & """"`) Or run a macro on workbook_save to apply the format. But I don't think it's possible to truncate with builtin number formats – Greedo Jun 26 '23 at 08:24
  • 1
    Is the formatted version meant to be for display only? You could potentially make a separate column with a formula that reads the original value as text and shortens the end character by X amount. I don't think I've ever seen a request for changing significant figures without rounding. – Omnishroom Jun 26 '23 at 13:03
  • *Is there a number format that I can use to achieve this?* No, there is not. You will need a different approach to accomplish this. – Ron Rosenfeld Jun 26 '23 at 18:27
  • 2
    @Omnishroom agree with separate column, but rather than text which counts characters from the left, I would do =int(B2*100)/100 . – zsalya Jun 27 '23 at 09:48
  • 2
    I agree with others that this seems a very strange request - perhaps the real solution is to educate whoever is asking you for this? – zsalya Jun 27 '23 at 09:49
  • 1
    Here is an [answer](https://stackoverflow.com/a/76560629) I posted not long ago for the Worksheet Change event. It looks like it could be utilized with a variation of the line suggested by Greedo in the first comment in the most inner `If` statement: `iCell.NumberFormat = """" & dValue & """"`. – VBasic2008 Jun 27 '23 at 10:14
  • @zsalya that makes way more sense to be honest. SarahH, I would recommend the answer he suggested. – Omnishroom Jun 28 '23 at 11:57

0 Answers0