人民币大写公式中，怎样去除零角、零分

wupeiqi

公式：=TEXT(LEFT(RMB(A1),LEN(RMB(A1))-3),"[dbnum2]G/通用格式元") & TEXT(RIGHT(RMB(A1),2),"[dbnum2]0角0分;;整")
① 如A1=158.06，壹佰伍拾捌元零角陆分
② 如A1=158.60，壹佰伍拾捌元陆角零分
怎样去除①中的角与②中的零分，①中为壹佰伍拾捌元零陆分；②中为壹佰伍拾捌元陆角

zyouong

=IF(A1<=0,,TEXT(INT(A1),"[dbnum2]G/通用格式")&"元"&IF(INT(A1*10)-INT(A1)*10=0,IF(INT(A1)*(INT(A1*100)-INT(A1*10)*10)=0,"","零"),TEXT(INT(A1*10)-INT(A1)*10,"[dbnum2]")&"角")&IF((INT(A1*100)-INT(A1*10)*10)=0,"整",TEXT((INT(A1*100)-INT(A1*10)*10),"[dbnum2]")&"分"))

9999djx

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

tjshenjian

1. =SUBSTITUTE(SUBSTITUTE(TEXT(INT(A1),"[dbnum2]g/通用格式元")&IF(MOD(A1,1),TEXT(RIGHT(A1/1%,2),"[dbnum2]0角0分"),),"零角","零"),"零分",)

复制代码