Question

EXCEL中用什么函数可以将例子中的阿拉伯数字转为大写数字

如：9仟3佰6拾5元1角5分
我需要转换后变成：玖仟叁佰陆拾伍元壹角伍分

Answer 1

在B1中输入或复制粘贴下列公式

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,"零"),1,"壹"),2,"贰"),3,"叁"),4,"肆"),5,"伍"),6,"陆"),7,"柒"),8,"捌"),9,"玖")

下拉填充

公式反复用SUBSTITUTE函数，依次将0-9替换为零-玖。

Answer 2

估计只能用自定义函数了，代码如下：

Function XTOD$(ByVal S$)

Dim I%, X$

For I = 1 To Len(S)

X = Mid(S, I, 1)

If X Like "#" Then X = Choose(Val(X) + 1, "零", "壹", "贰", "叁", "肆", "伍", "陆", "柒", "捌", "玖")

XTOD = XTOD & X

Next

End Function

Answer 3

以你的阿拉伯数字在B11为例：改成936515这种形式输入以下公式。
=IF((INT(B11*10)-INT(B11)*10)=0,TEXT(INT(B11),"[DBNum2]G/通用格式")&"元"&IF((INT(B11*100)-INT((B11)*10)*10)=0,"整","零"&TEXT(INT(B11*100)-INT(B11*10)*10,"[DBNum2]G/通用格式")&"分"),TEXT(INT(B11),"[DBNum2]G/通用格式")&"元"&IF((INT(B11*100)-INT((B11)*10)*10)=0,TEXT((INT(B11*10)-INT(B11)*10),"[DBNum2]G/通用格式")&"角整",TEXT((INT(B11*10)-INT(B11)*10),"[DBNum2]G/通用格式")&"角"&TEXT(INT(B11*100)-INT(B11*10)*10,"[DBNum2]G/通用格式")&"分"))

Answer 4

以下表为例，

在B1输入A1中的数字

回车，单元格会跳到B2，按CTRL+E，会自动填充下面的单元格

下面不用说怎么做了吧

Answer 5

=IF(A7<0,"负","")&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(INT(ABS(A7)),"[dbnum2]")&"元"&TEXT(RIGHT(INT(ABS(A7*100)),2),"[dbnum2]0角0分")&"整","零角零分",""),"零分",""),"分整","分")