thousand separator as indian style

[kamlakar]

hi every body

we in india to any number put comma as stated below

ie 15,13,10,565.00
fifteen crores thirteen lacs ten thousand five hundred sixty five only.
00,00,00,000.00

but excel we have only thousand separator.it separates the number after
every thousand.

can it possible in excel to formate number as per our style.
00,00,00,000.00

if possible
please guide me

kamlakar

 

[Andrew Poulsom]

Try the number format:

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

 

[kamlakar]

hi
mr Andrew Poulsom

thank u very much
its fine.
since so many year i was craving for this

once again lot of thanks
may the god bless you.

kamlakar

 

[kamlakar]

Your formula for indian comma style is working correctly.
but in case of if the cell content negative number it wont worked.

hence i added the same formula by adding , and minus sign
ie.

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

but this formula is not accepting in my spreedsheet.

please help me

kamlakar

 

[Krishnakumar]

Hi Kamlakar,

See this thread

HTH

 

[pmsocho]

Try the number format:

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

Thanks a lot Andrew!
This is what I was looking for a few hours. I had to create two different chart versions in the same Excel file with two different thousand's separators for USA and POLAND and I had to ensure that the numers in cells and within charts will be displayed AS I WANT for every person regardless his/her regional settings.
I achieved this with these custom formats:
PL: [>=1000000]###" "###" "###;[>=1000]###" "###;0
US: [>=1000000]###","###","###;[>=1000]###","###;0

Unfortunately this solutions works properly only for integers and values >=1000 and <=999999999 but its enough in my case

Anyone knows, if there is a way to use two different decimal separators in the same excel sheet?
I know how to get this task done by storing numbers as text but I am wondering if there is a better way.

 

[Mahesha]

Try the number format:

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

Thanks, Andrew! Your solution worked for me. I've added 2 improvisations:

1) With INR prefix: [$INR] [>=10000000]##\,##\,##\,###;[>=100000]##\,##\,###;#,##0
2) With Rupee sign prefix: [$₹-4009] [>=10000000]##\,##\,##\,###;[>=100000]##\,##\,###;#,##0

However, these formats don't seem to be compatible with negative values. If anyone can add those improvisations, please do so

 

[Andrew Poulsom]

Can't you change your Regional setting in Windows Control Panel to Guijarati(India) and use the Currency format?

 

[Mahesha]

Thanks for the idea, Andrew! Here's what finally worked:

Region and Language --> Location = India
Region and Language --> Formats --> Additional settings --> Numbers --> Digit grouping = 12,34,56,789
Region and Language --> Formats --> Additional settings --> Currency --> Currency Symbol = ₹
Region and Language --> Formats --> Additional settings --> Currency --> Digit grouping = 12,34,56,789

 

[Rick Rothstein]

Wow, it took you a looong time to get back to this thread.

I know you have a workable answer, which is good, of course, but I thought you might find these mini-blog articles of mine to be of some interest as well...

<!-- title / author block --> [h=3]Excel Number Format: Indian Style Comma Separation[/h]
<!-- title / author block --> [h=3]Follow-up to "Excel Number Format: Indian Style Comma Separation"[/h]