Representing numbers with K, M, G sufix using a macro

  • Thread starter Thread starter Farooq Sheri
  • Start date Start date
F

Farooq Sheri

I need help with code to convert a number, for example 2000000, into 2 M or
2000000000 into 2 G etc.

Thanks.

Farooq
 
dim s as string

select case application.worksheetfunction.log10(x)
case >=9: s = round(x/1e9,2) & "G"
case >=6: s = round(x/1e6,2) & "M"
case >=3: s = round(x/1e3,2) & "K"
case else: s = cstr(x)
end select

(untested)
 
You can use this event code to do that (it also includes a T for terabytes,
just in case). To implement this 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"
ElseIf Target.Value < 999500 Then
Target.NumberFormat = "0.000, \K"
ElseIf Target.Value < 999500000 Then
Target.NumberFormat = "0.000,, \M"
ElseIf Target.Value < 999500000000# Then
Target.NumberFormat = "0.000,,, \G"
Else
Target.NumberFormat = "0.000,,,, \T"
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 (use Cells instead of a Range call if you want this
functionality to apply everywhere on the worksheet). Also, since you didn't
mention it, I chose to display 3 decimal places after the decimal point for
numbers with a suffix. After implementing the above code, 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
 
By the way, I chose to add the suffixes using NumberFormat in order to leave
the numbers in the cells as numbers so that they could continue to be used
in calculations.
 
Function NumSuffix(MyNum as Long) as String
Dim Result as String
if MyNum Mod 1E12 = 0 then Result = CStr(Mynum/1E12) & " T"
if MyNum Mod 1E9 = 0 then Result = CStr(Mynum/1E9) & " G"
if MyNum Mod 1E6 = 0 then Result = CStr(Mynum/1E6) & " M"
if MyNum Mod 1E3 = 0 then Result = CStr(Mynum/1E3) & " K"
NumSuffix = Result
End Function
 
You'll have to reverse the order of those ifs...

2,000,000,000,000 mod 1E12 = 0 so result = 2T
2,000,000,000,000 mod 1E9 = 0 so result = 2000G
etc.

Sam
 
Back
Top