Using 1000 sperator without specifying decimal places

[Roee]

Hello,

I would like to use the 1000 sperator but I don't want to specify fixed
decimal places.
For example, if the cell contains the number 300, I want it to show 300 and
not 300.00, but I still wan't to use the 1000 sperator.
How do I do that?

The "General" formatting showes the decimal places like I want (only if
exists) but does not allow 1000 sperator and the "Number" formatting requires
defining a fixed number of decimal places.

Thanks,
Roee.

 

[RichardSchollar]

Hi Roee

If you go Format>Cells>Number tab and select Custom, you will see a
few suitable formats such as:

#,##0;-#,##0

This will do what you want.

Best regards

Richard

 

[john]

Just use number, set decimals to zero and put a tick in the seperator box
--
John
MOS Master Instructor Office 2000, 2002 & 2003
Please reply & rate any replies you get

Ice Hockey rules (especially the Wightlink Raiders)

 

[Bob Phillips]

Use a format of #,##0.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

Is that dot a decimal point or a period (end of sentence)?

<vbg>

 

[Sandy Mann]

Roee,

Try something like a custom format of #,##0.######

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk

 

[Roee]

But I want it to show decimal parts *if they exists*.
I want to see 1000.05 as 1,000.05 (and not 1,000 or 1000.05) and 1,000 as
1,000 (and not 1,000.00).

Using a fixed 0 decimal places or a format of #,##0 does not allow it.

Any suggestions?

Thanks,
Roee.

 

[Bob Phillips]

The you need

#,##0.####

but you will always see the dot.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Roee]

Thanks, it works - only now 1000 it shows "1,000." with a dot at the end even
if there aren't any numbers after it.

Is my request so wierd that it does not exists in Excel as a proper option??

 

[Roee]

I'm not sure what you mean. I use a dot as a decimal point (and not comma as
in France for example).
What is the difference between a dot and a period in that context?

 

[Roee]

OK, since that is what Sandy suggested I guess this is as close as it gets.

Any chance that conditional formatting or some VB function can solve it?
Something in general tenor of:
if the number is an integer show in "General" formatting
else use #,##0.####

Roee.

 

[Dave Peterson]

I used dot to represent this character: .

I was asking Bob if he meant that the custom format should be:
#,##0
or
#,##0.

Was Bob using that dot character to indicate the end of sentence--or did he mean
for it to be part of the custom format?

 

[Dave Peterson]

Are you changing those values -- or are they the results of a formula?

If you're changing them yourself, you could use a worksheet_change event.

If you want to try this, rightclick on the worksheet tab that should have this
behavior. Select view code and paste this into the code window:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRng As Range
Dim myCell As Range

Set myRng = Intersect(Target, Me.Range("a:a"))

If myRng Is Nothing Then
Exit Sub
End If

For Each myCell In myRng.Cells
If IsNumeric(myCell.Value) Then
If Int(myCell.Value) = myCell.Value Then
myCell.NumberFormat = "General"
Else
myCell.NumberFormat = "#,##0.0000"
End If
End If
Next myCell

End Sub

I only changed the format in column A.

Did you really mean this: #,##0.####?

 

[Roee]

Thanks.

Actually, some of the values are the result of a formula and some I will
input manually.

As to the format I think I will use this: #,##0.## (2 places is enough)

One more question:
The VB function is working, how do I set myCell.NumberFormat to be "Numeric"
with a 1000 seperator and 0 decimal places?

Thanks,
Roee.

 

[Dave Peterson]

I'd use:

#,##0

no dots <bg>

And you could loop through all the cells that you want in the
worksheet_calculate event. The code will look pretty much like what you have in
the _change event.

If you have trouble post back with what you've got and your question.