Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. |
#2
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Hi Roee
If you go FormatCellsNumber tab and select Custom, you will see a few suitable formats such as: #,##0;-#,##0 This will do what you want. Best regards Richard On 7 Mar, 14:29, Roee wrote: 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. |
#3
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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) "Roee" wrote: 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. |
#4
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Use a format of #,##0.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. |
#5
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Is that dot a decimal point or a period (end of sentence)?
<vbg Bob Phillips wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson |
#6
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
Roee,
Try something like a custom format of #,##0.###### -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Roee" wrote in message ... 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. |
#7
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. |
#8
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote in message ... 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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. |
#9
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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?? "Sandy Mann" wrote: Roee, Try something like a custom format of #,##0.###### -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings with @tiscali.co.uk "Roee" wrote in message ... 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. |
#10
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? "Dave Peterson" wrote: Is that dot a decimal point or a period (end of sentence)? <vbg Bob Phillips wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson |
#11
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. |
#12
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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? Roee wrote: 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? "Dave Peterson" wrote: Is that dot a decimal point or a period (end of sentence)? <vbg Bob Phillips wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson -- Dave Peterson |
#13
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 wrote: 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. "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson |
#14
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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" wrote: 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 wrote: 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. "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson |
#15
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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. Roee wrote: 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" wrote: 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 wrote: 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. "Bob Phillips" wrote: 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" wrote in message ... 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" wrote: Use a format of #,##0. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Roee" wrote in message ... 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. -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Decimal Places | Excel Discussion (Misc queries) | |||
Max decimal places | Excel Discussion (Misc queries) | |||
FIXED 2 DECIMAL PLACES, MUST ENTER ALL ZEROES AFTER DECIMAL POINT. | Excel Discussion (Misc queries) | |||
decimal places | Excel Discussion (Misc queries) | |||
add 2 decimal places | Excel Discussion (Misc queries) |