Thousand/Decimal seperator

[Gertjan Huiskes]

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

 

[Sheeloo]

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

 

[Gertjan Huiskes]

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]

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