Number formatting

[id4xl]

Hi

I have a number :

35678329

I want it to appear in xl as below

3,56,78,329

meaning 3 crores 56 lakhs 78 thousands 3 hundred and 29.

We have crores and lakhs in india.. and are comfortable with that than millions and billions..

how do i format the cell to display the commas like this?

i tried changing the # formatting through control panel - regional setting - standards & formats (English UK) - customise - and selected 12,34,56,789

still doesn't work

pl dont tell me i need to get used to millions and bns ...........help !!!!!!!!!!!

 

[RAM]

Welcome to the Board!

Why don't you just format the cell to Currency or Accounting and display no Symbol?

RAM

 

[id4xl]

Hi

I am looking for an option where I don't need to type the commas manually and some kind of cell formatting will give it the way I want.

If I type "35678329" and click on Currency / Accounting, it displays
35,678,329.00 where again the comma is not where I want.

 

[Joe Was]

Excel does not do data masks!
And will only format numbers or dates in custom formats using western conventions, such as commas every three digits for values.

But you can do a Custom number format using dashes to get what you want: ##-##-##-###

Or

do it with VBA!

 

[venkat1926]

try this

put this custom format
if it more than one lakh
[>=100000]##\,##\,##0.00;##,##0


if more than 1 crore
[>1000000]##\,##\,##\,##0;##,##,##0

12345678 becomes
1,23,45,678

 

[Legacy 55362]

http://www.microsoft.com/mspress/books/sampchap/5020a.asp

 

[lenze]

For new data entry, you can use the Worksheet Change event to display the required format

Private Sub Worksheet_Change(ByVal Target As Range)
    'Limit to a specific target
    If Target.Column <> 1 Then Exit Sub
    'Make sure only one cell was changed
    If Target.Count > 1 Then Exit Sub
    Application.EnableEvents = False
    Select Case Len(Target)
    Case Is = 8
    With Target
    .Value = (Left(.Value, 1) & "," & Mid(.Value, 2, 2) & "," & Mid(.Value, 4, 2) & "," & Right(.Value, 3))
    End With
    Case Is = 7
    With Target
    .Value = Left(.Value, 2) & "," & Mid(.Value, 2, 2) & "," & Right(.Value, 3)
    End With
    Case Is = 6
    With Target
    .Value = Left(.Value, 1) & "," & Mid(.Value, 2, 2) & "," & Right(.Value, 3)
    End With
    Case Is = 5
    With Target
    .Value = Left(.Value, 2) & "," & Right(.Value, 3)
    End With
    Case Is = 4
    With Target
    .Value = Left(.Value, 1) & "," & Right(.Value, 3)
    End With
    Case Is < 4
    With Target
    .Value = .Value
    End With
    End Select
    Application.EnableEvents = True
End Sub

For existing cells, modify the code and run a loop from a regular module

HTH

lenze

 

[id4xl]

Dear all,

I must thank you for the replies.

But unfortunately, I dont know anything about coding and Venkat's suggestion "[>1000000]##\,##\,##\,##0;##,##,##0 .........." doesn't work either...

thanks anyways...

 

[RAM]

Hi,

lenze's code work, for what I can see, but only in column A, as it is written.

Copy lenze's code above, right-click on the sheet tab in Excel and select View Code. Paste in the code and close VBE (Visual Basic Editor) with Alt+Q.

Now try again typing in your number combination in column A and it should work.

Joe Was Custom formatting work too if you settle with dashes (-) and not commas (,).

Post back for more help.

RAM

 

[id4xl]

Dear RAM / LENZE,

Thanks, that code worked, but yes only in column A.
Guess this is better than nothing at all.