excel函数公式汉字转数字（Excel基础知识-详解文本函数逆转财务大写数字编写公式全过程）

上文说到第二种的公式=mid(A3,search("-",A3) 1,search("-",substitute(A3,"LTE-","",1))-1)存在缺点，首先我们需要确定公式中的缺点在哪？我们选择过滤“LTE-”这个不具备普遍意义，哪我们就把他改成通用的格式就可以啦！其实也挺简单的，就是将LET-用公式自动匹配出来就可以啦，即代替的公式为left(A3,search("-",A3))，把这个公式带入上面的公式即：=mid(A3,search("-",A3) 1,search("-",substitute(A3,left(A3,search("-",A3)),"",1))-1)，这样就优化完成了，上文的问题解决完了，开始我们今天的内容吧！

要说Excel对单元格数据有整形能力的大师有两位，我们熟知的是“自定义单元格格式”下的【数据】中的“自定义”自大师，还有一位就是我们今天要聊的TEXT的T大师，两位大师技艺精湛，都能整形成你需要的样子，不过他们在使用的技术上还是有区别的，下面就来看看详细介绍一下他们的不同！

自大师自称习得上古秘术易容术，不用动刀，见效快，不过缺点是真实的容貌并未改变，而T大师则不同，他是通过高科技做的机器人偶，你可以自己设定机器人偶的外形，并通过网络控制人偶的行为（电影：机器代理人），它的优点是产生了新的真实容貌，如果你要整形，你会选自大师还是T大师呢？

TEXT用法

TEXT函数的结构图

​源文本：主要处理的原数据就两种，一种为可处理的数据：文本，数字，逻辑值；一种为不可处理的数据：错误编码；不论你是写的表达式，函数还是常量，定义的名称，引用单元格，它运行后的结果最终还是会返回文本，数字，逻辑值或错误编码；不能处理错误编码是大部分函数的通性，遇到错误编码直接输出，不经任何处理；

格式编码：特征必须由双引号""包裹才有效，而且支持符合自定义格式编码的规则，至于自定义格式编码规则和用法详细了解可以翻看之前的文章Excel基础知识-详解自定义格式编码规则​​​​和Excel基础知识-你的自定义格式技能该升级啦！ ​​​​,都有详细的讲解，这里就不再赘述了。

说了这么多，你对TEXT函数的了解也只是停留在字面上，哪我们就上个简单的例子，做一下用前热身吧！在自定义格式中，我们用的最多的就是定义数字和日期的显示格式，这里就拿一个日期来举例吧，比如我们想把2019-03-04日期变成2019年3月4日用TEXT函数怎么写呢？公式=TEXT("2019-03-04","YYYY年M月D日");哪如果我们想要2019年03月04日就需要将公式改为=TEXT("2019-03-04","YYYY年MM月DD日");想得到对应完整星期几对应的公式为=TEXT("2019-03-04","AAAA")，只想返回大写数字几公式=TEXT("2019-03-04","AAA");

注意项：一个小常识就是如果你输入只有日期，并没有具体的时间，在设置显示的样式加入了时间格式编码，则会返回当天的0点0分0秒；比如上面的公式变为=TEXT("2019-03-04","YYYY年MM月DD日 HH:MM:SS"),则返回2019年03月04日 00:00:00；

转化为大写

Excel内置了默认的小写转化大写有2种格式编码分别：[DBNum1]中文小数字,[DBNum2]中文大写数字和一种[DBNum3]阿拉伯数字和中文单位结合类型；123456用TEXT函数转化成中文数字为公式为：=TEXT(123456,"[DBNum1]")结果为：一十二万三千四百五十六，公式变为：=TEXT(123456,"[DBNum2]")结果为：壹拾贰万叁仟肆佰伍拾陆，则=TEXT(123456,"[DBNum3]")结果为１十２万３千４百５十６；这三种类型的格式编码使用的场景不同，第一种用于年份，第二种则多用于财务，第三种确实很少用，只做了解之用就可以了，哪么问题来，第一种表示年月日用TEXT函数如何写呢？

其实写法也挺简单的，还是上面的案例2019-03-04显示为二〇一九年三月四日，公式编写为=TEXT("2019-03-04","[DBNum1]yyyy年m月d日")，同样的效果还有一种写，这种写法跟字母e有关，我们知道E的表示科学计数法的标记，而小写e则表示四位数年份与YYYY效果相同，公式可调整为=TEXT("2019-03-04","[dbnum1]e年m月d日"),说完了日期，接下来我们就一起来看看会计用的中文大写数字怎么写公式吧！

默认的[DBNum2]为转为财务使用的大写金额提供方便，我们只需将不符合中文习惯的一些冗余的字过滤掉就可，替换一些中文书写的习惯的字就可以了，首先我们先来了解一下中国票据使用中文大写数字的一些要求：

1：中文金额数字到“元”为止的，并在“元”之后增加的整或正字，还有更小的单位角或分都可以不写整或正字；

2：金额数字中，整数部分，每四位数一个度量等级，通用单位为个，十，百，千，个位为零或个位连续几位都为零的情况，通常可以不写，如果其它位为零或者连续为零的情况，需要保留一个零；若金额整数为零的可以不写零，直接写几角几分或几分；

我们熟悉这些规则之后，我们先弄个例子一起看一下，默认的中文大写格式编码转化的效果：1003.28，用TEXT函数公式为=text(1003.28,"[DBNum2]") 结果为：壹仟零叁.贰捌，为了仔细确认一下优化的规则，需要多测试几组数据如下图：

阿拉伯数字转中文大写

我从测试的数据来看，整数部分的转化成大写的基本没有什么问题，只需在整数后面加上圆字或“圆整/圆正”就行了，小数部分我需要去掉小数点并把后两位转化成几角几分，在转换小部分的时候，需要注意几个规则：如果0.04，0.3，0.34，这时我们需要转化为肆分，叁角，叁角肆分，去掉里面零，只有1.03情况才会转化壹圆零三分；还有一种情况就是有负号-情况，需要转化为负字，规则大概就这些了，接下来随着我的思路一起看看公式是怎么编写的吧！

编写思路

在编写公式之前，我们需要将规则抽象成编公式的思想。首先从大的方面先来处理正负数的问题，不论我们用什么方法判断是用left截取第1字符来判断是不是“-”或是用find,search函数来查找都不是最终的目的，我这里采用SUBSTITUTE只需将“-”替换为负来处理，正数没有自然也替换不了，处理好正负数的问题接着，进一步区分整数和带有小数的整数吧！

因为只有整数的部分的处理起来就比较简单啦，直接在尾部加上“圆整”或“圆正”就可以啦，如果带有小数就只能在整数部分加“圆”了，接着要想的问题来了，怎么判断是否带有小数呢？没错小数点“.”,根据之前学的知识，判断我们能用两种，一种find和search方法判断负数的方法类似，一种就是利用lenb函数，检查一下长度是奇数还是偶数，奇数为带有小数的，偶数的则是整数，还有一种就是通过2len-lenb等英文的长度，0则为整数，1则为带有小数的整数，原理可以文章：Excel基础知识-文本长度函数和提取函数。

区分出来的带小数的整数部分，我们需要处置一种特殊的带小数的整数，就是只有个数位且为零的3种情况，0.04，0.4，0.34，个数位为零的情况，是不需要输出个位的数字零的，甄别这种情况有两种方法，一种讨巧，根据Excel的数字原则，首位数字不能为零，为零的只能是小数，所以一个left函数截取1位字符判断是不是“零”就可以啦，另一种就比较常规了，用find和search函数定位“.”的位置，只有为2的情况，再需要判断个位是否为零情况，是零显示空，不是零显示原来字符就行了，有了这些思路，就可以开始编写公式了，至于你是按逆思维的方式，还是按正常的思维顺序编写就看你习惯了，我们尝试逆思维的方式编写公式吧！

编写公式

首先我们就先处理整数带小数的部分，用left函数来处理，是零的情况，显示为空，不是零则返回并拼接源，假设转化为数字的单元格位置在A2,公式为=if(left(text(A2,"[dbnum2]"),1)="零","",left(text(A2,"[dbnum2]"),1)&"圆"),接着我们俩处理小数第一位数字，如果是零有两种情况处理，第一种就是个位不为零时，小数即便是零也需要显示但没有单位，第二种个位是零，它可不显示，如果它不是零就需要显示它拼合角了，公式为：

=if(mid(text(A2,"[dbnum2]"),find(".",text(A2,"[dbnum2]")) 1,1)="零",if(left(text(A2,"[dbnum2]"),1)="零","","零"),mid(text(A2,"[dbnum2]",find(".",text(A2,"[dbnum2]")) 1,1)&"角")

下面该处理分位的小数，这个就比较简单直接用right函数获取字符，然后拼接分就可以了，不过我们首先要确认一下分位小数是否存在？鉴别方式就是len(text(A2,"[dbnum2]"))减去小数点的位置等2，则表示小数点后有两位数字，即最后一位就是分啦，公式为：=if(len(text(A2,"[dbnum2]")-find(".",text(A2,"[dbnum2]"))=2,right(text(A2,"[dbnum2]"),1)&"分","")

编写个位即带小数的部分，就来编写一下整数的部分的公式，就从获取第一字符的不为零情况开始讲起，第一位不为零，范围就扩大到1位到多位整数；不论是1位到多位都需要在后面加上“圆”字，不同的是截取的位置不同，多位的截取的位置公式为：left(text(A2,"[dbnum2]"),find(".",text(A2,"[dbnum2]")-1),这部分整体的公式需要嵌入第一个公式=if(left(text(A2,"[dbnum2]"),1)="零","",if(find(".",a2)>2,left(text(A2,"[dbnum2]"),find(".",A2)-1)&"圆",left(text(A2,"[dbnum2]"),1)&"圆");

我们把上面三部分的公式都用&连接符连起来就成了我们要的中文大写数字了，你这根本就没有写只有的整数的部分啊？确实，没有小数的部分太容易处理，只要将text(A2,"[dbnum2]")&"圆整"就可以啦，可能聪明的已经想到，没有错，就是没有小数点的时候，上面的公式就会出错，哪我们只需用iferror函数，将上面三部分拼接在一起的公式作为第一参数，公式为=iferror(if(left(text(A2,"[dbnum2]"),1)="零","",if(find(".",a2)>2,left(text(A2,"[dbnum2]"),find(".",A2)-1)&"圆",left(text(A2,"[dbnum2]"),1)&"圆")&if(mid(text(A2,"[dbnum2]"),find(".",text(A2,"[dbnum2]")) 1,1)="零",if(left(text(A2,"[dbnum2]"),1)="零","","零"),mid(text(A2,"[dbnum2]",find(".",text(A2,"[dbnum2]")) 1,1)&"角")&if(len(text(A2,"[dbnum2]")-find(".",text(A2,"[dbnum2]"))=2,right(text(A2,"[dbnum2]"),1)&"分",""),text(A2,"[dbnum2]")&"圆整");

最后就剩下一个“-”负号没有处理啦，只需用=substitute(上面公式,"-","负"),到这整个转化中文大写数字就算是写完了，把公式带入源数据表中，看一下最终的效果入下：

最终的转化效果

​好了，今天的文章就写到这了，在最后提醒自定格式和TEXT函数的细微区别，在不经过任何处理的情况，赋值黏贴都可以做到所见即所得，但涉及到计算或用测量函数测量时，两者就会出现不同，自定义格式则维持原来的数据的状态而TEXT函数则呈现变形后的状态，比如123，同样采用[dbnum2]格式显示，用len(自定义)=3,len(text())=5,尤其适用文本函数定位截取时就会出现误差和错误，希望你在以后的工作中注意适用场景，避免没有必要的错误！希望你阅读后从中有所收获，人海万千，相遇即缘，我们下文再见吧！