Formatting numbers

[abanerji]

I give up looking for a solution. Google failed me on this one!

I am trying to format a number -123456789 in Indian comma style with the last three digits not entered.

Meaning, I want it to appear as (12,34,56) with the heading declaring "figure in thousands". I shall manually enter the number as -123456 only, and leave out 789.

Please help, without VBA.

 

[Ashhu]

Hi, See attached image. this what you are looking?

 

[Ashhu]

or Like this?

 

[abanerji]

Thank you, Ashhu.

However, the custom number format works precisely for a specific number of digits. Else, the format returns superfluous leading commas.

The attached screenshot explains.

In other words, as things stand now, I cannot do a global or range operation with this custom number format. I need to tweak it in every cell according to the digits (4 or 6 or anything else) entered in that particular cell.

Any thought how to make this format a global one, which can be applied irrespective of the number of digits please?

 

[Ashhu]

hmm...you are right AB!
I will check how can we resolve this.

 

[abanerji]

Thank you

 

[abanerji]

Hi Ashhu, Any luck with a solution please?

 

[Nebu]

Hi:

You may want to go through the following blink

https://www.quora.com/How-do-you-ch...n-Excel-from-millions-to-lakhs-and-vice-versa

Thanks

 

[NARAYANK991]

Hi ,

The cell format string has only 3 sections for numbers ; if the variation in your data is 3 or less , you can probably make use of custom cell formats.

If the variations are more than 3 , the only possibility is to use Conditional Formatting , since this can accommodate more than 3 rules.

However , whether what you want done , can be done using CF is something else ; I cannot comment on that.

Narayan

 

[abanerji]

Thanks Nebu for the link.

It still does not solve my problem.

a) Changing the default global regional settings upsets my other excel workbooks. I want to use custom formula for a single book.

b) The two custom formats provided, viz., "[>=10000000]##\,##\,##\,##0;[>=100000] ##\,##\,##0;##,##0" and "[>=10000000]"RS "##\,##\,##\,##0;[>=100000]"RS " ##\,##\,##0;"RS "##,##0" does not help. I tried.

Looks like there is no easy solution.

Hi:

You may want to go through the following blink

https://www.quora.com/How-do-you-ch...n-Excel-from-millions-to-lakhs-and-vice-versa

Thanks

 

[abanerji]

Hi Narayank, I have never used conditional formatting earlier. So, did a quick read http://www.techrepublic.com/blog/10...to-use-excels-conditional-formatting-feature/

Although this provides ten ways, it seems that CF is more about highlighting, and not so much about getting a specific number format displayed.

Hi ,

The cell format string has only 3 sections for numbers ; if the variation in your data is 3 or less , you can probably make use of custom cell formats.

If the variations are more than 3 , the only possibility is to use Conditional Formatting , since this can accommodate more than 3 rules.

However , whether what you want done , can be done using CF is something else ; I cannot comment on that.

Narayan

 

[NARAYANK991]

Hi ,

Please upload your workbook , with as much variety of data as possible , and alongside , please manually enter what should be displayed.

Narayan

 

[abanerji]

Thanks Narayan.

I am not sure why I am unable to upload my excel test file. So, I am attaching a screenshot. Hope it helps.

My issue is as below.

"I am trying to format a number -123456789 in Indian comma style with the last three digits not entered.
Meaning, I want it to appear as (12,34,56) with the heading declaring "figure in thousands". I shall manually enter the number as -123456 only, and leave out 789."

 

[NARAYANK991]

Hi ,

What is important to have all of the variety of your data ; doing anything for one number is not really relevant.

If you can post as much data as possible , encompassing all of the variations , along with how you want those numbers displayed , it will give everyone an idea of whether what is expected is possible or not.

Narayan

 

[abanerji]

First, I still do not understand why I cannot upload the excel file.

Actually, in this case, data variety is not much. I am presenting below, textually, what I intend to enter, and how excel should display it.
My entries are in thousands; so, a number 123456789 would be entered as 123456 by me. Here are four examples, with the last three digits truncated.

Entry 123456 Required display 12,34,56
Entry -123456 Required display (12,34,56)
Entry 1234 Required display 12,34
Entry -1234 Required display (12,34)

Hi ,

What is important to have all of the variety of your data ; doing anything for one number is not really relevant.

If you can post as much data as possible , encompassing all of the variations , along with how you want those numbers displayed , it will give everyone an idea of whether what is expected is possible or not.

Narayan

 

[NARAYANK991]

Hi ,

See if this is OK.

Narayan

 

[abanerji]

Hi Narayan, Thanks.

Yes this is how it should be. But, I am confused. When I check the format, it appears as "General". I am attaching the screenshot.

What is the secret please?

Hi ,

See if this is OK.

Narayan

 

[Prasang Singh parihar]

Narayan did it correctly. Thanks to him.
Anandanath, see the Custom format, it's there.
I have attached a pic for your reference which I got after
1. Selecting one of the filled up cells
2. Checking Conditional Formatting Rules

Hope this helps and serves the purpose.

-
Pasha

 

[abanerji]

Thanks to both Narayan and Prasang.

Unfortunately, I am still going wrong somewhere. May be because I have never used conditional formatting before.

Since I am still unable to upload my excel file here, I have uploaded to another site. The link is http://www.filedropper.com/croresformatting

I shall be obliged if Narayan / Prasang / or anybody else takes a look at this file, and tells me how the correct formatting should be done. I have highlighted the cells where the display does not appear as intended.
-------------------------------------------------------------------------------
Mod edit:
File included

 

[NARAYANK991]

Hi ,

See the file now.

The range B14:D20 is the one which has been corrected.

Narayan

 

[abanerji]

Hi Narayan, Thanks a ton!

It has been a great learning, and hopefully I shall be able to use CF extensively in future

You are awesome