Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thousand/Decimal seperator
I have the following problem:
I set Windows to Dutch (dot as thousand seperator and comma as decimal sererator) The format of cell A1 and A2 is both General In cell A1 I have this formula: =1234/1000 Excel displays 1,234 (comma) From VBA I execute the following statement: Range("A2").Value = CStr(Range("A1").value) I should expect that 1,234 (comma) is displayed in cell A2, as a value, or at least as a string But cell A2 displays 1.234 (dot) and contains the value 1234 The format of cell A2 has been changed by Excel to 'Number' with 0 decimals and using thousand seperator It seems Excel is calculating somewhere using a comma as thousand seperator. Is there some setting in Excel to change this? I'm working on Windows XP Pro I tested it with Excel 2000 (NL); Excel 2003 (NL) en Excel 2007 (Eng) Gertjan |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thousand/Decimal seperator
Hello,
Excel internally stores numbers as real numbers... Which means that =1234/1000 is stored as 1.234 which will be displayed as 1,234 under your settings. When you use cStr on the number in A1 it returns the number as 1.234 to the cell A2 but since the cell A2 is formatted to treat . as thousand separator it is converted to 1234 and displayed as 1.234 Hope this makes sense... If not then enter =123456789/1000 in A1. You should see 123.456,789 Now run your macro you should get 123.456.789 (I think :-) -- Always provide your feedback... "Gertjan Huiskes" wrote: I have the following problem: I set Windows to Dutch (dot as thousand seperator and comma as decimal sererator) The format of cell A1 and A2 is both General In cell A1 I have this formula: =1234/1000 Excel displays 1,234 (comma) From VBA I execute the following statement: Range("A2").Value = CStr(Range("A1").value) I should expect that 1,234 (comma) is displayed in cell A2, as a value, or at least as a string But cell A2 displays 1.234 (dot) and contains the value 1234 The format of cell A2 has been changed by Excel to 'Number' with 0 decimals and using thousand seperator It seems Excel is calculating somewhere using a comma as thousand seperator. Is there some setting in Excel to change this? I'm working on Windows XP Pro I tested it with Excel 2000 (NL); Excel 2003 (NL) en Excel 2007 (Eng) Gertjan |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thousand/Decimal seperator
No, it still doesn't make sense
If I have a string "123.456,789" and place it in a cell, using VBA, I would like to see 123456,789 as a value or at least 123.456,789 as text Gertjan "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" schreef in bericht ... Hello, Excel internally stores numbers as real numbers... Which means that =1234/1000 is stored as 1.234 which will be displayed as 1,234 under your settings. When you use cStr on the number in A1 it returns the number as 1.234 to the cell A2 but since the cell A2 is formatted to treat . as thousand separator it is converted to 1234 and displayed as 1.234 Hope this makes sense... If not then enter =123456789/1000 in A1. You should see 123.456,789 Now run your macro you should get 123.456.789 (I think :-) -- Always provide your feedback... "Gertjan Huiskes" wrote: I have the following problem: I set Windows to Dutch (dot as thousand seperator and comma as decimal sererator) The format of cell A1 and A2 is both General In cell A1 I have this formula: =1234/1000 Excel displays 1,234 (comma) From VBA I execute the following statement: Range("A2").Value = CStr(Range("A1").value) I should expect that 1,234 (comma) is displayed in cell A2, as a value, or at least as a string But cell A2 displays 1.234 (dot) and contains the value 1234 The format of cell A2 has been changed by Excel to 'Number' with 0 decimals and using thousand seperator It seems Excel is calculating somewhere using a comma as thousand seperator. Is there some setting in Excel to change this? I'm working on Windows XP Pro I tested it with Excel 2000 (NL); Excel 2003 (NL) en Excel 2007 (Eng) Gertjan |
#4
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheet.functions
|
|||
|
|||
Thousand/Decimal seperator
Can you pl. replace
Range("A2").Value = CStr(Range("A1").value) in your macro with Range("A2").Value = "A" & CStr(Range("A1").value) and let me know the result. I think if you format the cells as Number your problem will go away -- Always provide your feedback... "Gertjan Huiskes" wrote: No, it still doesn't make sense If I have a string "123.456,789" and place it in a cell, using VBA, I would like to see 123456,789 as a value or at least 123.456,789 as text Gertjan "Sheeloo" <="to" & CHAR(95) & "sheeloo" & CHAR(64) & "hotmail.com" schreef in bericht ... Hello, Excel internally stores numbers as real numbers... Which means that =1234/1000 is stored as 1.234 which will be displayed as 1,234 under your settings. When you use cStr on the number in A1 it returns the number as 1.234 to the cell A2 but since the cell A2 is formatted to treat . as thousand separator it is converted to 1234 and displayed as 1.234 Hope this makes sense... If not then enter =123456789/1000 in A1. You should see 123.456,789 Now run your macro you should get 123.456.789 (I think :-) -- Always provide your feedback... "Gertjan Huiskes" wrote: I have the following problem: I set Windows to Dutch (dot as thousand seperator and comma as decimal sererator) The format of cell A1 and A2 is both General In cell A1 I have this formula: =1234/1000 Excel displays 1,234 (comma) From VBA I execute the following statement: Range("A2").Value = CStr(Range("A1").value) I should expect that 1,234 (comma) is displayed in cell A2, as a value, or at least as a string But cell A2 displays 1.234 (dot) and contains the value 1234 The format of cell A2 has been changed by Excel to 'Number' with 0 decimals and using thousand seperator It seems Excel is calculating somewhere using a comma as thousand seperator. Is there some setting in Excel to change this? I'm working on Windows XP Pro I tested it with Excel 2000 (NL); Excel 2003 (NL) en Excel 2007 (Eng) Gertjan |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Date Seperator | Excel Discussion (Misc queries) | |||
use 100 seperator instead of 1000 in excel | Excel Discussion (Misc queries) | |||
Removal of thousands seperator | Excel Discussion (Misc queries) | |||
Digit seperator | Excel Worksheet Functions | |||
Thousand Seperator | Excel Discussion (Misc queries) |