Excel - How to format a number as B, KB, MB, or GB?

Asked By Square Peg

11-Jan-09 11:32 PM

Is there a way to format the value in a cell so that

if it's      it displays as

without changing the actual value in the cell?

12-Jan-09 12:28 AM

hi, !


I left-out the first section (nnnB), for the other three I used this custom format:
[<1e6]0.0," Kb";[<1e9]0.0,," Mb";0.0,,," Gb"

change the order if you use more "B" than "Gb"

hth,
hector.

12-Jan-09 07:50 AM

Alternatively, use Engineering Notation - scientific notation with powers of
3
http://people.stfx.ca/bliengme/ExcelTips/EngineeringNotation.htm
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

12-Jan-09 11:36 PM

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 =3D "0 \B"
ElseIf Target.Value < 999500 Then
Target.NumberFormat =3D "0.000, \K\B"
ElseIf Target.Value < 999500000 Then
Target.NumberFormat =3D "0.000,, \M\B"
ElseIf Target.Value < 999500000000# Then
Target.NumberFormat =3D "0.000,,, \G\B"
Else
Target.NumberFormat =3D "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...

12-Jan-09 08:53 AM

Hi there,
well it is n

Square Peg a écrit :

12-Jan-09 02:36 PM

When you copied that entire text (verbatim, by the way) from when I
originally posted that solution on December 14, 2008 (subject line
sentence...

numbers and execute this code from the Immediate Window..."

doesn't mean much unless you include the last line I originally posted,
namely, this line...

Selection.Formula = Selection.Formula

--
Rick (MVP - Excel)



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...

12-Jan-09 07:51 PM

Oooppps... Sorry. I forgot I already asked and was answered. I think I
got busy with the holidays and forgot to implement your solution. I will
give it a try now.

16-Jan-09 02:28 AM

l
ng

Use this formula and your problem will be solved : =3DIF
(A2>999499999,TEXT((A2/999500000),"0.00")&" GB",IF(A2>999499,TEXT
((A2/999500),"00.00")&" MB",IF(A2>999,TEXT((A2/1000),"00.00")
&"KB",A2&" B")))

15-Jan-09 02:17 PM

The OP asked how to "format the value in a cell so that if it's it displays
as...", not how to show it in that format in a different cell.

--
Rick (MVP - Excel)



Use this formula and your problem will be solved : =IF
(A2>999499999,TEXT((A2/999500000),"0.00")&" GB",IF(A2>999499,TEXT
((A2/999500),"00.00")&" MB",IF(A2>999,TEXT((A2/1000),"00.00")
&"KB",A2&" B")))