導(dǎo)讀Excel函數(shù)的冷門(mén)技巧，很多人不知道，學(xué)會(huì)告別加班如何分離姓名和電話號(hào)碼？巧用SUBSTITUTE函數(shù)進(jìn)行文本字符串的替換！長(zhǎng)相相似但功能相反的兩個(gè)函數(shù)—....

Excel函數(shù)的冷門(mén)技巧，很多人不知道，學(xué)會(huì)告別加班

---

1. 如何分離姓名和電話號(hào)碼？
2. 巧用SUBSTITUTE函數(shù)進(jìn)行文本字符串的替換！
3. 長(zhǎng)相相似但功能相反的兩個(gè)函數(shù)——CODE和CHAR！
4. 怎樣將帶有小數(shù)點(diǎn)的小寫(xiě)數(shù)字轉(zhuǎn)化為大寫(xiě)？

---

怎樣分離姓名和電話號(hào)碼

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

圖3-10-17

如果A列姓名都是2個(gè)漢字，電話號(hào)碼都是11位固定長(zhǎng)度，我們可以通過(guò)分列實(shí)現(xiàn)，第一步選擇固定寬度，第二步建立分列線，直接下一步就可以實(shí)現(xiàn)分開(kāi)，如圖3-10-18所示。

上如例子姓名長(zhǎng)度不固定，要拆分姓名和電話號(hào)碼可以用公式與函數(shù)實(shí)現(xiàn)，漢字是雙字節(jié)，而字母和數(shù)字是單字節(jié)。

而在Excel函數(shù)中有一類是帶B的函數(shù)LENB,LEFTB,RIGHTB,MIDB,SEARCHB，它們可以區(qū)分單雙字節(jié)，所以我們就可以利用帶B的函數(shù)來(lái)解決這個(gè)問(wèn)題。

圖3-10-18

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

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

公式說(shuō)明：

SEARCHB是在一個(gè)字符串中查找特定字符位置的函數(shù)，而且可以區(qū)分單雙字節(jié)，它和FIND的區(qū)別是可以使用通配符。

公式中的？就是表示任意一個(gè)單字節(jié)的字符，屬通配符，不是真的查找問(wèn)號(hào)。MIDB是按字節(jié)數(shù)截取。一個(gè)漢字算兩個(gè)字節(jié)，字母和數(shù)字分別算一個(gè)。

也可以用下面的公式來(lái)實(shí)現(xiàn)，LEN函數(shù)返回字符數(shù)（char），LENB返回字節(jié)數(shù)(byte)，2個(gè)函數(shù)返回值相減就得到漢字的個(gè)數(shù)，再用LEFT和RIGHT函數(shù)截取姓名和電話號(hào)碼。

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

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

如圖3-10-19所示，公式說(shuō)明：

LENB(A2)-LEN(A2)得到姓名的字符數(shù)，姓名在左邊，用LEFT函數(shù)截取姓名。LEN(A2)-(LENB(A2)-LEN(A2))A2單元格內(nèi)容總長(zhǎng)度減去姓名的字符數(shù)就得到電話號(hào)碼的字符數(shù)，電話號(hào)碼在右邊，再用RIGHT函數(shù)截取電話號(hào)碼。

圖3-10-19

文本字符串的替換——SUBSTITUTE函數(shù)

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

語(yǔ)法：

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

• text為需要替換其中字符的文本或?qū)形谋镜膯卧竦囊谩?/li>
• old_text為需要替換的舊文本。
• new_text用于替換old_text的文本。
• instance_num為一數(shù)值，用來(lái)指定以new_text替換第幾次出現(xiàn)的old_text。

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

如果缺省則將用new_text替換text中出現(xiàn)的所有old_text。

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

長(zhǎng)相相似但功能相反的兩個(gè)函數(shù)——CODE和CHAR函數(shù)

在計(jì)算機(jī)數(shù)據(jù)表示中，每一個(gè)字符都有對(duì)應(yīng)的ASCII碼與其對(duì)應(yīng)，我們可以借助CHAR函數(shù)將ASCII碼轉(zhuǎn)換成對(duì)應(yīng)的字符，該函數(shù)在編程及循環(huán)操作時(shí)常見(jiàn)。

反過(guò)來(lái)，如果要將字符轉(zhuǎn)換為對(duì)應(yīng)的ASCII碼，則借助函數(shù)CODE。下面就一起來(lái)了解一下這兩個(gè)函數(shù)的使用方法。

首先來(lái)看一下CHAR函數(shù)的語(yǔ)法：

CHAR(number)。

number：代表用于轉(zhuǎn)換的ASCII碼字符代碼，使用的是當(dāng)前計(jì)算機(jī)字符集中的字符。

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

圖3-10-20

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

圖3-10-21

再看看漢字，比如，我的姓名對(duì)應(yīng)的數(shù)字代碼如圖3-10-22所示。

圖3-10-22

幾個(gè)數(shù)字就代表一個(gè)漢字，是不是感覺(jué)很神奇？這就是計(jì)算機(jī)強(qiáng)大的記憶功能。

再看看CODE函數(shù)，這個(gè)函數(shù)的功能是用于返回與字符相對(duì)應(yīng)的字符編碼，如圖3-10-23和圖3-10-24所示。

圖3-10-23

圖3-10-24

怎樣將帶有小數(shù)點(diǎn)的小寫(xiě)數(shù)字轉(zhuǎn)化為大寫(xiě)

Excel中要將人民幣小寫(xiě)金額轉(zhuǎn)換成大寫(xiě)格式，將自定義格式類型中的“G/通用格式”改為“G/通用格式"元"”來(lái)實(shí)現(xiàn)。

但在轉(zhuǎn)換小數(shù)時(shí)卻出現(xiàn)了問(wèn)題，比如123.45元只能轉(zhuǎn)換為“壹佰貳拾叁.肆伍”。那怎么解決這一先天不足呢？

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

圖3-10-27

公式里用到了4個(gè)函數(shù)，TEXT,INT,DBNum2,MOD，我們來(lái)一一解釋這4個(gè)函數(shù)的功能。TEXT函數(shù)是將數(shù)值轉(zhuǎn)換為指定數(shù)字格式表示的文本，語(yǔ)法是：TEXT(數(shù)值，指定格式的文本)。

[DBNum2]是格式函數(shù),小寫(xiě)數(shù)字轉(zhuǎn)中文大寫(xiě)。

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

INT函數(shù)是將任意實(shí)數(shù)向下取整為最接近的整數(shù)。

例如，INT（123.56）返回結(jié)果是123。

MOD函數(shù)是兩數(shù)相除的余數(shù)，語(yǔ)法是：MOD(被除數(shù)，除數(shù))，例如，MOD(25,2)返回結(jié)果是1。了解了函數(shù)的功能，我們?cè)賮?lái)看看公式前面一部分=TEXT(INT(A1),"[DBNum2]G/通用格式")，是將小數(shù)點(diǎn)前面的整數(shù)部分轉(zhuǎn)換為大寫(xiě)，后面一部分TEXT(MOD(A1,1)*100,"[DBNum2]0角0分")是將小數(shù)點(diǎn)后面的小數(shù)部分放大100倍后再轉(zhuǎn)換為大寫(xiě)。