Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default Use 100 separator inspite of 1000 separator in excel

Suppose A1 number is 120000.00. Now I want to separate it by 1000 & 100, i.e.
1,20,000.00. If I format the cell - Number & click on 1000 separatetor. It
show like 120,000.00. Is there any custom format to show 1,20,000.00 in excel
2003
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: Use 100 separator inspite of 1000 separator in excel

Yes, there is a custom format that you can use to show the number with 100 separators instead of 1000 separators in Excel 2003. Here are the steps to do it:
  1. Select the cell or range of cells that you want to format.
  2. Right-click on the selection and choose "Format Cells" from the context menu.
  3. In the "Format Cells" dialog box, click on the "Custom" category.
  4. In the "Type" field, enter the following custom format: #,##,##0.00
  5. Click on the "OK" button to apply the format to the selected cells.

Now, the number in the cell will be displayed with 100 separators instead of 1000 separators. For example, if the cell contains the number 120000.00, it will be displayed as 1,20,000.00 after applying the custom format.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Use 100 separator inspite of 1000 separator in excel


--
Gary''s Student - gsnu200758


"Montu" wrote:

Suppose A1 number is 120000.00. Now I want to separate it by 1000 & 100, i.e.
1,20,000.00. If I format the cell - Number & click on 1000 separatetor. It
show like 120,000.00. Is there any custom format to show 1,20,000.00 in excel
2003

  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Use 100 separator inspite of 1000 separator in excel

Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 50
Default Use 100 separator inspite of 1000 separator in excel

Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.

"Gary''s Student" wrote:

Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 11,058
Default Use 100 separator inspite of 1000 separator in excel

May you also have a nice day.
--
Gary''s Student - gsnu200758


"Montu" wrote:

Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.

"Gary''s Student" wrote:

Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 30
Default Use 100 separator inspite of 1000 separator in excel

On Nov 23, 5:53 pm, Gary''s Student
wrote:
May you also have a nice day.
--
Gary''s Student - gsnu200758

"Montu" wrote:
Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.


"Gary''s Student" wrote:


Format Cells... Number Custom:


0\,00\,000.00
--
Gary''s Student - gsnu200758


Sorry, but when i write 100 it shows 0,00,100.00 that looks odd. when
a figure should be less that 100,000.00 then it should look as usual.

Help required.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 434
Default Use 100 separator inspite of 1000 separator in excel

hi, shaqil !

"Gary''s Student" wrote:
Format Cells... Number Custom:
0\,00\,000.00


Sorry, but when i write 100 it shows 0,00,100.00 that looks odd.
when a figure should be less that 100,000.00 then it should look as usual...


you can "hide" leading commas if you "play" with custom formats (i.e.)

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

[9999999]##\,##\,##\,##0;[99999]##\,##\,##0;#,##0

for negative numbers try with the following custom number format:
[<-9999999]-##\,##\,##\,##0;[<-99999]-##\,##\,##0;#,##0

(however) you won't be able to mix positive/negative number using only one format
so, you might want to give a try an extra cell/column and ws-functions
(i.e.) assuming the number in 'A1':

=text(a1,rept("##\,",match(len(abs(a1)),{3;4;6;8})-1)&"##0")

hth,
hector.


  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Use 100 separator inspite of 1000 separator in excel

kindly send the formulae to use 100 separator instead of 1000 seperator in MS excel while numbering

On Friday, November 23, 2007 12:43 AM monisankar_dutt wrote:


Suppose A1 number is 120000.00. Now I want to separate it by 1000 & 100, i.e.
1,20,000.00. If I format the cell - Number & click on 1000 separatetor. It
show like 120,000.00. Is there any custom format to show 1,20,000.00 in excel
2003



On Friday, November 23, 2007 6:07 AM GarysStuden wrote:


--
Gary''s Student - gsnu200758


"Montu" wrote:



On Friday, November 23, 2007 6:09 AM GarysStuden wrote:


Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758



On Friday, November 23, 2007 7:41 AM monisankar_dutt wrote:


Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.

"Gary''s Student" wrote:



Submitted via EggHeadCafe
Profiling and Optimizing VBA
http://www.eggheadcafe.com/tutorials...izing-vba.aspx

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2
Default Use 100 separator inspite of 1000 separator in excel

kindly send the formulae to use 100 separator instead of 1000 seperator in MS excel while numbering

On Friday, November 23, 2007 12:43 AM monisankar_dutt wrote:


Suppose A1 number is 120000.00. Now I want to separate it by 1000 & 100, i.e.
1,20,000.00. If I format the cell - Number & click on 1000 separatetor. It
show like 120,000.00. Is there any custom format to show 1,20,000.00 in excel
2003



On Friday, November 23, 2007 6:07 AM GarysStuden wrote:


--
Gary''s Student - gsnu200758


"Montu" wrote:



On Friday, November 23, 2007 6:09 AM GarysStuden wrote:


Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758



On Friday, November 23, 2007 7:41 AM monisankar_dutt wrote:


Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.

"Gary''s Student" wrote:



On Wednesday, March 16, 2011 2:15 AM barun sinha wrote:


kindly send the formulae to use 100 separator instead of 1000 seperator in MS excel while numbering



Submitted via EggHeadCafe
Task Parallel Library | Continuation Tasks (.NET Framework 4.0)
http://www.eggheadcafe.com/tutorials...mework-40.aspx



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 621
Default Use 100 separator inspite of 1000 separator in excel

Kindly read the answer Gary's Student gave below regarding custom formatting.


Gord Dibben MS Excel MVP

On Wed, 16 Mar 2011 06:16:04 GMT, barun sinha wrote:

kindly send the formulae to use 100 separator instead of 1000 seperator in MS excel while numbering

On Friday, November 23, 2007 12:43 AM monisankar_dutt wrote:


Suppose A1 number is 120000.00. Now I want to separate it by 1000 & 100, i.e.
1,20,000.00. If I format the cell - Number & click on 1000 separatetor. It
show like 120,000.00. Is there any custom format to show 1,20,000.00 in excel
2003



On Friday, November 23, 2007 6:07 AM GarysStuden wrote:


--
Gary''s Student - gsnu200758


"Montu" wrote:



On Friday, November 23, 2007 6:09 AM GarysStuden wrote:


Format Cells... Number Custom:

0\,00\,000.00
--
Gary''s Student - gsnu200758



On Friday, November 23, 2007 7:41 AM monisankar_dutt wrote:


Many Many Thanks to you - Gray. Hoping get more support in future. Have nice
day.

"Gary''s Student" wrote:



Submitted via EggHeadCafe
Profiling and Optimizing VBA
http://www.eggheadcafe.com/tutorials...izing-vba.aspx

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,059
Default Use 100 separator inspite of 1000 separator in excel

On Mar 15, 11:16*pm, barun sinha wrote:
kindly send the formulae to use 100 separator instead of
1000 seperator in MS excel while numbering


You are responding to a thread that is nearly 3.5 years old. You do
not really think anyone is paying attention, do you? It would be
better to start a new thread of your own.

In any case, it would behoove you to read the entire thread. You
should have noticed that Gary's Student posted a corrected response
with the following suggestion:

Custom format 0\,00\,000.00

Personally, I don't think that is adequate since it can result in the
odd result ,,123 for smaller values. I would suggest:

Custom format [<1000]0.00;[<100000]0\,000.00;0\,00\,000.00

Unfortunately, that works only up to 99,99,999.99. Larger values will
have the form 123,45,678.99, which is probably not desirable.

Nothing you can do about it, though, at least not without an event
macro. The condition-value custom format is limited to conditions, at
least in XL2003.
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
In Excel, how do I change the argument separator in a function fr. Springsteel Setting up and Configuration of Excel 2 April 23rd 23 11:42 AM
How do we add a separator in Excel 2007 menu using VBA Suman Excel Discussion (Misc queries) 6 December 19th 06 07:54 AM
Save a csv or text file in Excel 2000 with choice of separator bazza1 Excel Discussion (Misc queries) 0 September 8th 06 04:12 PM
Excel separator pages aware Excel Discussion (Misc queries) 0 January 5th 06 06:39 PM
thousand separator R Kapoor Setting up and Configuration of Excel 5 January 5th 06 11:32 AM


All times are GMT +1. The time now is 12:07 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"