带有千位分隔符和十进制(如有必要)的数字格式 [英] Number Format with Thousands Separator and Decimal if Necessary
问题描述
我正在尝试创建一种自定义格式,以使该数字以逗号作为千位分隔符显示.我在哪里努力寻找解决方案(通过反复试验以及搜索),如果数字有一个,则用小数点表示,如果没有数字,则用小数点表示.数字是完整的.
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 if the number has one, but without a decimal point if the number is whole.
这是我想要实现的目标:
-"123"显示为"123"
-"1234"显示为"1,234"
-"1234.5"显示为"1,234.5"
-"1234.56"显示为"1,234.56"
-"1234.567"显示为"1,234.57"
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"
这是我到目前为止尝试过的,但无济于事:
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?
推荐答案
您可以使用Excel VBA中的自定义格式函数 CustomFormat
来完成此任务,如下所示:
You may complete this task by using the custom format Function CustomFormat
in Excel VBA as shown below:
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
注意 :由于您没有指定如何显示小于1(例如0.123)的数字,所以考虑了两种可能的解决方案:
Note: as you didn't specify how to display the numbers less that 1 (for example, 0.123) then consider two possible solutions:
- 上面显示的
-
CustomFormat
VBA函数会将其显示为.12 - 为了将其显示为0.12,只需将格式掩码从"
#,###.##
"更改为"#,## 0.##
>"在CustomFormat
函数中,其余部分保持不变.
CustomFormat
VBA Function shown above will display it as .12- in order to display it as 0.12, just change the format mask from "
#,###.##
" to "#,##0.##
" inCustomFormat
Function, keeping the rest intact.
希望这会有所帮助.
这篇关于带有千位分隔符和十进制(如有必要)的数字格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!