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:

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:

The code can be lengthy and looks messy.
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.

Before pasting, you need to change the column format to Text. — Karthick Gunasekaran, Jul 11 '16 at 11:17
What conditions would you use, if you were to use conditional formatting? — Scott Hunter, Jul 11 '16 at 11:19
@NanAvanIllai I've just realized that so I edit my question. I add **P.S.** — Anastasiya-Romanova 秀, Jul 11 '16 at 11:19
Don't change the true numbers to text-that-looks-like-a-number.Never, never, never. What other column has an identifier that can determine what the number format should be? Can you provide 3-5 examples? — , Jul 11 '16 at 11:19
Why do you need the trailing 0's (which do not appear to provide any information, as numbers)? — Scott Hunter, Jul 11 '16 at 11:20
@Anastasiya-Romanova秀, please go through this link https://support.microsoft.com/en-in/kb/269370 — Karthick Gunasekaran, Jul 11 '16 at 11:22
@ScottHunter - As I understand the problem (I have some prior knowledge) it is to demonstrate that a currency that should be displayed to a 4 decimal precision is always displayed that way. Unfortunately, other currencies may only demand a 2 decimal precision. The key I believe is the currency identifier. — , Jul 11 '16 at 11:22
@ScottHunter In finance, formatting numbers like this does matter. If I use conditional formatting, I might use `If number_1 > 10 Then ` but this doesn't work for any value of currencies — Anastasiya-Romanova 秀, Jul 11 '16 at 11:23
I don't understand why does one downvote this since it's kind of representation really matters in finance like foreign exchange data — Anastasiya-Romanova 秀, Jul 11 '16 at 11:25
@Anastasiya-Romanova秀 Apply the shortcut, ALT + H, ANM and change the decimal places as 4 — Karthick Gunasekaran, Jul 11 '16 at 11:28
Anastasiya - if you don't have a column of currency identifiers then the numbers are meaningless anyways. Why are you so reluctant to disclose this **very** important piece of information? (btw, the down-voter probably didn't like being told that number-as-text was a bad idea. have one back+1) — , Jul 11 '16 at 11:29
@Jeeped It's not meaningless to me but applying that kind of conditionals will extend the code like I said in point 1. I'm hoping that user here is able to give me the alternative way, i.e. the simplest and most efficient one. Thanks for the +1, really appreciate it. — Anastasiya-Romanova 秀, Jul 11 '16 at 11:35

score 2 · Answer 1 · answered Jul 11 '16 at 12:49

If there was any accompanying column with an identifier that could be used to group the numerical values into currency format categories then the problem becomes simple¹.

^{Data before running the currencyConditionals sub procedure}

_{• EUR should use €, have 3 decimal places and be green

                  • JPN should use ¥, have 4 decimal places and be dark red

                  • GPD should use £, have 0 decimal places and be dark blue

                  • USD should use $, have 2 decimal places and be blue}

Run the code.

Option Explicit

Sub currencyConditionals()
    Dim a As Long, aCURRs As Variant, aFRMTS As Variant

    aCURRs = Array("EUR", "JPY", "GBP", "USD")
    aFRMTS = Array("[color10]_([$€-2]* #,##0.000_);[color3]_([$€-2]* (#,##0.000);[color15]_(* -??_);[color46]_(@_)", _
                   "[color9]_([$¥-411]* #,##0.0000_);[color9]_=[$¥-411]* (#,##0.0000);[color15]_(* -??_);[color46]_(@_)", _
                   "[color11]_([$£-809]* #,##0_-;[color11]-[$£-809]* #,##0_-;[color15]_-[$£-809]* -_);[color46]_-@_-", _
                   "[color5]_($* #,##0.00_);[color5]_($* (#,##0.00);[color15]_($* -??_);[color46]_(@_)")

    With Worksheets("Sheet2")
        With .Cells(1, 1).CurrentRegion
            With .Offset(1, 5).Resize(.Rows.Count - 1, 1)
                .FormatConditions.Delete
                For a = LBound(aCURRs) To UBound(aCURRs)
                    With .FormatConditions.Add(Type:=xlExpression, _
                                Formula1:="=$C2=" & Chr(34) & aCURRs(a) & Chr(34))
                        .NumberFormat = aFRMTS(a)
                    End With
                Next a
            End With
        End With
    End With
End Sub

^{Data after running the currencyConditionals sub procedure}

¹ _{Examples of formatting mask codes can be found at Number Format Code.}

Wow, this is very helpful. Thanks. (+1). BTW, I'm thinking a simple method right now. I hope I can post the answer as soon as possible if it works — Anastasiya-Romanova 秀, Jul 11 '16 at 12:55

Gary's Student · Accepted Answer · 2016-07-11T11:50:27.750

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:

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

edited Jul 11 '16 at 11:50

answered Jul 11 '16 at 11:38

Gary's Student

95,722
10
59
99

See! Finally someone with the answer instead of downvoting my OP without giving any suggestions. Hmm, this is really an interesting answer. But how if the numbers from notepad and then I wanna keep the original formats after pasting them in Excel? – Anastasiya-Romanova 秀 Jul 11 '16 at 11:43
I answer my own question using your suggestion in **EDIT#1**. Thanks. – Anastasiya-Romanova 秀 Jul 11 '16 at 14:19
Doesn't that mean Gary's student answered it? – Gary Evans Jul 12 '16 at 04:47

score 1 · Answer 3 · edited May 23 '17 at 11:58

~~Though this code is a bit messy, but at least~~ I try what Gary's Student suggests in his answer (See EDIT#1). Suppose that I want to place the original data in column A and the desired formats in column B, then I will:

Set Number Format in Column A as Text. To do this, click column A heading and look for the Number section of the Home menu tab then click the arrow in the lower right corner of the Number section and choose Text.
Copy (CtrlC) the original data in Notepad then click cell A1 and paste (CtrlV) the data.

Run this code:

Sub Keeping_Number_Format_1()
Last_Row = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Last_Row
   'Determine the number of the decimal places
   Dec_Number = Len(Cells(i, 1)) - InStr(Cells(i, 1), ".")

   'Return the number found in the input string (each cells in column A)
   Cells(i, 2) = Val(Cells(i, 1))

   'Set the data in column B to specific formats
   If Dec_Number = Len(Cells(i, 1)) Then
      Cells(i, 2).NumberFormat = "0"
   ElseIf Dec_Number = 1 Then
      Cells(i, 2).NumberFormat = "0.0"
   ElseIf Dec_Number = 2 Then
      Cells(i, 2).NumberFormat = "0.00"
   ElseIf Dec_Number = 3 Then
      Cells(i, 2).NumberFormat = "0.000"
   ElseIf Dec_Number = 4 Then
      Cells(i, 2).NumberFormat = "0.0000"
   'I add these lines just in case the given numbers exceed 4 decimal places.
   'You can add the lines as you wish.
   ElseIf Dec_Number = 5 Then
      Cells(i, 2).NumberFormat = "0.00000"
   ElseIf Dec_Number = 6 Then
      Cells(i, 2).NumberFormat = "0.000000"
   End If
Next i
End Sub

or one may run the shorter version of the above code. It employs the REPT function in Microsoft Excel so that one doesn't need to change the code in the future since it works for data with any number of decimal places.

Sub Keeping_Number_Format_2()
Last_Row = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Last_Row
   Dec_Number = Len(Cells(i, 1)) - InStr(Cells(i, 1), ".")
   Cells(i, 2) = Val(Cells(i, 1))

   If Dec_Number = Len(Cells(i, 1)) Then
      Cells(i, 2).NumberFormat = "0"
   Else
      Cells(i, 2).NumberFormat = "0." & WorksheetFunction.Rept("0", Dec_Number)
   End If

Next i
End Sub

How to keep trailing zeroes in formatting decimal numbers?

3 Answers3

Linked