Thousands separator problem

[davester]

On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):

1234567

or this:

7.125

or this:

8.1

I want them to look just like that except for the first one, which I want to have the thousands separator:

1,234,567

I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).

I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.

Does anybody know 1) what I am talking about, 2) how to fix it.

Thanks for any help.

 

[davester]

Thanks Yogi, you're getting the result I want, but the method may be a bit too cumbersome. Plus, the spreadsheets will need to be manipulated by other users who will be mystified by that formula, and will wonder why I have a separate formatted worksheet for every input data worksheet.

This problem seems so elementary that I thought I was just missing some simple Excel formatting method...apparently not.

 

[Yogi Anand]

Thanks Yogi, you're getting the result I want, but the method may be a bit too cumbersome. Plus, the spreadsheets will need to be manipulated by other users who will be mystified by that formula, and will wonder why I have a separate formatted worksheet for every input data worksheet.

This problem seems so elementary that I thought I was just missing some simple Excel formatting method...apparently not.

Hi davester:

If you want to format the entries in place, then you may want to use the following macro code ...

For Each cell In Range("A2:A7")
    If Int(cell.Value) - cell.Value = 0 Then
        cell.NumberFormat = "###,###"
        Else
        cell.NumberFormat = "###,###.##"
    End If
Next cell

the preceding code is for formatting to your specification the entries in cells A2:A7 -- modify the range to suit.

I hope this helps.