write at exceltoexplore@gmail.com : Report Automation|Dashboard in Excel| Provide Excel consulting through macro (VBA) automation |Financial Modeling | Ethical Hacking

Thursday, 23 February 2012

Excel : Format Number Lakh Separator

Excel allows only thousand's separator not "lakh" separator
There is difference between Excel Numbering format between Indian & Western system
Both systems agree till 10,000. After that the 'problems' start.

10^5 = Hundred Thousand = 1 Lakh
10^6 = Million = 10 Lakh
10^7 = 10 Million = 100 Lakh = 1 Crore
10^8 = 100 million = 10 Crore
10^9 = 1 Billion = 100 Crore = 1 Arrab
10^10 = 10 Billion = 10 Arrab
10^11 = 100 Billion = 100 Arrab = 1 Kharrab
10^12 = 1 Trillion = 10 Kharrab
In India numerical value for lakh is 100,000 not 100,000 (one hundred thousands)


Here's a useful Excel tip for users in India and other places that use number formats like 100,00,00,000 instead of 1,000,000,000:

With 2 decimals:

[>99999]##\,##\,##0.00;[<-99999.99]-##\,##\,##0.00;##,##0.00

Without decimals:

[>99999]##\,##\,##0;[<-99999.99]-##\,##\,##0;##,##0

For Lakhs and crores (+ve)

[>9999999]##\,##\,##\,##0.00;[>99999]##\,##\,##0.00;##,##0.00

For Lakhs and crores (-ve)

[<-9999999](##\,##\,##\,##0.00);[<-99999](##\,##\,##0.00);##,##0.00

Or

For numbers upto 100 Crores or 1 Arab..

[>9999999]##\,##\,##\,###;[>99999]#\,##\,###;###,###


There is an easier way to change the global settings from Settings > Control Panel > Regional Settings > Numbers, but that will affect all your Excel sheets and the change will be visible only on your system and not on your files which you send to others.


with Vb Code (By : Graham aka parry )


Private Sub Worksheet_Change(ByVal Target As Range)
     '//Written by parry
    Dim c
    If Target.Cells.Count = 1 Then
        Select Case Target.Value
        Case Is >= 1000000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
        Case Is >= 10000000
            Target.Cells.NumberFormat = "##"",""00"",""00"",""000.00"
        Case Is >= 100000
            Target.Cells.NumberFormat = "##"",""00"",""000.00"
        Case Else
            Target.Cells.NumberFormat = "##,###.00"
        End Select
    Else
        For Each c In Target
            Select Case c.Value
            Case Is >= 1000000000
                c.NumberFormat = "##"",""00"",""00"",""00"",""000.00"
            Case Is >= 10000000
                c.NumberFormat = "##"",""00"",""00"",""000.00"
            Case Is >= 100000
                c.NumberFormat = "##"",""00"",""000.00"
            Case Else
                c.NumberFormat = "##,###.00"
            End Select
        Next c
    End If
End Sub





No comments:

Post a Comment