Solved: Custom Formats

[sai_golden]

Hi All,

How to create custom formats in excel.

I have an xl sheet with numbers. I want the numbers to be displayed as millions or billions.

for e.g. 1000000 should be displayed as 1.00mn

Is there any post / articles which can be refered?

Please help:(

[mdmackillop]

It could be changed using a macro, but the cell would not be the "real" value and could not be used in calculation without adjustment


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Exits
Application.EnableEvents = False
If Target.Column <> 3 Then Exit Sub
Select Case Len(Target)
Case Is < 7
'do nothing
Case Is < 10
Target = Target / 10 ^ 6
Target.NumberFormat = "0.00 ""Mn"""
Case Is < 13
Target = Target / 10 ^ 9
Target.NumberFormat = "0.00 ""Bn"""
End Select
Exits:
Application.EnableEvents = True
End Sub

[mdmackillop]

To change existing numbers

Sub Macro1()
Dim cel As Range
For Each cel In Selection
Select Case Len(cel)
Case Is < 7
'do nothing
Case Is < 10
cel = cel / 10 ^ 6
cel.NumberFormat = "0.00 ""Mn"""
Case Is < 13
cel = cel / 10 ^ 9
cel.NumberFormat = "0.00 ""Bn"""
End Select
Next
End Sub

[sai_golden]

Hi,

I tried the above code by putting it in sheet 1 but nothing seems to be happening.

How to make the code work

[mdmackillop]

Post 2 will convert data as it is entered. To change existing values, see post 3

[Bob Phillips]

Hi All,

How to create custom formats in excel.

I have an xl sheet with numbers. I want the numbers to be displayed as millions or billions.

for e.g. 1000000 should be displayed as 1.00mn

Is there any post / articles which can be refered?

Please help:(


0.00,,"mn"

[mdmackillop]

:clap2::clap2: Never thought of that!

[Bob Phillips]

For millions and billions

[<1000000000]0.00,,"mn";[>=1000000000]0.00,,,"Bn"

[sai_golden]

Hi All,

Thanks for your contributions. It Worked.

:bow: :bow: :bow: