Question

这样可以把EXCEL中的数字转换成汉字大写

在同一个单元格里还有文字 例：人民币大写：71587.96
同一个单元格里前面还有文字 我要人民币大写也同时显示

Answer 1

假设人民币大写：71587.96 在B1 单元格
A1输入公式 =right(b1,len(b1)-find("：",b1,1))
A2输入下面公式
=IF(ABS(A1)<0.005,"",IF(A1<0,"负",)&IF(INT(ABS(A1)),TEXT(INT(ABS(A1)),"[dbnum2]")&"元",)&IF(INT(ABS(A1)*10)-INT(ABS(A1))*10,TEXT(INT(ABS(A1)*10)-INT(ABS(A1))*10,"[dbnum2]")&"角",IF(INT(ABS(A1))=ABS(A1),,IF(ABS(A1)<0.1,,"零")))&IF(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),TEXT(ROUND(ABS(A1)*100-INT(ABS(A1)*10)*10,),"[dbnum2]")&"分","整"))
A2 得到值 柒万壹仟伍佰捌拾柒元玖角陆分

Answer 2

1 点击鼠标反键，点击设置单元格格式。
2 在数字一栏找到分类里的特殊。并且点击。
3 点击后在右边栏里会看到中文大写数字，找到后点击。
4 点击后点确定。
然后在你选取的区域里输入数字，你会发现输入后直接变成大写字体了

Answer 3

=TEXT(A1,"[dbnum2]")

Answer 4

"人民币"&IF(A1>0,IF(ROUND(ABS(A1),2)=ROUND(ABS(A1),0),TEXT(ABS(A1),"[dbnum2]")&"元整",IF(ROUND(ABS(A1),2)=ROUND(ABS(A1),1),TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(RIGHT(ABS(A1),1),"[dbnum2]")&"角",TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(LEFT(RIGHT(ROUND(ABS(A1),2),2),1),"[dbnum2]")&"角"&TEXT(RIGHT(ROUND(ABS(A1),2),1),"[dbnum2]")&"分")),"负"&IF(ROUND(ABS(A1),2)=ROUND(ABS(A1),0),TEXT(ABS(A1),"[dbnum2]")&"元",IF(ROUND(ABS(A1),2)=ROUND(ABS(A1),1),TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(RIGHT(ABS(A1),1),"[dbnum2]")&"角",TEXT(INT(ABS(A1)),"[dbnum2]")&"元"&TEXT(LEFT(RIGHT(ROUND(ABS(A1),2),2),1),"[dbnum2]")&"角"&TEXT(RIGHT(ROUND(ABS(A1),2),1),"[dbnum2]")&"分")))

Answer 5

A1输入数字，在A2或其它单元格输入公式：=IF(A1="","",IF(ROUND(A1,2)=0,"零",IF(ROUND(A1,2)<0,"负","")&IF(ROUND(ABS(A1),2)<1,"",TEXT(INT(ROUND(ABS(A1),2)),"[dbnum2]")&"圆")&IF(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10=0,IF(INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10=0,"","零"),TEXT(INT(ROUND(ABS(A1),2)*10)-INT(ROUND(ABS(A1),2))*10,"[dbnum2]")&"角")&IF((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10)=0,"整",TEXT((INT(ROUND(ABS(A1),2)*100)-INT(ROUND(ABS(A1),2)*10)*10),"[dbnum2]")&"分")))
例：人民币大写：71587.96 成为：柒万壹仟伍佰捌拾柒圆玖角陆分

Answer 6

=IF(ISERROR(FIND(".",A1)),NUMBERSTRING(INT(A1),2)&"元整",IF(ISERROR(NUMBERSTRING(MID(A1,FIND(".",A1)+2,1),2)),NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MID(A1,FIND(".",A1)+1,1),2)&"角整",NUMBERSTRING(INT(A1),2)&"元"&NUMBERSTRING(MID(A1,FIND(".",A1)+1,1),2)&"角"&NUMBERSTRING(MID(A1,FIND(".",A1)+2,1),2)&"分"))
试一下这公式