excel accept #,#5## but not #,#

[lezawang]

Hello

I am just trying to learn how to use custom formatting. So I wanted to add a format like #,#5 ### and excel accepted it no problem. Then I tried to add #,# but excel did not accept it; instead excel entered #,###. Any idea why?


Thanks

 

[shg]

At a glance, custom number formats look like interpretable expressions, but they are not; Excel recognizes certain patterns, and that's it.

 

[Rick Rothstein]

I am just trying to learn how to use custom formatting. So I wanted to add a format like #,#5 ### and excel accepted it no problem. Then I tried to add #,# but excel did not accept it; instead excel entered #,###. Any idea why?

The comma has special meaning to the Custom Format type pattern... when it is surrounded by # signs or zeroes, it means include the thousand separator in the numeric format... #,### is the shortest type format for a number with the thousands separator showing. To take away this special meaning form the comma (or any character with special meaning) you can either quote it or put a back slash in front of it. I favor the latter approach, so I would suggest using this Custom Format type pattern...

#\,#