如題，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分"),"零分",)
以上函式都沒法實作
uj5u.com熱心網友回復：
標準就是沒有“零”的啊。uj5u.com熱心網友回復：
你人民幣小寫轉大寫呼叫的是什么函式?能不能發出來?uj5u.com熱心網友回復：
寫好了,只是按照你的要求改的,只考慮萬位是零的情況,不考慮其他位是零的情況,代碼如下:
Option Explicit
Function 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
運行示例:
uj5u.com熱心網友回復：
怎么覺得本來沒錯的東西你一定要改成錯的呢？轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/25170.html
標籤：VBA
上一篇：CAA測量最大距離