NumberStr = NumberStr & "元整"

Else

NumberStr = Replace(NumberStr, ".", "元")

If DotLoc2 = 2 Then

NumberStr = NumberStr & "分"

If Mid(NumberStr, DotLoc1 + 1, 1) <> "零" Then

NumberStr1 = Mid(NumberStr, 1, DotLoc1 + 1) & "角"

NumberStr2 = Right(NumberStr, 2)

NumberStr = NumberStr1 & NumberStr2

End If

Else

NumberStr = NumberStr & "角整"

End If

End If

On Error Rescom Next

UNumber = Replace(NumberStr, "零元", "")

End Function

方法2、

=IF(TRUNC(H16)=H16,TEXT(H16,"[DBNum2]G/通用格式")&"元整",TEXT(TRUNC(H16),"[DBNum2]G/通用格式"&"元"))&IF(AND(TRUNC(H16)<>H16,RIGHT(TRUNC(H16*10))<>"0"),TEXT(TRUNC(MOD(H16*10,10)),"[DBNum2]G/通用格式")&"角","")&IF(AND(RIGHT(TRUNC(H16*10))="0",TRUNC(H16)<>H16),"零","")&IF(TRUNC(H16*10)<>H16,TRUNC(H16*10)=H16*10), "整","")

方法3、

=IF(F10=0,"",CONCATENATE(IF(INT(F10)=0,"",TEXT(INT(F10),"[DBNum2]G/通用格式元")),IF(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"",IF(INT(F10)=0,"","零")),TEXT(INT(MID(RIGHT(FIXED(F10,2,1),2),1,1)),"[DBNum2]G/通用格式角")),IF(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1))=0,"整",TEXT(INT(MID(RIGHT(FIXED(F10,2,1),1),1,1)),"[DBNum2]G/通用格式分"))))

方法4、

人民币大写的函数公式，可正负，最多两位小数。

=IF(A1<0,"负","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))

方法5

修改一下4：根据剑魔兄的测试，发现有一个问题，如-100.05，现修正如下：

=IF(A1<0,"负","")&IF(TRUNC(A1)=A1,TEXT(IF(A1<0,-A1,A1),"[DBNum2]")&"元整",IF(TRUNC(A1*10)=A1*10,TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&TEXT(RIGHT(A1),"[DBNum2]")&"角整",TEXT(TRUNC(IF(A1<0,-A1,A1)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".0",A1)),"零",TEXT(LEFT(RIGHT(A1,2)),"[DBNum2]")&"角")&TEXT(RIGHT(A1),"[DBNum2]")&"分"))

方法6

=IF(A1<0,"负",)&TEXT(TRUNC(ABS(A1)),"[DBNum2]G/通用格式")&"元 "&IF(ROUND(A1,3)=ROUND(A1,),"整",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]G/通用格式")&"角"&IF(ROUND(A1,3)=ROUND(A1,1),"整",TEXT(RIGHT(ROUND((A1*100),),1),"[DBNum2]G/通用格式")&"分"))

方法7

无条件舍去： =CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(A1)=0,"零",TRUNC(ABS(A1))),"[DBNum2]")&"元",IF(OR(AND(ABS(A1)<0.1,TRUNC(A1)=A1),RIGHT(INT(ABS(A1)*100),2)="00"),"",TEXT(RIGHT(TRUNC(A1*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(A1*10),1)="0","","角"),IF(OR(TRUNC(A1*10)-(A1*10)=0,RIGHT(TRUNC(A1*100),1)="0"),"整",TEXT(RIGHT(TRUNC(A1*100),1),"[DBNum2]")&"分"))

小数点后两位四舍五入: =CONCATENATE(IF(A1<0,"负",""),TEXT(IF(TRUNC(ROUND(A1,2))=0,"零",TRUNC(ABS(ROUND(A1,2)))),"[DBNum2]")&"元",IF(TRUNC(ROUND(A1,2))=ROUND(A1,2),"",TEXT(RIGHT(TRUNC(ROUND(A1,2)*10),1),"[DBNum2]")),IF(RIGHT(TRUNC(ROUND(A1,2)*10),1)="0","","角"),IF(OR(TRUNC(ROUND(A1,2)*10)-(ROUND(A1,2)*10)=0,RIGHT(ROUND(A1,2),1)="0",TRUNC(ROUND(A1,2))=ROUND(A1,2)),"整",TEXT(RIGHT(ROUND(A1,2),1),"[DBNum2]")&"分"))

方法8

无条件舍去： =IF(A1<0,"负","")&SUBSTITUTE(TEXT(TRUNC(A1),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",TRUNC(A1,2))),TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",A1)),"","角"),"")&IF(LEFT(RIGHT(TRUNC(A1,2),3),1)=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整"),"-",)

小数点后两位四舍五入: =IF(A1<0,"负","")&SUBSTITUTE(TEXT(TRUNC(ROUND(A1,2)),"[DBNum2]")&"元"&IF(ISNUMBER(FIND(".",ROUND(A1,2))),TEXT(RIGHT(TRUNC(ROUND(A1,2)*10)),"[DBNum2]")&IF(ISNUMBER(FIND(".0",ROUND(A1,2))),"","角"),"")&IF(LEFT(RIGHT(TRUNC(ROUND(A1,2),2),3),1)=".",TEXT(RIGHT(ROUND(A1,2)),"[DBNum2]")&"分","整"),"-",)

方法9

再简化如后，请大家试试。

无条件舍去：

=IF(A1<0,"负","")&TEXT(TRUNC(ABS(A1)),"[DBNum2]")&"元"&IF(ISERR(FIND(".",TRUNC(A1,2))),"",TEXT(RIGHT(TRUNC(A1*10)),"[DBNum2]"))&IF(RIGHT(TRUNC(A1*10))="0","","角")&IF(LEFT(RIGHT(TRUNC(A1,2),3))=".",TEXT(RIGHT(TRUNC(A1,2)),"[DBNum2]")&"分","整")

小数点后两位四舍五入:

=IF(A1<0,"负","")&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]")&"分","整")

方法10(好)

Function RMB_DX(money As Variant)

Dim m1 As Long, n1 As Long, n2 As Integer, n3 As Integer

m1 = Application.WorksheetFunction.Round(money * 100, 0)

n1 = Int(m1 / 100)

n2 = Int(m1 / 10) - n1 * 10

n3 = m1 - n1 * 100 - n2 * 10

If n3 = 0 Then

RMB_DX = "整"

Else

RMB_DX = Application.WorksheetFunction.Text(n3, "[DBnum2]") & "分"

End If

If n2 = 0 Then

If n1 <> 0 And n3 <> 0 Then RMB_DX = "零" & RMB_DX

Else

RMB_DX = Application.WorksheetFunction.Text(n2, "[DBnum2]") & "角" & RMB_DX

End If

If n1 <> 0 Or m1 = 0 Then

RMB_DX = Application.WorksheetFunction.Text(n1, "[DBnum2]") & "元" & RMB_DX

End If

End Function

方法11

unction rmbdx(value, Optional m = 0)

'支持负数,支持小数点后的第三位数是否进行四舍五入处理

'默认参数为0,即不将小数点后的第三位数进行四舍五入处理

'redwin增改 2002-10-11

'Application.Volatile True

On Error Rescom Next

Dim a

If value < 0 Then

a = "负"

Else

a = ""

End If

'当参数m不输入(默认为0)或为0时,小数点后的第三数不进行四舍五入处理

'当参数m为1或其它数值时,小数点后的

松语文学免费小说阅读_www.16sy.com