Custom Column Format for Terabytes

[wmotley1]

I am using Excel from Office 2010 Standard. I have found this format, [<1000000]0.0," KB";[<1000000000]0.0,," MB";0.0,,,"GB" to show KB, MB and GB. Is there any way to include TB (terabyte). I have tried numerous combinations of the above to no avail. Thank you, Wayne.

 

[jamcall]

I think you're limited to two conditions in a custom number format... You could drop the KB and have MB, GB, or TB.

Cheers,
~ Jim

 

[Rick Rothstein]

I am using Excel from Office 2010 Standard. I have found this format, [<1000000]0.0," KB";[<1000000000]0.0,," MB";0.0,,,"GB" to show KB, MB and GB. Is there any way to include TB (terabyte). I have tried numerous combinations of the above to no avail. Thank you, Wayne.

From a previously posted answer of mine to an almost identical question...

Format cells for KB, MB, GB, etc....

You can't do that with straight cell formatting, but you can implement it using event code. Right click the tab at the bottom of the worksheet and select View Code from the popup menu that appears, then copy/paste the following into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range) 
  If Not Intersect(Target, Range("C:C")) Is Nothing Then 
    If Target.Value < 1000 Then 
      Target.NumberFormat = "0 \B" 
    ElseIf Target.Value < 999500 Then 
      Target.NumberFormat = "0.000, \K\B" 
    ElseIf Target.Value < 999500000 Then 
      Target.NumberFormat = "0.000,, \M\B" 
    ElseIf Target.Value < 999500000000# Then 
      Target.NumberFormat = "0.000,,, \G\B" 
    Else 
      Target.NumberFormat = "0.000,,,, \T\B" 
    End If 
  End If 
End Sub

Since you didn't tell us what cells, I assumed Column "C" (change the Range inside the Intersect function to the cell range you want to have this functionality). After doing this, any numbers entered into those cells will adopt the number format you requested. Note that existing numbers will not change unless re-entered. You can do that one at a time or you can select all the existing numbers and execute this code from the Immediate Window...

Selection.Formula = Selection.Formula

 

[wmotley1]

From a previously posted answer of mine to an almost identical question...

Format cells for KB, MB, GB, etc....

You can't do that with straight cell formatting, but you can implement it using event code. Right click the tab at the bottom of the worksheet and select View Code from the popup menu that appears, then copy/paste the following into the code window that opened up...

Private Sub Worksheet_Change(ByVal Target As Range) 
  If Not Intersect(Target, Range("C:C")) Is Nothing Then 
    If Target.Value < 1000 Then 
      Target.NumberFormat = "0 \B" 
    ElseIf Target.Value < 999500 Then 
      Target.NumberFormat = "0.000, \K\B" 
    ElseIf Target.Value < 999500000 Then 
      Target.NumberFormat = "0.000,, \M\B" 
    ElseIf Target.Value < 999500000000# Then 
      Target.NumberFormat = "0.000,,, \G\B" 
    Else 
      Target.NumberFormat = "0.000,,,, \T\B" 
    End If 
  End If 
End Sub

Since you didn't tell us what cells, I assumed Column "C" (change the Range inside the Intersect function to the cell range you want to have this functionality). After doing this, any numbers entered into those cells will adopt the number format you requested. Note that existing numbers will not change unless re-entered. You can do that one at a time or you can select all the existing numbers and execute this code from the Immediate Window...

Selection.Formula = Selection.Formula

Rick, Thank you for above. (I pasted the above and saved as a .xlsm file. Is that all I needed to do)? But it is not working in my case. The cells in the columns I am using are either a product, =(B4*200) or a reference to another cell, =H4. The above solution works fine with numbers directly manually added. Is there a way to make this work for my particular needs? Thank you, Wayne.