Use 100 separator inspite of 1000 separator in excel

Montu

unread,

Nov 23, 2007, 12:43:00 AM11/23/07

to

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

Gary''s Student

unread,

Nov 23, 2007, 6:07:01 AM11/23/07

to

--
Gary''s Student - gsnu200758

Gary''s Student

unread,

Nov 23, 2007, 6:09:00 AM11/23/07

to

Format > Cells... > Number > Custom:

0\,00\,000.00

Montu

unread,

Nov 23, 2007, 7:41:04 AM11/23/07

to

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

Gary''s Student

unread,

Nov 23, 2007, 7:53:01 AM11/23/07

to

May you also have a nice day.

--
Gary''s Student - gsnu200758

shaqil

unread,

Nov 24, 2007, 12:07:26 AM11/24/07

to

On Nov 23, 5:53 pm, Gary''s Student

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.

Héctor Miguel

unread,

Nov 24, 2007, 11:24:56 PM11/24/07

to

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.

barun sinha

unread,

Mar 16, 2011, 2:16:04 AM3/16/11

to

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/aspnet/12dfc2ae-d796-40e8-9762-572bdaa7b637/profiling-and-optimizing-vba.aspx

barun sinha

unread,

Mar 16, 2011, 2:16:06 AM3/16/11

to

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/aspnet/9ab977de-5502-495e-9315-b2f78d73cdf8/task-parallel-library--continuation-tasks-net-framework-40.aspx

Gord Dibben

unread,

Mar 16, 2011, 12:10:58 PM3/16/11

to

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

Gord Dibben MS Excel MVP

joeu2004

unread,

Mar 16, 2011, 3:45:52 PM3/16/11

to

On Mar 15, 11:16 pm, barun sinha <bksinh...@gmail.com> 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.