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

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

Thanks.

Farooq

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

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)


"Farooq Sheri" wrote in message
...
I need help with code to convert a number, for example 2000000, into 2 M or
2000000000 into 2 G etc.

Thanks.

Farooq

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

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

--
Rick (MVP - Excel)


"Farooq Sheri" wrote in message
...
I need help with code to convert a number, for example 2000000, into 2 M or
2000000000 into 2 G etc.

Thanks.

Farooq

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

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.

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
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

--
Rick (MVP - Excel)


"Farooq Sheri" wrote in message
...
I need help with code to convert a number, for example 2000000, into 2 M
or
2000000000 into 2 G etc.

Thanks.

Farooq

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

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
--
If this helps, please click "Yes"
<<<<<<<<<<<<


"Farooq Sheri" wrote:

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

Thanks.

Farooq

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

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

"BSc Chem Eng Rick" wrote:

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
--
If this helps, please click "Yes"
<<<<<<<<<<<<


"Farooq Sheri" wrote:

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

Thanks.

Farooq