vba 如有必要，带有千位分隔符和十进制的数字格式

Number Format with Thousands Separator and Decimal if Necessary

提问by PrinterDrop

I'm trying to create a custom format such that the number will be displayed with commas as the thousands separator. Where I am struggling to find a solution (both through trial and error as well as searching) with a decimal point ifthe number has one, but without a decimal pointif the number is whole.

我正在尝试创建一个自定义格式，以便数字将以逗号作为千位分隔符显示。如果数字有小数点，我正在努力寻找带有小数点的解决方案（通过反复试验以及搜索），但如果数字是整数，则没有小数点。

Here's what I would like to achieve:
- "123" displays as "123"
- "1234" displays as "1,234"
- "1234.5" displays as "1,234.5"
- "1234.56" displays as "1,234.56"
- "1234.567" displays as "1,234.57"

这是我想要实现的：
- “123”显示为“123”
- “1234”显示为“1,234”
- “1234.5”显示为“1,234.5”
- “1234.56”显示为“1,234.56”
- “1234.567”显示为“1,234.57”

Here's what I have tried so far, to no avail:

这是我迄今为止尝试过的方法，但无济于事：

Print Format(1234, "Standard") 'Displays "1,234.00"
Print Format(1234, "#,###.##") 'Displays "1,234."

These are not the desired results, as it unnecessarily displays the decimal. How can I access the decimal point when needed, and avoid when not, all while having the thousands separator?

这些不是想要的结果，因为它不必要地显示小数点。如何在需要时访问小数点，并在不需要时避免使用千位分隔符？

回答by Alexander Bell

You may complete this task by using the custom format Function CustomFormatin Excel VBA as shown below:

您可以使用CustomFormatExcel VBA 中的自定义格式函数来完成此任务，如下所示：

Sub TestFormatString()
    Debug.Print CustomFormat(123)
    Debug.Print CustomFormat(1234)
    Debug.Print CustomFormat(1234.5)
    Debug.Print CustomFormat(1234.56)
    Debug.Print CustomFormat(1234.567)
End Sub

Function CustomFormat(InputValue As Double) As String
    CustomFormat = Format(InputValue, "#,###.##")
    If (Right(CustomFormat, 1) = ".") Then
        CustomFormat = Left(CustomFormat, Len(CustomFormat) - 1)
    End If
End Function

The result shown below conforms to your requirements:

下面显示的结果符合您的要求：

123
1,234
1,234.5
1,234.56
1,234.57

Note: as you didn't specify how to display the numbers less that 1 (for example, 0.123) then consider two possible solutions:

注意：由于您没有指定如何显示小于 1 的数字（例如 0.123），请考虑两种可能的解决方案：

• CustomFormatVBA Function shown above will display it as .12
• in order to display it as 0.12, just change the format mask from "#,###.##" to "#,##0.##" in CustomFormatFunction, keeping the rest intact.

• CustomFormat上面显示的 VBA 函数将其显示为 .12
• 为了显示为0.12，只需将Function中的格式掩码从“ #,###.##”更改为“ #,##0.##”，CustomFormat其余部分保持不变。

Hope this may help.

希望这可能会有所帮助。

回答by PatricK

You can add Conditional Formattingfor those with a Decimal then set the default Number Format with 0 decimals.

您可以为具有小数的那些添加条件格式，然后将默认数字格式设置为 0 小数。

1. Add Conditional Formatting: with #,##0.##unless you want 0.5as .5
   
2. Set Default Number Format:
   
3. Done
   

1. 添加条件格式：用#,##0.##，除非你想0.5为.5
   
2. 设置默认数字格式：
   
3. 完毕