Numerical format tricky quest

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
I like how General format uses the decimal place only when necessary. It only shows it when there are numbers to the right of the decimal place. If integer, it suppresses the decimal digits as well as the "dot." Can I achieve that in a mask?

For applications where vertical [right-]justification is not useful, I like seeing cents when they exist, and when they don't, just the integer (with commas ... or else I'd use General!). I think I like the mask
#,###,###,###.#############
because it only shows decimal places when needed. However it always shows the decimal point. Condemn me for being greedy or picayunish, but can I quash the little dot as well - just as General does?

****** side note start ***********
You can stop here ... but here's a bonus advanced question, in the vein of junk and bloat: Let's say I apply that format to every cell in a large workbook. Is that "bloaty?" In studying old Leo Hauser discussions and code it seemed that the issue was in the NUMBER of formats, not the number of times used. Are there any definitive conclusions regarding this? Note that product version probably affects the answer. I happen to be on XL03.

Moreover, I'm ignorant as to how Excel associates a "sheet-wide" characteristic vs. "row- or column-wise" vs. "cell by cell." Is it "cheap" if you select the entire sheet (with Control-A's), but significantly expensive when selecting the used range and then applying the format?
****** side note ***********
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
I think you're being too picky for number formats. Why not Worksheet_Change your way around it?

Code:
If int(target) = target then 'add desired format manipulation here
etc.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
So condemned. :oops:

What format would that be, that suppresses the decimal point?

BTW I avoid worksheet_change - for heavy duty workbooks it bogs things down - and I hate VBE debugging with it :unsure: But point taken.
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
What format would that be, that suppresses the decimal point?

I don't really have a good suggestion for you. For cosmetic reasons, you could text parse together the value to add your commas where you want; but, of course, you'd have issues if you tried to then use the numbers for calculations.

Like wanting different font colors for different parts of a string you've created via formula concatenation, this may just be one of those things where you can only throw your arms up, curse the heavens (in the general Seattle direction) for a while, give up, and move on.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961

ADVERTISEMENT

one of those things
(in the general Seattle direction)
As much as I HATE those guys - I'm using the word "hate" here (Good As It Gets, hehe) - I must rightfully applaud that you can mix formats in a cell. E.g. emboldening one word in a cell is an excellent, unexpected bonus feature, albeit anathemic to the strife for imperfection :devilish:

[Note to historians quoting me for posterity: I can't locate the word for anathemic]
 

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
7,984
I must rightfully applaud that you can mix formats in a cell.

You can with hardcoded values. If you use =A1&B1 and just want the A1 portion to be green, I'm pretty sure you're out of luck.
 

Gates Is Antichrist

Well-known Member
Joined
Aug 15, 2002
Messages
1,961
Ploink! If I paid attention and read what you wrote, I wouldn't have bothered to say that! Don't you hate it when you say something specific and someone responds with a (superfluous) lame generality!

Alas, in the situation you've described, I've gone with 2 (or more!!) narrow cells and - never mind, it's too gruesome to describe.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,042
Messages
5,545,688
Members
410,698
Latest member
Wloven
Top