導讀Excel函數的冷門技巧，很多人不知道，學會告別加班如何分離姓名和電話號碼？巧用SUBSTITUTE函數進行文本字符串的替換！長相相似但功能相反的兩個函數—....

Excel函數的冷門技巧，很多人不知道，學會告別加班

---

1. 如何分離姓名和電話號碼？
2. 巧用SUBSTITUTE函數進行文本字符串的替換！
3. 長相相似但功能相反的兩個函數——CODE和CHAR！
4. 怎樣將帶有小數點的小寫數字轉化為大寫？

---

怎樣分離姓名和電話號碼

如圖3-10-17所示，要把A列內容拆分到B列和C列。這個問題可以用第1章第10招介紹的快速填充一秒鐘搞定，如果A列內容變了，B列和C列要自動變化就得用公式與函數了。

圖3-10-17

如果A列姓名都是2個漢字，電話號碼都是11位固定長度，我們可以通過分列實現，第一步選擇固定寬度，第二步建立分列線，直接下一步就可以實現分開，如圖3-10-18所示。

上如例子姓名長度不固定，要拆分姓名和電話號碼可以用公式與函數實現，漢字是雙字節，而字母和數字是單字節。

而在Excel函數中有一類是帶B的函數LENB,LEFTB,RIGHTB,MIDB,SEARCHB，它們可以區分單雙字節，所以我們就可以利用帶B的函數來解決這個問題。

圖3-10-18

B2公式：=LEFTB(A2,SEARCHB("?",A2)-1)

C2公式:=MIDB(A2,SEARCHB("?",A2),11)

公式說明：

SEARCHB是在一個字符串中查找特定字符位置的函數，而且可以區分單雙字節，它和FIND的區別是可以使用通配符。

公式中的？就是表示任意一個單字節的字符，屬通配符，不是真的查找問號。MIDB是按字節數截取。一個漢字算兩個字節，字母和數字分別算一個。

也可以用下面的公式來實現，LEN函數返回字符數（char），LENB返回字節數(byte)，2個函數返回值相減就得到漢字的個數，再用LEFT和RIGHT函數截取姓名和電話號碼。

D2公式：=LEFT(A2,LENB(A2)-LEN(A2))

E2公式：=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))

如圖3-10-19所示，公式說明：

LENB(A2)-LEN(A2)得到姓名的字符數，姓名在左邊，用LEFT函數截取姓名。LEN(A2)-(LENB(A2)-LEN(A2))A2單元格內容總長度減去姓名的字符數就得到電話號碼的字符數，電話號碼在右邊，再用RIGHT函數截取電話號碼。

圖3-10-19

文本字符串的替換——SUBSTITUTE函數

SUBSTITUTE函數在文本字符串中用new_text替代old_text。如果需要在某一文本字符串中替換指定的文本，請使用函數SUBSTITUTE；如果需要在某一文本字符串中替換指定位置處的任意文本，請使用函數REPLACE。

語法：

SUBSTITUTE(text,old_text,new_text,[instance_num])

• text為需要替換其中字符的文本或對含有文本的單元格的引用。
• old_text為需要替換的舊文本。
• new_text用于替換old_text的文本。
• instance_num為一數值，用來指定以new_text替換第幾次出現的old_text。

如果指定了instance_num，則只有滿足要求的old_text被替換；

如果缺省則將用new_text替換text中出現的所有old_text。

例如，A1單元格內容為騰迅，公式=SUBSTITUTE(A1,"迅","訊")返回結果騰訊。

長相相似但功能相反的兩個函數——CODE和CHAR函數

在計算機數據表示中，每一個字符都有對應的ASCII碼與其對應，我們可以借助CHAR函數將ASCII碼轉換成對應的字符，該函數在編程及循環操作時常見。

反過來，如果要將字符轉換為對應的ASCII碼，則借助函數CODE。下面就一起來了解一下這兩個函數的使用方法。

首先來看一下CHAR函數的語法：

CHAR(number)。

number：代表用于轉換的ASCII碼字符代碼，使用的是當前計算機字符集中的字符。

在A2單元格中輸入“65”，然后在B2單元格中輸入公式“=CHAR(A2)”即可生成大寫字母A，將A列ASCII碼依次增加，同時對B列進行公式的復制，就會發現自動生成后續字母。在C列輸入97到122，D列輸入公式“=CHAR(C2)”，向下復制公式，可以自動生成小寫字母，如圖3-10-20所示。

圖3-10-20

如果需要輸入圓圈內帶數字的字符，輸入數字代碼，借助函數CHAR就可以得到，如圖3-10-21所示。

圖3-10-21

再看看漢字，比如，我的姓名對應的數字代碼如圖3-10-22所示。

圖3-10-22

幾個數字就代表一個漢字，是不是感覺很神奇？這就是計算機強大的記憶功能。

再看看CODE函數，這個函數的功能是用于返回與字符相對應的字符編碼，如圖3-10-23和圖3-10-24所示。

圖3-10-23

圖3-10-24

怎樣將帶有小數點的小寫數字轉化為大寫

Excel中要將人民幣小寫金額轉換成大寫格式，將自定義格式類型中的“G/通用格式”改為“G/通用格式"元"”來實現。

但在轉換小數時卻出現了問題，比如123.45元只能轉換為“壹佰貳拾叁.肆伍”。那怎么解決這一先天不足呢？

我們可以利用公式和函數解決，A1單元格是小寫數字，我們在B1單元格輸入公式=TEXT(INT(A1),"[DBNum2]G/通用格式")&"元"&TEXT(MOD(A1,1)*100,"[DBNum2]0角0分")就可以將A1單元格的小寫數字轉換為大寫數字，如圖3-10-27所示。

圖3-10-27

公式里用到了4個函數，TEXT,INT,DBNum2,MOD，我們來一一解釋這4個函數的功能。TEXT函數是將數值轉換為指定數字格式表示的文本，語法是：TEXT(數值，指定格式的文本)。

[DBNum2]是格式函數,小寫數字轉中文大寫。

例如，公式=TEXT(123,"[DBNum2]")返回的結果是壹佰貳拾叁。

INT函數是將任意實數向下取整為最接近的整數。

例如，INT（123.56）返回結果是123。

MOD函數是兩數相除的余數，語法是：MOD(被除數，除數)，例如，MOD(25,2)返回結果是1。了解了函數的功能，我們再來看看公式前面一部分=TEXT(INT(A1),"[DBNum2]G/通用格式")，是將小數點前面的整數部分轉換為大寫，后面一部分TEXT(MOD(A1,1)*100,"[DBNum2]0角0分")是將小數點后面的小數部分放大100倍后再轉換為大寫。