最重要的文本函数text的进阶用法2

今天来个完整版的大写金额的转换公式。由于种类比较繁杂，分为下面几种情况：1元以上的，1元以下和0元的，而且还区分正负，如下图所示。

在B2单元格中输入公式=IF(A2,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXT(A2,";负")&TEXT(INT(ABS(A2)),"[dbnum2]")&"元"&TEXT(MOD(ABS(A2),1)/1%,"[=0]整;[>9][dbnum2]0角0分;[dbnum2]零0分"),"零分","整"),"零元零",),"零元",),"")，向下填充。公式很长，不要被吓到哦

。

下面分步说明，可以看到公式里有3个text函数用&连接起来。第1个text判断正负，第2个text将整数部分显示为大写金额，第3个text将小数部分显示为大写金额。

下面先看第1个text，在C11单元格中输入公式=TEXT(A11,";负")，向下填充，得到C列的结果。可以看到正数和0显示为空，负数显示为负。

再看第2个text，在D11单元格中输入公式=TEXT(INT(ABS(A11)),"[dbnum2]")，向下填充，得到D列的结果，将整数部分显示为大写。

abs是绝对值函数，将负数变成正数，如E列所示；int是向下取整函数，对abs的结果取整数部分，如F列所示。

第3个text函数，在G11单元格中输入公式=TEXT(MOD(ABS(A11),1)/1%,"[=0]整;[>9][dbnum2]0角0分;[dbnum2]零0分")，向下填充。得到G列的结果，将小数部分显示为大写。

先看text的第1参数MOD(ABS(A11),1)/1%，abs取绝对值，MOD是求余函数，除以1，就可以将小数部分提取出来，如H列所示。后面除以1%，就相当于乘以100，将其转成整数才好设置自定义格式，如I列所示。

text的第2参数有3部分代码，都是条件判断。[=0]整;[>9][dbnum2]0角0分;[dbnum2]零0分，第1部分代码是当小数部分扩大100倍后等于0时，显示为整，这样整数后面就可以连接整，如45所示。第2部分代码是判断小数部分扩大100倍后大于9的就显示为0角0分，实际就是角的位置不为0，如0.15小数部分显示为壹角伍分。第3部分代码是小于9且不为0的显示为零0分，如-23.08小数部分显示为零捌分。

然后将3个text用&连接起来，在第2个text后面连接个"元"，结果如下。可以看到有些地方是不正确的，我用红框标记出来。第1个红框的零分应该替换为整，第2个红框的零元零应该替换为空，第3个红框的零元也应该替换为空，第4个红框显示为零元整是因为它的金额为0，可以将其显示为空，也可以设置为你想要的内容。

所以，你可以看到用了3次substitute，分别替换零分，零元零，零元。这里注意，先要替换零元零，再替换零元，否则结果会出错。最外层用了个if函数进行判断，如果金额为0，让其显示为空。

第2种方法我在网上看到的，公式为=SUBSTITUTE(SUBSTITUTE(IF(-RMB(A2),TEXT(A2,";负")&TEXT(INT(ABS(A2)+0.5%),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")，公式较短，思路也很好。

对其进行精简改为=SUBSTITUTE(SUBSTITUTE(IF(A2,TEXT(A2,";负")&TEXT(INT(ABS(A2)),"[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整"),),"零角",IF(A2^2<1,,"零")),"零分","整")。

如果对你有所帮助或启发，请打赏或分享一下，你的支持就是我最大的动力！

关注解锁更多函数的用法