Comma Separation Indian String with Dr. Cr.

**aronyo** · 11-27-2010, 04:20 AM

Hello..

I use to use the following string for comma separation in Indian format [such as 00,00,00,000.00] :

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

Unfortunately, this does not work for the negative numbers. Further I need to add a suffix, for (+) numbers Dr. and for (-) numbers Cr. so that it Looks like,

for 912345678.90 ==> 91,23,45,678.90 Cr.

and for -912345678.90 ==> 91,23,45,678.90 Dr.

Someone may help please ?

**squiggler47** · 11-27-2010, 05:57 AM

http://chandoo.org/wp/2010/07/26/ind...xcel/#comments

setting up your pc as described should solve your problems! then you and just add " cr" and "dr" to the end of the inbuilt formats!

**Marcol** · 11-27-2010, 10:42 PM

Hmmm?

This one has me intrigued, nay puzzled, there seems to be many variations of this custom format, but I can't find one so far that handles negative numbers....

All work easily enough for +ve, so why not -ve?

This is the best compromise I have managed so far

Please Login or Register  to view this content.

Note that there are double spaces between the #s

The problem is surpressing commas as the numbers decrease.

If you want to show a minus with negative numbers

Please Login or Register  to view this content.

This workbook should help to better explain what I mean.

**squiggler47** · 11-28-2010, 03:58 AM

By changing the number format settings in windows, it should take care of the negative problem! As on the Chandoo link I provided!

**Marcol** · 11-28-2010, 06:43 AM

Agreed squiggler, but that was not the point I was trying to get across.

I did look at the link you provided, and the formatting formula provided there also failed.

There is something missing in the negative number formatting arguement, that I cannot fathom. Crudely speaking there should be no difference in syntax between +ve and -ve arguements.

This broad rule seems to fail when thousands separator suppression \, (back-slash comma) is employed.

I don't know what happens when the originators' machine is set to "Indian Notation" and a file is distributed to a more conventionallly based machine, I suspect there will be other problems.