Question related to Number format

%3CLINGO-SUB%20id%3D%22lingo-sub-2227243%22%20slang%3D%22en-US%22%3EQuestion%20related%20to%20Number%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227243%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3EI%20would%20to%20add%20the%20below%20comma%20separation%20format%20in%20the%20numeric%20value.%3C%2FP%3E%3CP%3EPresently%20I%20have%201%2C000%2C000%2C000.00%3C%2FP%3E%3CP%3EBut%20I%20would%20like%20to%20add%20%3CSTRONG%3E100%2C00%2C00%2C000.00%3C%2FSTRONG%3E%20in%20this%20format...%3F%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOR%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20should%20i%20write%20in%26nbsp%3B%3CSTRONG%3EFormat%20Cells%3C%2FSTRONG%3E%20-%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(2240).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F265992i09CEF8BCB655E3BC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(2240).png%22%20alt%3D%22Screenshot%20(2240).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help..%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2227243%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2227440%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Number%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227440%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F811137%22%20target%3D%22_blank%22%3E%40Zan_Hanifee%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20want%20to%20use%20the%20Indian%20Numbering%20System%2C%20you%20must%20select%20the%20corresponding%20digit%20grouping%20setting%2012%2C34%2C56%2C789%20in%20the%20Currency%20and%20Number%20tabs%20of%20Customize%20Format%20(press%20%3CSTRONG%3EWindows%20key%2BR%3C%2FSTRONG%3E%2C%20type%20%3CSTRONG%3Econtrol%20intl.cpl%3C%2FSTRONG%3E%20and%20press%20%3CSTRONG%3EEnter%3C%2FSTRONG%3E%20or%20click%20%3CSTRONG%3EOK%3C%2FSTRONG%3E%2C%20then%20click%20%3CSTRONG%3EAdditional%20settings...%3C%2FSTRONG%3E)%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0224.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266011iA2917FFF44CB6B76%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0224.png%22%20alt%3D%22S0224.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E1000000000%20will%20then%20be%20displayed%20as%201%2C00%2C00%2C00%2C000.00%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0225.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F266012i02CF603B3EF65B21%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22S0225.png%22%20alt%3D%22S0225.png%22%20%2F%3E%3C%2FSPAN%3E%E2%80%83%3C%2FP%3E%0A%3CP%3EAs%20far%20as%20I%20know%2C%20there%20is%20no%20way%20to%20make%20Excel%20format%20it%20as%20100%2C00%2C00%2C000.00%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2227455%22%20slang%3D%22en-US%22%3ERe%3A%20Question%20related%20to%20Number%20format%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2227455%22%20slang%3D%22en-US%22%3Ei%20dont%20think%20its%20possible%20as%20%2C%20is%20thousand%20seperator%3C%2FLINGO-BODY%3E
Frequent Contributor

Hello Everyone,

I would to add the below comma separation format in the numeric value.

Presently I have 1,000,000,000.00

But I would like to add 100,00,00,000.00 in this format...???

 

OR

 

What should i write in Format Cells

Screenshot (2240).png

 

Please help...??

 

 

11 Replies

@Zan_Hanifee 

If you want to use the Indian Numbering System, you must select the corresponding digit grouping setting 12,34,56,789 in the Currency and Number tabs of Customize Format (press Windows key+R, type control intl.cpl and press Enter or click OK, then click Additional settings...)

 

S0224.png

1000000000 will then be displayed as 1,00,00,00,000.00

 

S0225.png

As far as I know, there is no way to make Excel format it as 100,00,00,000.00

i dont think its possible as , is thousand seperator

@Zan_Hanifee 

 

Cell formatting
Custom
Type: insert -> ### "." ## "," ## "," ### "." ##

 

Format Cell:

###"."##","##","###"."##

 

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here

 

 

 

great tip , "," never knew that in excel number formatting, where did you learn it?
You can also use \ as an escape character. Hence
#\,###\,##\,##\,##0.00
should also work. On the other hand
0 "dog biscuits";;"Out of dog biscuits"
is better with double-quotes!

@Peter Bartholomew 

It's a nice trick, but such formats have the disadvantage that they don't work well for all numbers:

 

S0227.png

I'm getting old and I'm still learning a lot (Solon)
... because I know that I don't know anything. Socrates)
It is always nice to learn something new, love the ignorant for their happiness. Nikolino

...old German school

Thanks for the good words :))

@Hans Vogelaar 

Good point!

[<100000]#,##0.00;[<10000000]#\,##\,##0.00;###\,##\,##0.00

will deal with smaller numbers but then the number formats would need to be combined with conditional formatting to accommodate very large numbers.

it worked! Thank you so much
It worked! Thank you so much
Once again it has been proven that there are many ways to find a solution in excel.
Rome had fewer routes available :)))

Let's enjoy Excel, with a cup of chocolate and two biscuits everyone :-))