I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.

I don't think you can do this with a number format, but you can do it
with a
formula like:

=TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))


HTH
Andrew Taylor


John Persons wrote:
I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.

Actually it seems you _can_ (almost) do it, with number format
#,##0.############### (15 #s after the decimal point, which will
always be enough as Excel displays at most 15 digits precision).

The only drawback is that whole numbers display with a trailing
decimal point.

Andrew



Andrew Taylor wrote:
I don't think you can do this with a number format, but you can do it
with a
formula like:

=TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))


HTH
Andrew Taylor


John Persons wrote:
I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.

On 3 Nov 2005 14:50:57 GMT, John Persons wrote:

I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.

You will need to use a VBA event-triggered macro.

Right click on the worksheet tab and select View Code.

Paste the macro below into the window that opens.

Change AOI appropriately to your ranges. That means make it cover as large an
area as you might ever want to format this way. Subsequent coding will select
only the cells with numbers to be formatted. This will both shorten the time
for the routine to run, as well as ignore TEXT and errors within that range.

========================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Fmls As Range, Cnst As Range, Combined As Range
Dim c As Range

Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

'restrict AOI to just those cells containing numbers or formulas that -- a
number
'HELP says one can add the Type constants, but I could not get that to work.

On Error GoTo Handler

Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

If Fmls Is Nothing Then Set Combined = Cnst
If Cnst Is Nothing Then Set Combined = Fmls
If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

For Each c In Combined
With c
If Int(.Value) = .Value Then
.NumberFormat = "#,##0"
Else
.NumberFormat = "#,##0.###############"
End If
End With
Next c
Exit Sub

Handler:
If Error = "No cells were found." Then
Resume Next
Else: MsgBox ("Error: " & Error)
End If

End Sub
=====================================
--ron

"Andrew Taylor" wrote in
oups.com:

Actually it seems you _can_ (almost) do it, with number format
#,##0.############### (15 #s after the decimal point, which will
always be enough as Excel displays at most 15 digits precision).

The only drawback is that whole numbers display with a trailing
decimal point.

That has one other drawback besides the trailing decimal point. If the cell is
only wide enough to show (say) 3 decimal points, it either widens the column or
displays ###########.

Thanks for the suggetion, though.

Replying to my own message yet again.... my suggested number
format won't give the correct format if the number is very small: e.g.
..000000000000000000000001234

Andrew


Andrew Taylor wrote:
Actually it seems you _can_ (almost) do it, with number format
#,##0.############### (15 #s after the decimal point, which will
always be enough as Excel displays at most 15 digits precision).

The only drawback is that whole numbers display with a trailing
decimal point.

Andrew



Andrew Taylor wrote:
I don't think you can do this with a number format, but you can do it
with a
formula like:

=TEXT(A1,"#,##0")&IF(A1=TRUNC(A1),"","."&RIGHT(A1-TRUNC(A1),LEN(A1-TRUNC(A1))-2))


HTH
Andrew Taylor


John Persons wrote:
I would like to format cells so the numbers are displayed like the general format
but with the thousands separator. I can't figure out how to insert the thousands
separator without fixing the number of decimal places.

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

I would like
=10
to display as
10 .

Thanks for any hints.

Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured there *must*
be a simple way to get the general formatting with comma separators!

The drawback of the macro, for my purposes, is that Excel insists on widening the
columns to show all the decimal places specified in the format code given in the
macro. I would like it to behave like general formatting does by just showing the
number of decimal places that will fit, given the width of the cell.

For instance, I would like =1000*pi() to display 3,141.593 and =10000*pi() to
display 31,415.93

On 4 Nov 2005 15:05:03 GMT, John Persons wrote:


Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured there *must*
be a simple way to get the general formatting with comma separators!

The drawback of the macro, for my purposes, is that Excel insists on widening the
columns to show all the decimal places specified in the format code given in the
macro. I would like it to behave like general formatting does by just showing the
number of decimal places that will fit, given the width of the cell.

For instance, I would like =1000*pi() to display 3,141.593 and =10000*pi() to
display 31,415.93

That specification is a bit different from your original specification which
was:

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the cell).

Do you want to leave the width of the cell fixed, at whatever you happen to set
it prior to setting the format?

If so, what should happen if the cell width is too small to display the number,
which could happen if the number is a large integer.

What do you want to happen if the cell width is not enough to display ANY of
the fractional portion?

In any event, this modification will adjust the formatting of fractional
numbers so as to fit in the current column width. If your number is
fractional, but the column width is too narrow to display ANY of the fractional
portion, it will be displayed with a terminal decimal point.

In other words, =10000*PI() might display as 31,416. if there is not enough
room to display ANY of the decimals.

This is very preliminary, and will work only for your NORMAL font, but can be
modified once you supply more precise specifications.

=================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Fmls As Range, Cnst As Range, Combined As Range
Dim c As Range
Dim W
Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

'restrict AOI to just those cells containing numbers _
or formulas that -- a Number
'HELP says one can add the Type constants, but I _
could not get that to work.

On Error GoTo Handler

Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

If Fmls Is Nothing Then Set Combined = Cnst
If Cnst Is Nothing Then Set Combined = Fmls
If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

For Each c In Combined
With c
W = .ColumnWidth
If Int(.Value) = .Value Then
.NumberFormat = "#,##0"
Else
.NumberFormat = "#,##0." & Application.WorksheetFunction.Rept _
("#", Application.WorksheetFunction.Max(0, W - Len(Format _
(Int(.Value), "#,##0."))))
End If
End With
Next c
Exit Sub

Handler:
If Error = "No cells were found." Then
Resume Next
Else: MsgBox ("Error: " & Error)
End If

End Sub
==============================
--ron

Ah, sorry, I misunderstood the question. Still, I taught myself
something I didn't know about number formats.. ;)


John Persons wrote:
"Andrew Taylor" wrote in
oups.com:

Actually it seems you _can_ (almost) do it, with number format
#,##0.############### (15 #s after the decimal point, which will
always be enough as Excel displays at most 15 digits precision).

The only drawback is that whole numbers display with a trailing
decimal point.

That has one other drawback besides the trailing decimal point. If the cell is
only wide enough to show (say) 3 decimal points, it either widens the column or
displays ###########.

Thanks for the suggetion, though.

Ron Rosenfeld wrote in
:

On 4 Nov 2005 15:05:03 GMT, John Persons
wrote:


Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured
there *must* be a simple way to get the general formatting with comma
separators!

The drawback of the macro, for my purposes, is that Excel insists on
widening the columns to show all the decimal places specified in the
format code given in the macro. I would like it to behave like
general formatting does by just showing the number of decimal places
that will fit, given the width of the cell.

For instance, I would like =1000*pi() to display 3,141.593 and
=10000*pi() to display 31,415.93

That specification is a bit different from your original
specification which was:

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the
cell).

Do you want to leave the width of the cell fixed, at whatever you
happen to set it prior to setting the format?

If so, what should happen if the cell width is too small to display
the number, which could happen if the number is a large integer.

What do you want to happen if the cell width is not enough to display
ANY of the fractional portion?

I would like it to behave something like the general format seems to behave:

(a) If the number is very close to zero (so with the existing cell width, the
display would be all zeroes except for perhaps the last two digits), then use
scientific notation. Widening the column instead of using scientific notation
would also be fine.

(b) If abs(x) is so large that the integer portion will not fit in the existing
cell width, then use scientific notation. Widening the column instead of using
scientific notation would also be fine.

(c) Else, show as many decimal places as will fit in the cell.


In any event, this modification will adjust the formatting of
fractional numbers so as to fit in the current column width. If your
number is fractional, but the column width is too narrow to display
ANY of the fractional portion, it will be displayed with a terminal
decimal point.

Slick! It doesn't handle category (a) above the way I would like. I could
change those cells manually to scientific notation, but the formatting changes
back when I edit any cell in AOI. Is there a way to get the macro to skip over
cells that are currently formatted to something other than general or the two
formats prescribed by the macro? That way particular cells within AOI could have
different formats.

Thanks again from a VBA illiterate!

On 4 Nov 2005 20:14:04 GMT, John Persons wrote:

Ron Rosenfeld wrote in
:

On 4 Nov 2005 15:05:03 GMT, John Persons
wrote:


Thanks for the macro, Ron. I'm clearly naive about Excel -- I figured
there *must* be a simple way to get the general formatting with comma
separators!

The drawback of the macro, for my purposes, is that Excel insists on
widening the columns to show all the decimal places specified in the
format code given in the macro. I would like it to behave like
general formatting does by just showing the number of decimal places
that will fit, given the width of the cell.

For instance, I would like =1000*pi() to display 3,141.593 and
=10000*pi() to display 31,415.93

That specification is a bit different from your original
specification which was:

For instance, I would like
=1000*pi()
to display as
3,141.59265 (showing as many decimal places as will fit in the
cell).

Do you want to leave the width of the cell fixed, at whatever you
happen to set it prior to setting the format?

If so, what should happen if the cell width is too small to display
the number, which could happen if the number is a large integer.

What do you want to happen if the cell width is not enough to display
ANY of the fractional portion?

I would like it to behave something like the general format seems to behave:

(a) If the number is very close to zero (so with the existing cell width, the
display would be all zeroes except for perhaps the last two digits), then use
scientific notation. Widening the column instead of using scientific notation
would also be fine.

(b) If abs(x) is so large that the integer portion will not fit in the existing
cell width, then use scientific notation. Widening the column instead of using
scientific notation would also be fine.

(c) Else, show as many decimal places as will fit in the cell.


In any event, this modification will adjust the formatting of
fractional numbers so as to fit in the current column width. If your
number is fractional, but the column width is too narrow to display
ANY of the fractional portion, it will be displayed with a terminal
decimal point.

Slick! It doesn't handle category (a) above the way I would like. I could
change those cells manually to scientific notation, but the formatting changes
back when I edit any cell in AOI. Is there a way to get the macro to skip over
cells that are currently formatted to something other than general or the two
formats prescribed by the macro? That way particular cells within AOI could have
different formats.

Thanks again from a VBA illiterate!

Try this. I've not done extensive testing, but I think it will work pretty
close to what you want, and might even properly handle different sized fonts.

================================
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim AOI As Range
Dim Fmls As Range, Cnst As Range, Combined As Range
Dim c As Range
Dim W, SFZ

SFZ = Application.StandardFontSize
Set AOI = Range("A:A") 'set to range(s) you may wish to format this way

'restrict AOI to just those cells containing numbers _
or formulas that -- a Number
'HELP says one can add the Type constants, but I _
could not get that to work.

On Error GoTo Handler

Set Fmls = AOI.SpecialCells(xlCellTypeFormulas, 1)
Set Cnst = AOI.SpecialCells(xlCellTypeConstants, 1)

If Fmls Is Nothing And Cnst Is Nothing Then Exit Sub

If Fmls Is Nothing Then Set Combined = Cnst
If Cnst Is Nothing Then Set Combined = Fmls
If Combined Is Nothing Then Set Combined = Union(Fmls, Cnst)

For Each c In Combined
With c
W = .ColumnWidth * SFZ / .Font.Size
If Int(.Value) = .Value Then
.NumberFormat = "#,##0"
Else
.NumberFormat = "#,##0." & Application.WorksheetFunction.Rept _
("#", Application.WorksheetFunction.Max(0, W - Len(Format _
(Int(.Value), "#,##0."))))
End If

'Check for proper display
If Right(.Text, 1) = "." Then .NumberFormat = "#,##0"
If .Text = 0 Or Left(.Text, 1) = "#" Then .NumberFormat = "General"
End With
Next c
Exit Sub

Handler:
If Error = "No cells were found." Then
Resume Next
Else: MsgBox ("Error: " & Error)
End If

End Sub
===========================


--ron

Ron Rosenfeld wrote in
:

Try this. I've not done extensive testing, but I think it will work
pretty close to what you want, and might even properly handle
different sized fonts.

Thanks again, Ron! I have been messing around with it and it seems to work well.
Is there an easy method that would allow me to set particular cells in the range to
a different format? That way I could use this as the default format for the
worksheet, but some cells could be formatted differently.

I suppose I could define AOI to be multiple ranges by editing the VBA code whenever
I need to format a cell differently, but that would be a pain. If there is a
clever way to make the macro skip over any cells in AOI that have been formatted
differently, that would be great.

On 7 Nov 2005 20:05:45 GMT, John Persons wrote:

Ron Rosenfeld wrote in
:

Try this. I've not done extensive testing, but I think it will work
pretty close to what you want, and might even properly handle
different sized fonts.

Thanks again, Ron! I have been messing around with it and it seems to work well.
Is there an easy method that would allow me to set particular cells in the range to
a different format? That way I could use this as the default format for the
worksheet, but some cells could be formatted differently.

I suppose I could define AOI to be multiple ranges by editing the VBA code whenever
I need to format a cell differently, but that would be a pain. If there is a
clever way to make the macro skip over any cells in AOI that have been formatted
differently, that would be great.

Well, you'd have to know in advance what format to check for, and if it's
already in the cell, don't reformat it. Or some other common characteristic of
the cells to be formatted (or not formatted).

You'd also have to be sure that your "excluded formats" don't include any of
these custom formats.

Another approach would be to select the cells you wish to format (or not
format) and include(exclude) those cells that are selected.

Think about what sort of approach you want to take.


--ron