Custom Type to show Currency in Indian format in Excel 2003

[ravichandrans]

When a number 12456800 is typed, excel displays only in multiples of 3 digits -112,456,800 whereas there is no option to convert it as 11,24,56,800. Even if the figures are negative, it displays like (112,456,800) instead of (11,24,56,800).


Is there a common command to take care of the above problem and also for lower digits like 6,800 , 4,56,800 and so on.

 

[keymaster]

I have tried to solve this earlier. I think you can write UDF to format a number in Indian currency way. But the limitation of that would be, if you need to do something with the actual values (like adding them up etc.) then you need to convert the formatted value (would be text) to a number (simply remove all commas and convert to number).


I dont think there is inbuilt support for comma seperation at 3 decimals then at 2 subsequent. May be I am wrong...

 

[Hui]

Just use the Custom Format


##","##","##","###

 

[keymaster]

Hui... very cool.. however it has one small problem... it shows extra commas when the number is smaller than 8 digits.. but I guess that is better than doing the UDF thing.

 

[Green Squircle]

Another option (for Windows users) is to go to control panel > regional and language options > formats > customise > currency > digit grouping. If you temporarily change to "12,34,56,789" all currency formatted numbers will be shown in the Indian format.


Of course you need to remember to change this back when you're ready to work in the western number format again.


Another twist on Hui's theme would be to use ##,##,### as a custom format.

 

[nagovind]

Please correct if am wrong


If the above steps are followed for a group of number...but one no. is a single digit number...there it is displayed like " ,,5 "

Is there any way to avoid this


i think best way is control panel option

 

[oldchippy]

Hi,


Try Custom format


[>=10000000]##,##,##,##0;[>=100000] ##,##,##0;##,##0

 

[nagovind]

Oldchippy


Really amazing...even this is small tips but this value is v. great...for me

I know small minute thinks are always hidden...My question is from where these tips are mentioned i need to know the ORIGIN..please

 

[lavkeshbhatia]

Commas in number system are to indicate the places


Most of the time a space will suffice


Even using ## ## ### as a custom number format solves the problem in a simple manner


This can be extended for crores also just increase it to ## ## ## ### and no commas to worry about