View Full Version : Solved: Custom Formats
sai_golden
06-12-2008, 09:56 AM
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
06-12-2008, 10:16 AM
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
06-12-2008, 10:26 AM
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
06-12-2008, 10:27 AM
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
06-12-2008, 10:34 AM
Post 2 will convert data as it is entered. To change existing values, see post 3
Bob Phillips
06-12-2008, 12:16 PM
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
06-12-2008, 03:31 PM
:clap2::clap2: Never thought of that!
Bob Phillips
06-13-2008, 01:13 AM
For millions and billions
[<1000000000]0.00,,"mn";[>=1000000000]0.00,,,"Bn"
sai_golden
06-14-2008, 03:46 AM
Hi All,
Thanks for your contributions. It Worked.
:bow: :bow: :bow:
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.