上文說到第二種的公式=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,尤其适用文本函數定位截取時就會出現誤差和錯誤，希望你在以後的工作中注意适用場景，避免沒有必要的錯誤！希望你閱讀後從中有所收獲，人海萬千，相遇即緣，我們下文再見吧！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!