2024年4月2日发(作者：)
分享10种Excel人民币小写转大写的函数公式
=TEXT(INT(A5),”[DBNum2]“)&”元”&TEXT(INT(A5*10)-INT(A5)*10,”[DBNum2]“)&”
角”&TEXT(INT(A5*100)-INT(A5*10)*10,”[DBNum2]“)&”分”
=IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”
整
元
元
角
负
整
元
元
元
”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”
”&TEXT(RIGHT(A5),”[DBNum2]“)&”
”&IF(ISNUMBER(FIND(“.0″,A5)),”零
角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”
”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”
分”)),1,1,”
元
”)&TEXT(RIGHT(A5),”[DBNum2]“)&”
”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”
”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”
”&TEXT(RIGHT(A5),”[DBNum2]“)&”
”&IF(ISNUMBER(FIND(“.0″,A5)),”零
角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”
”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”
角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)))
=IF(A5<0,REPLACE(IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”
整
元
元
角
负
整
元
元
元
”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”
”&TEXT(RIGHT(A5),”[DBNum2]“)&”
”&IF(ISNUMBER(FIND(“.0″,A5)),”零
角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”
”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”
分”)),1,1,”
元
”)&TEXT(RIGHT(A5),”[DBNum2]“)&”
”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”
”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”
”&TEXT(RIGHT(A5),”[DBNum2]“)&”
”&IF(ISNUMBER(FIND(“.0″,A5)),”零
角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”
”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”
角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)))
=IF(ISNUMBER(A5),IF(TRUNC(A5)=0,IF(AND(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT
(TRUNC(A5,2),”0.00″))-1,1)=”0″,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”
元
零
”,”"),TEXT(TRUNC(A5),”[DBNUM2]“)&”
元”)&(IF(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT(TRUNC(A5,2),”0.00″))-1,1)=”0″,IF(
OR(TRUNC(A5)=0,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”",”
零”),TEXT(MID(A5,LEN(TEXT(TRUNC(A5,2),”0.00″))-1,1),”[DBNUM2]“)&”
角”))&(IF(RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″,”",TEXT(RIGHT(TEXT(TRUNC(A5,2)
,”0.00″),1),”[DBNUM2]“)&”分”)),IF(A5=”",”",”不是有效金额,请重新输入!”))
=IF(ISNUMBER(A5),IF(TRUNC(A5)=0,IF(AND(MID(TEXT(TRUNC(A5,2),”0.00″),LEN(TEXT
(TRUNC(A5,2),”0.00″))-1,1)=”0″,RIGHT(TEXT(TRUNC(A5,2),”0.00″),1)=”0″),”
元”,”"),(IF(A5<0,”负
零
”,”"))&TEXT(TRUNC(ABS(A5)),”[DBNUM2]“)&”
元”)&(IF(MID(TEXT(TRUNC(ABS(A5),2),”0.00″),LEN(TEXT(TRUNC(ABS(A5),2),”0.00″))-1
,1)=”0″,IF(OR(TRUNC(ABS(A5))=0,RIGHT(TEXT(TRUNC(ABS(A5),2),”0.00″),1)=”0″),”",”
零”),TEXT(MID(ABS(A5),LEN(TEXT(TRUNC(ABS(A5),2),”0.00″))-1,1),”[DBNUM2]“)&”
角”))&(IF(RIGHT(TEXT(TRUNC(ABS(A5),2),”0.00″),1)=”0″,”",TEXT(RIGHT(TEXT(TRUNC(
ABS(A5),2),”0.00″),1),”[DBNUM2]“)&”分”)),”不是有效金额,请重新输入!”)
=IF(A5=0,”",CONCATENATE(IF(INT(A5)=0,”",TEXT(INT(A5),”[DBNum2]G/通用格式
元”)),IF(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A5,2,1),1),
1,1))=0,”",IF(INT(A5)=0,”",”
零
角
”)),TEXT(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1)),”[DBNum2]G/通用格式
”)),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,”
整”,TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),”[DBNum2]G/通用格式分”))))
=IF(A5=0,”",CONCATENATE(IF(INT(ABS(A5))=0,”",TEXT(INT(ABS(A5)),”[DBNum2]G/通
用格式
元”)),IF(INT(MID(RIGHT(FIXED(ABS(A5),2,1),2),1,1))=0,IF(INT(MID(RIGHT(FIXED(A5,2
,1),1),1,1))=0,”",IF(INT(ABS(A5))=0,”",”
零
角
”)),TEXT(INT(MID(RIGHT(FIXED(A5,2,1),2),1,1)),”[DBNum2]G/通用格式
”)),IF(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1))=0,”
整”,TEXT(INT(MID(RIGHT(FIXED(A5,2,1),1),1,1)),”[DBNum2]G/通用格式分”))))
=IF(A5<0,”
整
元
元
负”,”")&IF(TRUNC(A5)=A5,TEXT(IF(A5<0,-A5,A5),”[DBNum2]“)&”元
”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(IF(A5<0,-A5,A5)),”[DBNum2]“)&”
”&TEXT(RIGHT(A5),”[DBNum2]“)&”
”&IF(ISNUMBER(FIND(“.0″,A5)),”零
角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”
”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”
角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”))
=IF(ISERROR(FIND(“.”,A5)),NUMBERSTRING(INT(A5),2)&”元
整”,IF(ISERROR(NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)),NUMBERSTRING(INT(A
5),2)&”元”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+1,1),2)&”
角”,NUMBERSTRING(INT(A5),2)&”元”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+1,1),2)&”
角”&NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)&”分”))
=IF(MOD(A5,1)=0,NUMBERSTRING(A5,2)&”元
整”,IF(ISERROR(NUMBERSTRING(MID(A5,FIND(“.”,A5)+2,1),2)),NUMBERSTRING(INT(A
5),2)&”元”&NUMBERSTRING(MID(MOD(A5,1),3,1),2)&”
角”,NUMBERSTRING(INT(A5),2)&”元”&NUMBERSTRING(MID(MOD(A5,2),3,1),2)&”
角”&NUMBERSTRING(MID(ROUND(MOD(A5,1),3),4,1),2)&”分”))
发布评论