How to convert string to number and vice versa in Excel

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 v开发者_JAVA百科alues 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?

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.

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"

@Jean-Francois since we're talking about i18n you should use CStr ( for international applications ) function instead of Str ( where . dot is separator for floating number , not much use in French where comma , is a separator ex : 7,54)

Sub myFunction()
    Dim num As Double
    Dim str As String

    num = 3.251
    str = CStr(num) ' convert a number into a String

    num = Val(str) ' convert a String into an integer
    num = Trim(str) ' convert a String into a Variant

    str = Trim(num) ' notice this works the other way around

End Sub