Easiest way to convert number stored as text to number in Excel VBA [duplicate]

Question

I trying to convert this text into numbers:

I have tried different formulas but Excel is not recognizing the format. However, using the code below it converts only the first three numbers and removes the rest.

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = Val(r.Value)
    Next r
Next WS
End Sub

The result looks like this

Does anyone have an ease fix for this without removing any numbers?

Answer 1

Easiest way to convert a number stored as text, is to multiply it by 1,
So try this If IsNumeric(r) Then r.Value = (r.Value)*1
Or you can copy paste with a multiplication by 1 and this works too! ;)

Sub ConvertTextNumberToNumber()
For Each WS In Sheets
    On Error Resume Next
    For Each r In WS.UsedRange.SpecialCells(xlCellTypeConstants)
        If IsNumeric(r) Then r.Value = (r.Value)*1
        ''Or test the values in the next column on the right
        'If IsNumeric(r) Then r.Offset(0,1).Value = (r.Value)*1
    Next r
Next WS
End Sub