excel 使用千位分隔符设置数字格式,印度和国际格式

ddrv8njm 于 2023-06-25 发布在其他

关注(0)|答案(2)|浏览(152)

我正试图格式化数字单元格，这是为印度客户在这种方式-24，33，000和外国客户喜欢2，433，000。
我尝试了这些数字格式，但我的输出格式随机变化。有时它是所需的格式，有时不是。你能帮帮我吗。真的很感激。谢谢

If InvoiceCurr <> "INR" Then
  For Each c In ThisWorkbook.Worksheets("Invoice").Range("E15:F29").Cells
    If Not (c.Value = "" Or c.Value = 0) Then
        c.Value = Val(c.Value)
        c.NumberFormat = "#,##0;-#,##0"
    End If
Next c

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = Val(ThisWorkbook.Worksheets("Invoice").Range("F30").Value)
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = Val(ThisWorkbook.Worksheets("Invoice").Range("F34").Value)

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = "#,##0;-#,##0"
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = "#,##0;-#,##0"
Else
 For Each c In ThisWorkbook.Worksheets("Invoice").Range("E15:F29").Cells
    If Not (c.Value = "" Or c.Value = 0) Then
        c.Value = Val(c.Value)
        c.NumberFormat = "#,##,##0"
    End If
Next c

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = Val(ThisWorkbook.Worksheets("Invoice").Range("F30").Value)
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = Val(ThisWorkbook.Worksheets("Invoice").Range("F34").Value)

'ThisWorkbook.Worksheets("Invoice").Range("F30") = Format(ThisWorkbook.Worksheets("Invoice").Range("F30"), "##\,##\,##\,##\,##\,##\,##\,##\,###.##")

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = "#,##,##0"
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = "#,##,##0"
End If

excel

来源：https://stackoverflow.com/questions/76513214/number-formatting-with-thousands-separator-indian-and-international-formatting

2条答案

按热度按时间

vwhgwdsa1#

替换为以下格式定义：

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = "##\,##\,##0"
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = "##\,##\,##0"

和/或

ThisWorkbook.Worksheets("Invoice").Range("F30").NumberFormat = "#\,##0;-#\,##0"
ThisWorkbook.Worksheets("Invoice").Range("F34").NumberFormat = "#\,##0;-#\,##0"

根据所需格式

赞(0）回复(0）举报 2023-06-25

jdg4fx2g2#

要在VBA中使用动态格式的完整实现，请研究我的文章：
Indian number formatting of Currency in Microsoft Access and Excel
代码：

' Format a Currency value in the Indian number format.
'
' Value can be any value between the minimum and maximum of Currency:
'   Value = CCur("-922337203685477.5808")
'   Value = CCur(" 922337203685477.5807")
'
' Example:
'   922337203685477.5807 -> "92,23,37,20,36,85,477.5807"
'
' Digits controls the decimal count:
'   If Digits is less than 0, floating decimals are used.
'   If Digits is between 0 and 4, fixed decimals are used.
'
' Note:
'   For fixed decimals less than four, parameter Value
'   should be rounded before passed to this function.
'
' Source:
'   https://en.wikipedia.org/wiki/Indian_numbering_system
'
' 2021-01-31. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatIndianCurrency( _
    ByVal Value As Currency, _
    Optional ByVal Digits As Integer = -1) _
    As String

    Const MaxDecimals   As Integer = 4
    
    Dim TextValue       As String

    If Digits < 0 Then
        ' Floating decimal.
        TextValue = _
            Format(Value, ";-") & _
            Format(Abs(Fix(Value)), _
                Right("##\,##\,##\,##\,##\,##\,", _
                ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & _
                "##0") & _
            IIf(Value - Fix(Value), LTrim(Str(Abs(Value - Fix(Value)))), "")
    Else
        ' Fixed decimal.
        If Digits > MaxDecimals Then
            Digits = MaxDecimals
        End If
        TextValue = _
            Format(Value, ";-") & _
            Format(Abs(Fix(Value)), _
                Right("##\,##\,##\,##\,##\,##\,", _
                ((Len(CStr(Abs(Fix(Value)))) - 2) \ 2) * 4) & _
                "##0") & _
            IIf(Value - Fix(Value), _
                Left(LTrim(Str(Abs(Value - Fix(Value)))) & "0000", Sgn(Digits) + Digits), _
                Mid(".0000", 2 - Sgn(Digits), Sgn(Digits) + Digits))
    End If
    
    FormatIndianCurrency = TextValue
    
End Function

赞(0）回复(0）举报 2023-06-25

我来回答

excel 使用千位分隔符设置数字格式,印度和国际格式

2条答案

相关问题

热门标签

最新问答