# Converting "text formated as string" using CDbl() fails

Viewed 7 times

I am using this function to convert "numbers formatted as strings" to numbers.

``````''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' **Occurence:**
' This function is used in multiple handlers.
'
' **Summary:**
' This function iterates over all the (a) rows & columns in the 1st table
' on the sheet. It checks non-empty values. If value is not formatted as
' to the table while *"numbers formated as text"* are converted to
' `Double` and added to the array. At the end this array is written back
' to the table while *"numbers formated as text"* are converted to `Double`.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Public Function remove_numbers_formated_as_text(ByVal sh As Worksheet)

Dim r As Range
Dim arr As Variant
Dim i As Long '''' arr's rows
Dim j As Long '''' arr's columns

Dim s As String
Dim b As Boolean

Set r = sh.ListObjects(1).DataBodyRange
arr = r.Formula2

'''' Iterate over a whole row and then proceed to next column
For i = 1 To UBound(arr)
For j = 1 To UBound(arr, 2)
If IsEmpty(arr(i, j)) = False Then

'''' Check whether array mamber stores formula
b = r(i, j).HasFormula
If b = True Then GoTo A

'''' If cell doesn't treat numbers as text and is a numeric value,
'''' then this is definitely a measurement that somebody entered and
'''' it is therefore converted to double.
s = r(i, j).NumberFormat
If IsNumeric(arr(i, j)) And s <> "@" Then
arr(i, j) = CDbl(arr(i, j))
End If

End If
A:
Next
Next

r.Formula2 = arr

End Function
``````

I thought that this works, but it does not. Original array stores everything as `Variant/Strings`, e.g. `"0.544502556324005"` but after these values are modified by `CDbl()`, they become a `Variant/Double` but they loose decimal separator, becoming e.g. `544502556324005`. So data get's corrupted...

Why are decimal separators removed? Any idea how to solve this? Or if anyone has some idea on how to convert "numbers formated as strings" better...