MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Custom number format problem


Posted by Robert Joseph Tinker on December 27, 2000 3:53 PM

I want to format a range of number cells to use a 1000 separator (,), but not to display a decimal point unless there are significant digits to the right of the decimal point.

For example:

2123123 should display as 2,123,123 (not as 2,123,123.)
2.1111 should display as 2.1111
2.1 should display as 2.1

Is there a way to do this?

I thought #,###.########## would work, but that displays a decimal point in every cell. I don't want that.


Posted by Celia on December 28, 2000 2:31 AM


I can't think of a custom format to do it.
You could try an event procedure. For example, provided none of the cells in your range to be formatted are the result of formulas :-

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim cell As Range
Dim myRange As Range
Set myRange = Range("A1:A100")

If Not Intersect(Target, myRange) Is Nothing Then
For Each cell In Intersect(Target, myRange)
If IsNumeric(cell) Then
If cell.Value - Int(cell) = 0 Then
cell.NumberFormat = "#,##0"
Else
cell.NumberFormat = "#,##0.##########"
End If
End If
Next
End If
End Sub

Celia

Posted by Dave on December 28, 2000 11:01 PM

Hi Robert

You will need to use a Conditional Custom format to achieve this, try using:
[>100]#,###.#########,;#.###


Dave

  • OzGrid Business Applications

Posted by Celia on December 29, 2000 12:17 AM


Dave
I can't get this to work. I get the following results :-
(1). For input numbers greater than 100, displayed is the number divided by 1000. Additionally, if the number ends in 000 (say, "5000"), displayed is "5."
(2). For input numbers less than or equal to 100 that are whole numbers (say "99"), displayed is "99."
Would appreciate your advising what I'm doing wrong. Thanks.
Celia

Posted by Dave on December 29, 2000 6:18 AM

Probably nothing. I only really tried on the numbers shown. Didn't you?

Or maybe your SheetChange event is still running, but disabling events should fix that bit :O)

Anyway I found this since
http://support.microsoft.com/support/excel/content/formats/default.asp


Dave

  • OzGrid Business Applications

Posted by Celia on December 29, 2000 4:06 PM


Dave
I can't get it to work whatever numbers I use and I can't really see why it should.
If you had it working, I think there must be a typo in the format you posted. I've tried various amendments to it without success. Would really like to get it working since I've spent a long time playing around with it.

Thanks for the link (was already familiar with the content thereof). I can't find anything there that indicates how to come up with a custom format that does what is required.
I must be missing something. I can't see how a condition can be set up to do what is required since :-
"The condition in a section of a number format can contain only a comparison operator (<, <=, = >=, >, <>) and a value. The condition cannot contain any other type of formula."

Celia


Posted by Dave on December 29, 2000 7:51 PM

Celia

I just formated 3 cells as shown then entered the 3 numbers 2123123, 2.111 and 2.1
the result were 2,123.123, 2.11 and 2.1

But having said this I'm assuming that the first example had a typo in it i.e.

2123123 should display as 2,123,123 (not as 2,123,123.)

Was really: 2123123 should display as 2,123.123 (not as 2,123,123.) as Robert stated that:

"but not to display a decimal point unless there are significant digits to the right of the decimal point."


I may have the wrong end of the stick though. Even I don't it certainly does have limitations.


Dave

  • OzGrid Business Applications

Posted by Celia on December 29, 2000 10:31 PM

Dave
We've been trying to arrive at different results. The way I read the question was the way it was actually written : "2123123" should be "2,123,123" and not "2,123,123." But your format produces "2,123.123"
I've admitted defeat - I don't think it's possible.
Celia