# Numerical format tricky quest

#### Gates Is Antichrist

##### Well-known Member
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 ***********

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### Oaktree

##### MrExcel MVP
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
So condemned.

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 But point taken.

#### Oaktree

##### MrExcel MVP
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

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

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

#### Oaktree

##### MrExcel MVP
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
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.

Replies
19
Views
374
Replies
14
Views
647
Replies
0
Views
200
Replies
0
Views
575
Replies
5
Views
288

1,141,670
Messages
5,707,731
Members
421,525
Latest member
carian1980

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back