thousands separator w/out set decimal places?

[Hoff]

I have a column of numbers, many of which are greater than 1000, and a
few of which are not integers (and need to display to the right of the
decimal point). I'd like to display the "thousands separator" (a comma
in this case) to make it easier to read the larger numbers. The only way
I've found to do this is with the Number format, which forces me to set
the number of decimal places to display. This forces every integer value
to display unneeded 0's (or to truncate the display of non-integer
values).

I've played around with the number format a bit, and even tried a
conditional format (if(a1-int(a1)>0, ...) before I realized that
conditional formatting cannot alter number formats (only limited font,
border and patterns).

Any tips to this extent would be much appreciated.

Larry

 

[J Laroche]

Hoff wrote on 2005/04/11 18:44:

I have a column of numbers, many of which are greater than 1000, and a
few of which are not integers (and need to display to the right of the
decimal point). I'd like to display the "thousands separator" (a comma
in this case) to make it easier to read the larger numbers. The only way
I've found to do this is with the Number format, which forces me to set
the number of decimal places to display. This forces every integer value
to display unneeded 0's (or to truncate the display of non-integer
values).

I've played around with the number format a bit, and even tried a
conditional format (if(a1-int(a1)>0, ...) before I realized that
conditional formatting cannot alter number formats (only limited font,
border and patterns).

Any tips to this extent would be much appreciated.

Larry

You have to go in OS X's System Preferences, International Pane, Formats
tab, and personalize the Numbers. You should do it while Excel is not
running.

Then in Excel, the General or Custom Standard number cell formats will
probably give you the desired result.

JL
Mac OS X 10.3.8, Office v.X 10.1.6

 

[Jim Gordon MVP]

Hi,

You were very close!

There is a cell function called TEXT that is the answer to your needs.

For example

A B
1 Number Formatted
2 55000.00 55,000
3 25.5 25.5

using these numbers and formulas

A B
1 Number Formatted
2 55000 =IF(A2>10000,TEXT(A2,"##,000"),TEXT(A2,"###.##"))
3 25.5 =IF(A3>10000,TEXT(A3,"##,000"),TEXT(A3,"###.##"))

There are some examples in Help if you search for the TEXT worksheet
function.

-Jim