[已解决]如何把货币大写中的小数.变成点?

baotouren

    我在编辑表格的时候有时打工资,但是不知道怎么把小写变成大写而且小数点也得变成字.

请高人指点,谢谢!

[em44]

最佳答案

月排行榜 / 总排行榜

办公软件123

2010-5-6 13:17

我的公式，别看长，最适用

谢谢楼下的慧眼，现在公式修正了，请检验！！！~~~~~~

假定你的数据放在表格A1

=IF((TEXT(A2,"#0.00")>0)*INT(--TEXT(A2,"#0.00")),TEXT(INT(--TEXT(A2,"#0.00")),"[dbnum2]"),"")&CHAR(54433*((INT(--TEXT(A2,"#0.00")))>0)+9)&IF(LEFT(RIGHT(TEXT(A2,"#0.00"),2))<>"0",TEXT(LEFT(RIGHT(TEXT(A2,"#0.00"),2)),"[dbnum2]"),IF(AND(RIGHT(TEXT(A2,"#0.00"))<>"0",--TEXT(A2,"#0.00")>1),"零",""))&CHAR(48574*(LEFT(RIGHT(TEXT(A2,"#0.00"),2))<>"0")+9)&IF(RIGHT(TEXT(A2,"#0.00"))<>"0",TEXT(RIGHT(TEXT(A2,"#0.00")),"[dbnum2]"),"")&CHAR(7717*(RIGHT(TEXT(A2,"#0.00"))="0")+47062-54770*(A2=0))

[此贴子已经被作者于2010-5-7 11:50:32编辑过]

跳转到最佳答案楼层

zqrv373

描述的不是很清楚，有没有具体表格呀

tkgg93

在论坛搜金额大写

办公软件123

我的公式，别看长，最适用

谢谢楼下的慧眼，现在公式修正了，请检验！！！~~~~~~

假定你的数据放在表格A1

=IF((TEXT(A2,"#0.00")>0)*INT(--TEXT(A2,"#0.00")),TEXT(INT(--TEXT(A2,"#0.00")),"[dbnum2]"),"")&CHAR(54433*((INT(--TEXT(A2,"#0.00")))>0)+9)&IF(LEFT(RIGHT(TEXT(A2,"#0.00"),2))<>"0",TEXT(LEFT(RIGHT(TEXT(A2,"#0.00"),2)),"[dbnum2]"),IF(AND(RIGHT(TEXT(A2,"#0.00"))<>"0",--TEXT(A2,"#0.00")>1),"零",""))&CHAR(48574*(LEFT(RIGHT(TEXT(A2,"#0.00"),2))<>"0")+9)&IF(RIGHT(TEXT(A2,"#0.00"))<>"0",TEXT(RIGHT(TEXT(A2,"#0.00")),"[dbnum2]"),"")&CHAR(7717*(RIGHT(TEXT(A2,"#0.00"))="0")+47062-54770*(A2=0))

[此贴子已经被作者于2010-5-7 11:50:32编辑过]

风雨云

QUOTE:

以下是引用办公软件123在2010-5-6 13:17:00的发言：

我的公式，别看长，最适用

假定你的数据放在表格A1

=IF((TEXT(A1,"#0.00")>0)*INT(--TEXT(A1,"#0.00")),TEXT(INT(--TEXT(A1,"#0.00")),"[dbnum2]"),"")&CHAR(54433*((INT(--TEXT(A1,"#0.00")))>0)+9)&IF(LEFT(RIGHT(TEXT(A1,"#0.00"),2))<>"0",TEXT(LEFT(RIGHT(TEXT(A1,"#0.00"),2)),"[dbnum2]"),IF(RIGHT(TEXT(A1,"#0.00"))<>"0","零",""))&CHAR(48574*(LEFT(RIGHT(TEXT(A1,"#0.00"),2))<>"0")+9)&IF(RIGHT(TEXT(A1,"#0.00"))<>"0",TEXT(RIGHT(TEXT(A1,"#0.00")),"[dbnum2]"),"")&CHAR(7717*(RIGHT(TEXT(A1,"#0.00"))="0")+47062-54770*(A1=0))

学习公式……

fgt258

做个记号，回去再看看

criteria

如果A列是金额的话，可以输入公式：

=TEXT(INT(A1),"[dbnum2]")&"元"&TEXT(LEFT(RIGHT(ROUND(A1,2),2),1),"[dbnum2]")&"角"&TEXT(RIGHT(ROUND(A1,2),1),"[dbnum2]")&"分"

蒸蒸日上

楼主问题解决没，请你给4楼评一个最佳答案！

xiachenglin

四楼真厉害

冷月诗魂

四楼的公式即长也不太准确，比如：0.01显示出来的结果是“零壹分”等