How to convert string to number and vice versa in Excel

Question

I have some XML files that contain decimal numbers stored with '.' as decimal separator. I created Excel workbook that is used for editing data in that XML. MSXML is used to read the data from XML that are then displayed in worksheets. Once data is being stored I modify DOM structure with new values read from tables and I save XML.

The problem is that when I convert decimal numbers back to strings, to save them as values in XML, decimal separator that is specified by user in Windows regional settings is used.

I need a way to force storing decimal numbers with '.' as decimal separator in all cases. Is this possible?

Answer 1

How do you modify the DOM structure with the new values--programmatically? How?

Is it possible for you to use Format(Cell.value, "#0.0")? Also try Format(CDbl(Cell.Value), "#0.0"). If that doesn't work, can you just throw in a Replace(Value, ",", ".")? This should be okay if you're not using thousands separators.

Answer 2

Use the Str function. It recognizes only the period . as a valid decimal separator.

Dim d as Double
Dim s as String
d = 3.25
s = Str(d) 
' s is now " 3.25"

Note that Str reserves a leading space for the sign of the number. If you don't want a leading space on your positive numbers, just Trim the result:

s = Trim(Str(d))
' s is now "3.25"