Indian Currency Format

Hi

You can use a custom format (Format Cell...|Custom) dialog to enter your own format: ##,##,##,##,###.
Hope this will help.

Re: Help with Custom Formats..Please!!!!

mr anand is right it is not working

any please advice how to bring this format

Re: Help with Custom Formats..Please!!!!

A Google search showed this Custom Number Format

[>=10000000]#\,##\,##\,##0.00;[>=100000]#\,##\,##0.00;##,##0.0

Re: Help with Custom Formats..Please!!!!

...Or a Custom Number Format Like
[>=10000000]#\,##\,##\,##;[>=100000]#\,##\,##;#,###

For no decimals.

Re: Help with Custom Formats..Please!!!!

but when i used

[>=10000000]#\,##\,##\,##0.00;[>=100000]#\,##\,##0.00;##,##0.0

and typed 22121121212

i got 2212,11,21,212.00

starting four digit should have been 22,12.

why it stopped formatting after 7 digit from right?

in other words, all the number should have same length to use this format. if the lenght is more then the last digits will not get formatted,

Re: Indian Currency Format

Hi,

Here is a contribution from Mr. Yogi Anand and a UDF from parry.
Custom cell format :
=REPLACE(TEXT(B2,"##"",""##"",""##"",""##"",""##"",""##"",""###"),1,6-INT((LEN(B2)>3)+(LEN(B2)-4)/2),"")
By Yogi.

Here is the UDF by Parry

Private Sub Worksheet_Change(ByVal Target As Range) 
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

Kris

Re: Indian Currency Format

Quote

Here is the UDF by Parry

Sorry! It's not a UDF.

Kris

Re: Indian Currency Format

If you change your Regional Settings in Windows, it works without any code (Start>Control Panel)

cnu nagula