靖勋 发表于 2021-11-12 14:45:00

求修改大写金额公式

IF(A11=0,"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A11)),"")&"元"&TEXT(RIGHT(TEXT(A11,".00"),2),"0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零",""))
找的现成的公式，转换成大写金额。但有一个问题，是当金额小于1是，比如￥0.10元，这个公式显示的是“”零元壹角整“，怎么修改公式，让它在金额小于1元时，大写金额不显示“零元”

最真实的我 发表于 2021-11-13 06:15:26

把最后的"零元零"改成“零元”？

全面小康 发表于 2021-12-27 05:56:34

=IF(A11<1,TEXT(A11*100,"0角0分"),IF(A11=0,"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A11)),"")&"元"&TEXT(RIGHT(TEXT(A11,".00"),2),"0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零",""))),公式前面再套个A11<1的条件就行

明媚的风子 发表于 2022-1-1 07:14:38

你的公式会显示成“壹角零分”~能麻烦再帮我完善下么~

刘佳琦 发表于 2022-1-21 18:10:24

=IF(A11<1,SUBSTITUTE(SUBSTITUTE(TEXT(A11*100,"0角0分"),"零分","整"),"零角",""),IF(A11=0,"",SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A11)),"")&"元"&TEXT(RIGHT(TEXT(A11,".00"),2),"0角0分"),"零角零分","整"),"零分","整"),"零角","零"),"零元零","")))

我不饿 发表于 2022-1-23 02:41:46

=IF(A11<1,"",TEXT(INT(ABS(A11)),"")&IF(INT(A11)=A11,"元整","元"))&TEXT(MID(A11,FIND(".",A11&".")+1,2),""&LOOKUP(LEN(ROUND(MOD(A11,1),2)),{1,3,4},{"";"0角整";"0角0分"}))
页: [1]
查看完整版本: 求修改大写金额公式