Number formatting

id4xl

New Member
Joined
Jan 7, 2006
Messages
6
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 !!!!!!!!!!!
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

RAM

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
Welcome to the Board!

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

RAM
 

id4xl

New Member
Joined
Jan 7, 2006
Messages
6
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

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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

Well-known Member
Joined
Aug 21, 2005
Messages
4,824

ADVERTISEMENT

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
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

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

Code:
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

New Member
Joined
Jan 7, 2006
Messages
6
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

Well-known Member
Joined
Oct 4, 2004
Messages
1,862
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

New Member
Joined
Jan 7, 2006
Messages
6
Dear RAM / LENZE,

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

Watch MrExcel Video

Forum statistics

Threads
1,122,303
Messages
5,595,324
Members
413,986
Latest member
Elizsk

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top