Geek asks and answers

How to convert string to number and vice versa in Excel

I have some XML files containing decimal numbers stored in '.' as a decimal separator. I created an Excel workbook that is used to edit data in this XML. MSXML is used to read data from XML, which is then displayed on sheets. When the data is stored, I change the structure of the DOM with the new values ​​read from the tables, and I save the XML.

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

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

+3
source share
3 answers

How do you change the structure of the DOM with the new values ​​- programmatically? How?

Is it possible to use Format(Cell.value, "#0.0")? Also try Format(CDbl(Cell.Value), "#0.0"). If this does not work, can you just paste Replace(Value, ",", ".")? This should be good if you are not using thousands separators.

+7
source

Use the function Str. It only recognizes 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 Strreserves the leading space for the sign of the number. If you do not want to lead in your positive numbers, simply the Trimresult:

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

@Jean-Francois, i18n, CStr ( ) Str (. dot - , , , 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
0

More articles:


All Articles