10种Excel人民币小写转大写的函数公式

分享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]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)),1,1,”负”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,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]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,TEXT(LEFT(RIGHT(A5,2)),”[DBNum2]“)&”角”)&TEXT(RIGHT(A5),”[DBNum2]“)&”分”)),1,1,”负”),IF(TRUNC(A5)=A5,TEXT(A5,”[DBNum2]“)&”元整”,IF(TRUNC(A5*10)=A5*10,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&TEXT(RIGHT(A5),”[DBNum2]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,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]“)&”角整”,TEXT(TRUNC(A5),”[DBNum2]“)&”元”&IF(ISNUMBER(FIND(“.0″,A5)),”零”,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)&”分”))。