Excel 人民币大写文字转换为小写金额

2017-9-28 10:58| 

各位老师：详见附件，需将人民币大写转换为小写金额，请教D列的公式如何写？       

也就是将C列大写还原为B列结果。如果公式不行的话，用VBA代码达成也行，谢谢！

自定义函数 -【金额转换】函数     金额转换(【第一参数】,【第二参数】)

【功能】: 人民币大写小写互相转化

【第一参数】金额(可为单元格或字符串)

【第二参数】转换类型(为可选参数)，当值为False(0)或者省略时表示小写转大写；当为值为True(1)时表示大写转小写；默认值为0(小写转大写)

【注意】  大写转小写时,金额为大写的字符串形式时,要在金额两边加【""】,如："壹万叁仟柒佰玖拾叁元整"

Function 金额转换(金额 As String, Optional 转换类型 As Boolean = False)

If Len(金额) = 0 Then 金额转换 = "": Exit Function

If 转换类型 = False And Not IsNumeric(金额) Then 金额转换 = "": Exit Function

If 转换类型 = True And IsNumeric(金额) Then 金额转换 = "": Exit Function

If 转换类型 = False Then

Dim RMBS As String

If 金额 = 0 Then 金额转换 = "零元整": Exit Function

RMBS = Replace(Replace(Application.Text(Round(金额, 2), "[DBnum2]"), ".", "元"), "-", "负")

RMBS = IIf(Left(Right(RMBS, 3), 1) = "元", Left(RMBS, Len(RMBS) - 1) & "角" & Right(RMBS, 1) & "分", IIf(Left(Right(RMBS, 2), 1) = "元", RMBS & "角", IIf(RMBS = "零", "", RMBS & "元整")))

RMBS = Replace(Replace(RMBS, "零元", ""), "零角", "")

金额转换 = RMBS

Else

Dim i As Byte, Style As Boolean, j As Byte, k As Byte, l As Byte, f As Byte

Style = True

f = InStr(金额, "负")

If f <> 0 Then 金额 = Replace(金额, "负", "")

金额 = Replace(Replace(Replace(Replace(金额, "整", ""), "亿", ")亿"), "万", ")万"), "兆", ")兆")

j = InStr(金额, "兆")

If j > 0 Then 金额 = "(" & 金额

k = InStr(金额, "亿")

If k > 0 Then 金额 = "(" & 金额

l = InStr(金额, "万")

If l > 0 Then 金额 = Left$(金额, k + IIf(k > 0, 1, 0)) & "(" & Right$(金额, Len(金额) - k - IIf(k > 0, 1, 0))

金额 = Replace(金额, "兆", "万")

For i = 1 To 11

金额 = Replace(金额, Mid("零壹贰叁肆伍陆柒捌玖 ", i, 1), i - 1)

金额 = Replace(金额, Mid("分角元拾佰仟万亿", i, 1), "*" & (10 ^ (i - 3)) & "+")

Next

金额 = Replace(Replace(金额, "+)", ")"), "+*", "*")

If f = 0 Then

金额转换 = Evaluate(Left(金额, Len(金额) - 1))

Else

金额转换 = "-" & Evaluate(Left(金额, Len(金额) - 1))

End If

End If

End Function

金额转换_自定义函数.gif(496.9 KB, 下载次数: 0)