How to keep trailing zeroes in formatting decimal numbers?

Question

Suppose that I have the following numbers in Notepad:

1.19
0.040
10.1123
23.110
21.223
2.35456
4.0
10234.0009
456798.7500
123
34.560
40060
33.7876

If I copy the numbers and paste them in Excel, the numbers will change to this:

1.19
0.04
10.1123
23.11
21.223
2.35456
4
10234.0009
456798.75
123
34.56
40060
33.7876

I want to copy-paste the numbers without changing their original format, for example:

• 0.040, not 0.04
• 4.0, not 4
• 456798.7500, not 456798.75

I'm aware that I can use conditional formatting like

If condition_1 Then
   Cells(...).NumberFormat = "0.00"

ElseIf condition_2 Then
   Cells(...).NumberFormat = "0.000"

...

Else
   result_else

End If

or use Select ... Case Statement, but the problems with these methods are:

1. The code can be lengthy and looks messy.
2. If the numbers change, then the conditionals must change, too.

So my question is:

How to make Excel not truncate the zeroes at the end of decimal numbers?

I want to keep the values as numbers, not text.

This kind of representation does matter in financial data for example: foreign exchange or currency rate.

Answer 1

If I place your original data in column A (with your posted formats) and run this:

Sub CopyFull()
    Dim A As Range, B As Range

    Set A = Range("A1:A13")
    Set B = Range("B1:B13")
    A.Copy B
End Sub

The copied data items will have the same formats as the originals:

So if A9 has a NumberFormat of 0.0000, then so will B9.

EDIT#1:

If the data started out in an open NotePad process, I would:

1. manually (or programmatically) store the data as a text file (.txt)
2. import the text file into a column as Text
3. convert each item in the column into a Number with a NumberFormat consistent with the text format