You can code it like this:

Sub Keep0s()

Dim c As Range
Dim MyStr As String
Dim x As Long
For Each c In Selection
    MyStr = "0.0"
    For x = 1 To Len(c) - 3
        MyStr = MyStr & "0"
    Next x
    c.NumberFormat = MyStr
    c = CDec(c)
Next c
End Sub

Insert the code into a module by pressing ALT+F11 or similar. Highlight the numbers stored as text that you want to convert, and then run the code.

Case 1: you find a math rule

If you find some kind of rule for computing the number of significant digits that you want displayed from the number itself, then it's relatively easy: you create as many conditional formatting conditions as how many different number of digits you want, and for each condition you define a numeric format with that many digits.

For example, from your data I can guess that you want to display 3 significant digits. I don't know if that is true, but if this was the case you could create several conditions like:

• condition is formula: =TRUNC(-LOG10(A1))+3=6 → format to 6 decimal places
• condition is formula: =TRUNC(-LOG10(A1))+3=5 → format to 5 decimal places

plus as many of them as needed. Note that I am assuming you have converted text to numbers and that those numbers start from cell A1.

Case 2: no math rule

If on the other hand there is no math rule, and you just have to keep the same number of characters as in the input text, you can do something similar to case 1.

Leave the text column as it is (say, starting from cell A1), and create one more column with the same data converted to numbers (say, from cell B1)

Then apply conditional formatting to column B, using a series of rules like

• condition is formula: =LEN(A1)-2=6 → format to 6 decimal places
• condition is formula: =LEN(A1)-2=5 → format to 5 decimal places

plus as many of them as needed.