[分享]人民币大写函数TEXT终极版 1行(136字符)

wshcw

版主辛苦,收藏学习了,特别喜欢163字符这个自定义函数.

QUOTE:

 Function dx(M)
    dx = Replace(Replace(Replace(Join(Application.Text(Split(Format(M, " 0. 0 0;负 0. 0 0;   ")), ["[DBnum2]"&{0,"","圆0角;;圆零","0分;;整"}]), a), "零圆零", a), "零圆", a), "零整", "整")
   '163字符A 无缺陷 负数显示 “负”   负伍圆陆角伍分 
End Function

另:大写公式中可用INT(RMB(ABS(A1)))取代INT(ABS(A1)+0.5%),字符数也不变.

[此贴子已经被作者于2008-8-14 23:22:12编辑过]

papapa1001

=IF(A12=0,"",IF(ABS(A12)>=1,IF(A12=TRUNC(A12),TEXT(TRUNC(A12),"[DBNUM2]")&"圆整",IF(A12*10=TRUNC(A12*10),TEXT(TRUNC(A12),"[DBNUM2]")&"圆"&TEXT(ABS(TRUNC(A12*10)-TRUNC(A12)*10),"[DBNUM2]")&"角整",TEXT(TRUNC(A12),"[DBNUM2]")&"圆"&IF(ABS(A12)-TRUNC(ABS(A12))<0.1,"零",TEXT(ABS(TRUNC(A12*10)-TRUNC(A12)*10),"[DBNUM2]")&"角")&TEXT(FIXED(ABS(A12)*100-INT(ABS(A12)*10)*10,0),"[DBNUM2]")&"分")),IF(ABS(A12)*10-INT(ABS(A12)*10)=0,TEXT(TRUNC(A12*10),"[DBNUM2]")&"角整",IF(ABS(ABS(A12)-TRUNC(ABS(A12)))<0.1,"",TEXT(TRUNC(A12*10),"[DBNUM2]")&"角")&TEXT(RIGHT(A12,1),"[DBNUM2]")&"分")))

太长了！

还是下面的好。

=SUBSTITUTE(SUBSTITUTE(TEXT(TRUNC(FIXED(A1)),"[dbnum2]G/通用格式元;负[dbnum2]G/通用格式元;"&IF(A1>-0.5%,,"负"))&TEXT(RIGHT(FIXED(A1),2),"[dbnum2]0角0分;;"&IF(ABS(A1)>1%,"整",)),"零角",IF(ABS(A1)<1,,"零")),"零分","整"） 

itianyi01

进来留名

dylane

哪个公式都不成功|||

ldy

QUOTE:

以下是引用dylane在2008-9-4 15:48:28的发言：
哪个公式都不成功|||

你的excel是英文版的？

把你的文件发上来看看

dy_ysl

精彩，，，学无止境

薄才多学

不知贰圆零捌角玖分算不算错

薄才多学

引用单元格为空时显示“#VALUE!”看着不太舒服，为“0”或“0.004”显示为“空白”个人觉得也不严谨。

wanlongwyp

谁能做个人民币大写的加载宏文件

jilijun

都精简到一行了，收藏学习了