Question

在excel中如何设置公式，把一串数字转换成单个大写字，会计发票打印用。

Answer 1

1. 公式为：

   ="金额大写:"&IF($D$12=0,"   拾   万   仟   佰   拾   元   角   分",TEXT($D$12/1%,"[dbnum2]_ 0拾_ 0万_ 0仟_ 0佰_ 0拾_ 0元_ 0角_ 0分"))
   

2. 如图所示：

   

   请点击输入图片描述

3. 第二种：

   =SUBSTITUTE(SUBSTITUTE(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]")&"分",IF(ROUND(A1,2)=0,"","整")),"零元零",""),"零元","")
   

   

4. 仅供参考，如有具体需求可追问。
   

Answer 2

百万位=TEXT(IF(A1>=1000000,MID(RIGHTB(A1*100,9),1,1),IF(A1<=1000000,"0","")),"[Dbnum2]0")

十万位=TEXT(IF(A1>=100000,MID(RIGHTB(A1*100,8),1,1),IF(A1<=100000,"0","")),"[Dbnum2]0")  

万位=TEXT(IF(A1>=10000,MID(RIGHTB(A1*100,7),1,1),IF(A1<=10000,"0","")),"[Dbnum2]0") 

千位=TEXT(IF(A1>=1000,MID(RIGHTB(A1*100,6),1,1),IF(A1<=1000,"0","")),"[Dbnum2]0") 

 百=TEXT(IF(A1>=100,MID(RIGHTB(A1*100,5),1,1),IF(A1<=100,"0","")),"[Dbnum2]0")

十位=TEXT(IF(A1>=10,MID(RIGHTB(A1*100,4),1,1),IF(A1<=10,"0","")),"[Dbnum2]0")  

元位=TEXT(IF(A1>=1,MID(RIGHTB(A1*100,3),1,1),IF(A1<=1,"0","")),"[Dbnum2]0")  

角位=TEXT(IF(A1>=0.1,MID(RIGHTB(A1*100,2),1,1),IF(A1<=0.1,"0","")),"[Dbnum2]0")  

分位=TEXT(IF(A1>=0.01,MID(RIGHTB(A1*100,1),1,1),IF(A1<=0.01,"0","")),"[Dbnum2]0")