• 這樣使用“Excel偷懶的技術(shù)”公眾號(hào)，快速提升Excel水平

  
  

基礎(chǔ)知識(shí)

Text函數(shù)，是易容大師，其本職工作是做醫(yī)美的，擅長(zhǎng)去脂、隆鼻、豐胸，甚至變性。他能將數(shù)字美容成你希望的模樣。

比如將123456.78進(jìn)行一番隆鼻、抽脂、豐胸、變性操作，就變成C5:C8的模樣：

這些都是Text函數(shù)的本職工作，它用函數(shù)實(shí)現(xiàn)自定義格式的功能。它有兩個(gè)參數(shù)，第一個(gè)參數(shù)是要美容的數(shù)字，第二個(gè)參數(shù)是希望美容成什么樣子。

關(guān)于第二參數(shù)的設(shè)置，咱們今天不詳細(xì)解釋。在以前的文章中有介紹，請(qǐng)點(diǎn)擊閱讀下面的文章了解：

• 一個(gè)TEXT函數(shù)打天下，如何對(duì)日期時(shí)間進(jìn)行分段？
  

• 我形我色，如何用TEXT函數(shù)顯示指定顏色？
  

今天要給大家介紹的是——函數(shù)高手們給他賦予的一個(gè)神技能：

給字符串做外科截肢手術(shù)——分列！

下面請(qǐng)欣賞Text函數(shù)的表演

提取短杠前的數(shù)字

函數(shù)公式：

=TEXT(0,"[$"&B2&"]")

解釋?zhuān)?/strong>

在自定義格式中，“[$-數(shù)字]”是區(qū)域代碼，其作用是指定國(guó)家或地區(qū)。比如要將數(shù)字格式化為中國(guó)大陸簡(jiǎn)體中文的樣式，就使用區(qū)域代碼“[$-804]”(也可用[$-zh-CN])；如果要格式化為臺(tái)灣省的中文繁體，就使用區(qū)域代碼[$-404]。

更多的區(qū)域代碼詳見(jiàn)：

https://zh./wiki/%E5%8C%BA%E5%9F%9F%E8%AE%BE%E7%BD%AE

在上面的公式中，直接用B列的字符串來(lái)構(gòu)造成區(qū)域代碼，Excel會(huì)將“-”后的內(nèi)容視為區(qū)域代碼，而自動(dòng)忽略掉，只顯示短杠前的內(nèi)容。

本公式就是利用這個(gè)特性提取短杠前的內(nèi)容。

分列3節(jié)的字符串

公式：

=TEXT({1,-1,0},SUBSTITUTE(B2,"-",";"))

解釋?zhuān)?/strong>

• {1,-1,0}

是常量數(shù)組。可理解為在同一行的三列的單元格中分別輸入1，-1，0

并不是只能寫(xiě)成1，-1，只要分別是正數(shù)、負(fù)數(shù)就行。比如換成下面的公式也是可以的：

=TEXT({99,-8,0},SUBSTITUTE(B3,"-",";"))

• SUBSTITUTE(B2,"-",";")

將B2單元格的短杠-替換為;，其結(jié)果為：

應(yīng)交稅金;應(yīng)交增值稅;未交增值稅

根據(jù)自定義格式的規(guī)則，這三節(jié)代碼分別應(yīng)用于正數(shù)、負(fù)數(shù)、零。

與第一參數(shù){1,-1,0}一起分別放到Text的第一第二參數(shù)，就得到了將字符串拆分的效果

如果只有兩節(jié)，公式改為：

=TEXT({1,-1},SUBSTITUTE(B2,"-",";"))

如果只提取第1節(jié)、第2節(jié)或第3節(jié)，分別用公式

=TEXT(1,SUBSTITUTE(B3,"-",";"))=TEXT(-1,SUBSTITUTE(B3,"-",";"))=TEXT(0,SUBSTITUTE(B3,"-",";"))

分列4節(jié)的字符串

公式：

=TEXT({1,-1,0,""},SUBSTITUTE(B2,"-",";"))

上面的公式不適用于數(shù)字，如果是數(shù)字怎么辦呢？

用下面的公式：

=--TEXT({1,-1,0,""},""""&SUBSTITUTE(B2,"-",""";""")&"""")

Substitute函數(shù)前后的雙引號(hào)為什么是四個(gè)？

這首先要理解為什么要加引號(hào)。

有兩個(gè)原因：

1、數(shù)字0是占位符，要讓其失去占位屬性，需用雙引號(hào)括起來(lái)。

2、自定義格式代碼的第四節(jié)，如果寫(xiě)入數(shù)字會(huì)提示出錯(cuò)（如下圖），需用雙引號(hào)括起來(lái)。

其次，要理解為什么是四個(gè)引號(hào)

要讓雙引號(hào)失去引號(hào)的作用，代表他自己，需在前面加個(gè)雙引號(hào)，然后，在公式中所有的字符需添加雙引號(hào)。所以，substitute函數(shù)的雙引號(hào)是四個(gè)。

那Substitute函數(shù)第三參數(shù)分號(hào)前后的雙引號(hào)為什么是3個(gè)？

這是因?yàn)椋?/p>

首先，要將短杠替換為用一對(duì)雙引號(hào)括起來(lái)的分號(hào)（";"）,

然后，要讓分號(hào)前后的雙引號(hào)失去引號(hào)的屬性，需分別在兩個(gè)引號(hào)前加一個(gè)引號(hào)。就變成了：

"";""

最后，再將上面的這組文本字符串（被兩對(duì)雙引號(hào)括起來(lái)的分號(hào)），用一對(duì)雙引號(hào)括起來(lái)，就變成了：

""";"""

解釋起來(lái)很繞口，理解起來(lái)很燒腦。能理解就理解，不能理解的話(huà)就強(qiáng)行記住它吧。

分列5節(jié)的字符串

要將5節(jié)的字符拆分，需用使用兩個(gè)公式

=TEXT({1,-1,0},SUBSTITUTE(SUBSTITUTE(B2,"-"," ",4),"-",";"))

=TEXT({-1,0},SUBSTITUTE(SUBSTITUTE(B2,"-",";",3),"-",";",3))

要真正掌握Text函數(shù)，首先得掌握自定義格式的相關(guān)知識(shí)，關(guān)于自定義格式請(qǐng)閱讀下面合集中的文章：

自定義格式

文后說(shuō)明：

Excel新增了TextSplit函數(shù)，可按指定字符輕松拆分字符串，對(duì)本文的這些Text函數(shù)的技巧，完全是降維打擊。如果你用的是Excel 2019以后的版本，就沒(méi)必要用本文的技巧了。

如需加入RPA群，請(qǐng)備注RPA

如需加入圖表群，請(qǐng)備注圖表