Convert a number stored as text to a number - Decimal numbers
I realize this is sort of a duplicate question, but the solutions mentioned in this don't work completely for me.
My current code looks like this
Sub ConvertTextToNumber()
Dim Area As Range, C As Range
Set Area = Sheets("Tank 3").Range("C7:L7,B8:B17,C20:L20,B21:B69")
For Each C In Area
If WorksheetFunction.IsNumber(Celle) = False And C <> "" Then
C.NumberFormat = "0.0"
C.Value = C.Value + 0
End If
Next C
End Sub
This works fine with integers, but the moment the number stored as text is decimal, like 0.1 or 0.2, it just converts the number to 1 or 2, respectively.
I tried to use C.value = c.value
after setting NumberFormat to numbers or general, but that doesn't do anything for me
Update: It looks like the issue is with delimiters. I have to use "," as decimal separator and it crashes. If I change the decimal point to "." Sub works fine.
source to share
Just use Range.FormulaLocal
to automatically translate a string to a number based on regional settings:
Sub ConvertTextToNumber()
Dim Area As Range, C As Range
Set Area = Sheets("Tank 3").Range("C7:L7,B8:B17,C20:L20,B21:B69")
For Each C In Area
C.FormulaLocal = C.Value
Next
End Sub
source to share
You can change the delimiters via VBA :
Sub ChangeSeparator()
Application.DecimalSeparator = "."
Application.ThousandsSeparator = ","
Application.UseSystemSeparators = False
ConvertTextToNumber
Application.UseSystemSeparators = True
End Sub
source to share
You could use a little trick and that would be to import all the numbers as text into your code and then manipulate it and spit it back out. The comma mixes up the English conversions and will see 2.0 as 20. Anyway, here's how you could do it
Sub ConvertTextToNumber()
Dim Area As Range, C As Range
Dim InNumAsStr As String
Set Area = Sheets("all").Range("A1:B10")
For Each C In Area
InNumAsStr = C.Text '*** force the value to be a string type ***
'***swap out any commas with decimal points ***
InNumAsStr = Replace(InNumAsStr, ",", ".")
C.NumberFormat = "0.0"
C.Value = InNumAsStr
Next C
End Sub
source to share