• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Indian comma format for positive value for braces for negative values together

raghubir

New Member
Hi Team,
I am looking for a code using which the numbers should have commas as per indian standard but upto lakh value
e.g. 21234567890.50 should be visible as 2123,45,67,890.00 i.e. comma should be restricted upto lakh
VALUEPOSITIVE VALUE
20.00​
20.00​
100.42​
100.42​
5100.42​
5,100.42​
25100.42​
25,100.42​
225100.42​
2,25,100.42​
1025100.00​
10,25,100.00​
51025100.42​
5,10,25,100.42​
29401025100.42​
2940,10,25,100.42​

similarly, if the value is negative it should be visible with brackets

like (2123,45,67,890.50).


I tried various options but all works in silos but not as a single condition.

Please suggest the code.

thanks
 

vletm

Excel Ninja
raghubir
Did You do as a new member could do?
How to get the Best Results at Chandoo.org
  • Quickly search the Web and Chandoo.org for help before posting. It is quite possible that your problem has been solved by someone else.
>>> and >>>
You could find:
... have You tested it?
 

raghubir

New Member
raghubir
Did You do as a new member could do?
How to get the Best Results at Chandoo.org
  • Quickly search the Web and Chandoo.org for help before posting. It is quite possible that your problem has been solved by someone else.
>>> and >>>
You could find:
... have You tested it?
Yes Sir,
I tried but the answer is not available as a single solution... I have number (positive & Negative) in one columns itself and would like to apply this format as per above requirement mentioned. Which I didn't found. Please help me to find the right solution..Regards
 

vletm

Excel Ninja
raghubir
I asked two questions
the 1st was : What is Your as a single solution?
Okay, You noticed 2nd question.

There is a better and permanent solution (for Indian users):
Add Region as India and language as English(India). Then you can see the Indian comma format and Indian currency symbol inplace of dollar symbol. Default setting in US and English(US).
Go to Control panel > Region (search)
(tab) Format : Select English(India)
Click Laguage Preferences and add English(India) and move up to top.

Press OK
 

raghubir

New Member
raghubir
I asked two questions
the 1st was : What is Your as a single solution?
Okay, You noticed 2nd question.

There is a better and permanent solution (for Indian users):
Add Region as India and language as English(India). Then you can see the Indian comma format and Indian currency symbol inplace of dollar symbol. Default setting in US and English(US).
Go to Control panel > Region (search)
(tab) Format : Select English(India)
Click Laguage Preferences and add English(India) and move up to top.

Press OK
Hi
Regional setting are already English(India)
Answer to First question you asked is ... Instead of applying the format on each BLOCK (Row x Column) ... I am looking for a format type which If applied on the complete column itself, the numbers should be visible in the format as shown in excel sheet under the desired format column.

I hope I am able to clarify the
 

Attachments

raghubir

New Member
Hi
Regional setting are already English(India)
Answer to First question you asked is ... Instead of applying the format on each BLOCK (Row x Column) ... I am looking for a format type which If applied on the complete column itself, the numbers should be visible in the format as shown in excel sheet under the desired format column.

I hope I am able to clarify the
The system is automatically putting commas after every two 2 numbers post initial 3 numbers. While I am looking that after lac it should not propose any COMMA. (Please refer to the attached excel sheet.)
Regards
 

vletm

Excel Ninja
raghubir
Your: Instead of applying the format on each BLOCK (Row x Column) ...
You can apply those formats manually even the whole sheet if someone needs so.
Basic settings have to be done correctly ( You skipped that my question ) before any new formatting.
Many times those settings could solve many things.
Do everyone knows ... about lac ... ? I can guess.
Should those 'numbers' be useful for something else than to be visible after 'formatting'?

About Your sample file:
There are many negative values in G-column which expected results are empty cell.
 

raghubir

New Member
raghubir
Your: Instead of applying the format on each BLOCK (Row x Column) ...
You can apply those formats manually even the whole sheet if someone needs so.
Basic settings have to be done correctly ( You skipped that my question ) before any new formatting.
Many times those settings could solve many things.
Do everyone knows ... about lac ... ? I can guess.
Should those 'numbers' be useful for something else than to be visible after 'formatting'?

About Your sample file:
There are many negative values in G-column which expected results are empty cell.
Sir,
Lac means $ 1,00,000/- (One Lac).

I have done the basic setting [English(India)], but with that setting [English(India)] also I am getting the result as 22,12,34,56,789.00 while I am looking for the value as 2212,34,56,789.00

and if value is negative than should come in brackets (2212,34,56,789.00)

I hope now is clear...

regards
 

vletm

Excel Ninja
raghubir
With which numberformat do You get some those Your expected values?
I won't change my settings to any English(India).
You skipped my question: Should those 'numbers' be useful for something else than to be visible after 'formatting'?
All those skips only make delay ...
 

raghubir

New Member
raghubir
With which numberformat do You get some those Your expected values?
I won't change my settings to any English(India).
You skipped my question: Should those 'numbers' be useful for something else than to be visible after 'formatting'?
All those skips only make delay ...
Sir,

I have applied _ * #,##0.00_ ;( #,##0.00) ;_ * "-"??_ ;_ @_ format with which I got the result shown in COLUMN G of excel sheet. In row number 8, 9, 16 and 17 COMMA is appearing after each 2 Numbers post initial 3 numbers. While I am looking for format as shown in desired format column.

Those numbers are desired by management as they wish to view the excel sheet in this particular format only.

Regards
 

Attachments

Last edited:

vletm

Excel Ninja
raghubir
Was Those numbers are desired by management as they wish to view the excel sheet in this particular format only.
as an answer to my question Should those 'numbers' be useful for something else than to be visible after 'formatting'?
 

raghubir

New Member
raghubir
Was Those numbers are desired by management as they wish to view the excel sheet in this particular format only.
as an answer to my question Should those 'numbers' be useful for something else than to be visible after 'formatting'?
Yes, Sir, It is an answer to your question.
 

vletm

Excel Ninja
raghubir
.. and still
as I have asked You to verify few times
there are seven cells (I10:I15 and I18) which should show empty as Your Expected Results.

You seems to skip my above verification.
Here's my sample solution based some guesses.
 

Attachments

Last edited:

raghubir

New Member
raghubir
.. and still
as I have asked You to verify few times
there are seven cells (I10:I15 and I18) which should show empty as Your Expected Results.

You seems to skip my above verification.
Here's my sample solution based some guesses.
Hi Sir,

Your guesses are working perfectly fine for me...

thanks for your support.

Regards
Raghubir Singh
 
Top