EXCEL函数人民币小写转大写，怎么实现万位为0的转大写万位也为零

Question

如题，EXCEL函数人民币小写转大写，怎么实现万位为0的转大写万位也为零。比如2703265.32，一般公式转换完成后为：贰佰柒拾万叁仟贰佰陆拾伍元叁角贰分，要实现转换后为：贰佰柒拾万零叁仟贰佰陆拾伍元叁角贰分。该怎么办？=IF(A1<0,"负","")&IF(ABS(A1)>1,TEXT(TRUNC(ABS(ROUND(A1,2))),"[DBNum2]")&"元","")&IF(ISERR(FIND(".",ROUND(A1,2))),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]"))&IF(ISERR(FIND(".0",TEXT(A1,"0.00"))),"角","")&IF(LEFT(RIGHT(ROUND(A1,2),3))=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整")=TEXT(TRUNC(FIXED(A2)),"[dbnum2]g/通用格式元;负[dbnum2]g/通用格式元")&SUBSTITUTE(TEXT(RIGHT(FIXED(A2),2),"[=0][dbnum2]整;[<10][dbnum2]零0分;[dbnum2]0角0分"),"零分",)以上函数都没法实现

Answer 1

写好了,只是按照你的要求改的,只考虑万位是零的情况,不考虑其他位是零的情况,代码如下:Option ExplicitFunction MyNumberString(objRange)
    Dim strNumber As String
    strNumber = Application.WorksheetFunction.Text(objRange, "[dbnum2]")
    
    Dim reg As Object
    Set reg = CreateObject("vbscript.regexp")
    reg.Global = True
    reg.MultiLine = False
    reg.ignorecase = True
    reg.Pattern = "^(.*)\.(.)?(.)?$"
    If reg.test(strNumber) Then
        Dim objMatch As Object
        Set objMatch = reg.Execute(strNumber)(0)
        
        If objMatch.submatches(1) = "" Then
            strNumber = objMatch.submatches(0) & "元整"
        ElseIf objMatch.submatches(2) = "" Then
            strNumber = objMatch.submatches(0) & "元" & objMatch.submatches(1) & "角零分"
        Else
            strNumber = objMatch.submatches(0) & "元" & objMatch.submatches(1) & "角" & objMatch.submatches(2) & "分"
        End If
    Else
        strNumber = strNumber & "元整"
    End If
    
    Dim lngNumber As Long
    lngNumber = CLng(objRange.Value)
    
    If lngNumber > 10000 And (lngNumber / 10000) Mod 10 = 0 Then
        strNumber = Replace(strNumber, "万", "万零")
    End If
    MyNumberString = strNumber
End Function下载地址:
链接：https://pan.baidu.com/s/1jUbon_v5egVXvD19cdNyQg 
提取码：k6td 运行示例: