文字函數
_
首頁 線上購物 接課行事曆 教學資料 美食消息 新知新聞 黑輪Q&A 黑輪BLOG 黑輪日記 童軍團友會 好貨推薦

My status
首頁
向上

 

文字函數

 

文字函數能夠將數字的文字輸入項轉換為數字以及數字輸入項轉換為文字字串以便讓您能夠熟練地操作文字字串本身。

TEXT函數

TEXT函數會以一特定的格式將數字轉換為一個文字字串。此函數的用法如下=TEXT(value,format_text)

value引數可以是任何數字、公式或者是儲存格參照。format_text引數表明結果字串是怎樣顯示的。您可以使用除了星號(*)以外的Excel格式化符號($,#,0,諸如此類)去指定您所想要的格式;您不能使用一般格式。

舉例而言,此公式=TEXT(98/4,"0.00")

會傳回文字字串24.50。

DOLLAR函數

就像TEXT函數一樣,DOLLAR函數會將一個數字轉換為一個字串。然而,DOLLAR會將傳回來的結果字串轉換為貨幣並且帶有您所指定的小數點位數。此函數用法如下=DOLLAR(number,decimals)

舉例而言,此公式=DOLLAR(45.899,2)

會傳回文字字串$45.90以及此公式=DOLLAR(45.899,0)

會傳回文字字串$46。請您注意當在必要時,Excel會自動取大約值。假如您在DOLLAR函數中遺漏了小數點引數,Excel就會使用2個點位數。假如您在小數點引數使用負數,則Excel會取大約值取到小數點左邊。

LEN函數

LEN函數會傳回在一個輸入項中的字元數目,其用法如下=LEN(text)

text引數可以是一個字母的數字,一個是被雙引號所包含住的字母字串,或者是對一個儲存格的參照。舉例而言,此公式=LEN("Test")

會傳回4。假如A1儲存格包含標籤Test,則此公式=LEN(A1)

也會傳回4。

LEN函數會傳回顯示出來的文字或數值的長度而不是潛在的儲存格內容的長度。舉例而言,假設A10儲存格包含以下的公式=A1+A2+A3+A4+A5+A6+A7+A8

其計算的結果為25。則底下的公式=LEN(A10)

會傳回2,而傳回來的2乃是25的長度。LEN函數會忽略掉蔓延的0。

儲存格參照可以包含另一個字串函數來當作是LEN函數的引數。舉例而言,假如A1儲存格包含以下的函數=REPT("-*",75)

然後底下的公式=LEN(A1)

會傳回150。

ASCII函數:CHAR以及CODE

每一個電腦都會使用數字碼來代表字元。最流行的數字碼系統稱為ASCII或是美國資訊交換標準碼(American Standard Code for Information Interchange)。ASCII使用0到127(某些系統是用到255)之間的數字去代表每一個數字、字母或符號。

CHAR以及CODE函數會處理這些ASCII碼。CHAR函數會傳回相對於一個ASCII碼數字的字元;CODE函數會傳回其引數的第一個字元的ASCII碼數字。這些函數其用法如下=CHAR(number)

以及=CODE(text)

舉例而言,此公式=CHAR(83)

會傳回文字S(請注意您可以輸入前面帶有0的引數。)此公式如下=CODE("S")

會傳回ASCII碼83。同樣地,假如A1儲存格包含文字S,則此公式為=CODE(A1)

也會傳回碼83

因為數字也是字元,所以CODE的引數可以是數字。舉例而言,此公式=CODE(8)

會傳回ASCII碼中的字元8所代表的數字56。

假如您輸入一個字母字元當作是引數,請您確定該字元必須以雙引號前後包夾住;否則Excel會傳回錯誤值#NAME?。

清除函數:TRIM以及CLEAN

在開頭的或蔓延的空白字元通常會使您無法在工作表或資料庫中正確地排序輸入項。假如您使用字串函數來處理在您工作表中的文字時,額外的空格會使得您的公式無法正確的運作。TRIM函數會在一個字串中清除掉字串開頭的、蔓延的或是額外的空白字元,僅僅留下在字距間一個單一的空格。此函數用法如下=TRIM(text)

舉例而言,在您工作表的A1儲存格包含了字串

Fuzzy Wuzzy Was A Bear,此公式=TRIM(A1)

會傳回Fuzzy Wuzzy Was A Bear。

CLEAN函數和TRIM函數相似,除了CLEAN函數僅僅作用在不可列印的字元,如跳格鍵以及程式特定的碼。假如您是由另一個程式當中匯入資料而且其程式中某些輸入項包含著不可列印的字元,則CLEAN函數是特別地有用。(這些字元也許會以粗體垂直列或是小方塊的方式出現在您的工作表中。)您可以使用CLEAN函數從這些資料當中移除這些字元。此函數的用法如下=CLEAN(text)

EXACT函數

EXACT函數是一個條件性的函數,它是用來決定兩個字串是否精確的相等,包含字串中的大寫以及小寫字母。格式的不同在此是會被忽略掉的。此公式用法如下=EXACT(text1,text2)

假如text1以及text2包含大小寫字母都是相同時,則EXACT函數會傳回TRUE;否則,EXACT函數會傳回FALSE。text1以及text2引數必須以雙引號將字母字串包夾住或是參照到內含有文字的儲存格。舉例而言,假如您工作表中的A5以及A6儲存格中都包含了文字Totals,則此公式=EXACT(A5,A6)

會傳回TRUE。

大小寫函數:UPPER、LOWER以及PROPER

這些函數能夠巧妙地處理掉在文字字串中字母的大小寫:UPPER、LOWER以及PROPER。UPPER函數會將一個文字字串轉換為全部是大寫的字母。LOWER函數會將一個文字字串轉換為全部是小寫的字母。PROPER函數會將每一個字的第一個字母轉會為大寫而任何其他在文字字串中的字母並不會跟隨著其他的字母;所有其他的字母皆會被轉為小寫字母。這些函數的用法如下=UPPER(text)

以及=LOWER(text)

以及=PROPER(text)

假設您在工作表中輸入一連串的名稱而您想要所有名稱的字首皆出現大寫。假如A1儲存格包含文字john Johnson您可以使用此公式=UPPER(A1)

會傳回JOHN JOHNSON。相同地,此公式=LOWER(A1)

會傳回john johnson。而=PROPER(A1)

會傳回John Johnson。

然而,當文字當中包含標點符號時會發生無法預期的結果。舉例而言,假如A1儲存格包含了a two-thirds majority wasn't possible,先前的公式會產生出A Two-Thirds Majority Wasn'T Possible。

當您將文字函數使用在現存的資料清單時,您常常會想要將文字修改到恰好能夠適用到函數上。當然,您不能在您正在輸入文字的儲存格上輸入一個函數,因為那將會覆蓋掉儲存格上的文字。相反地,您可以在同一個列上沒有使用到的儲存格中輸入暫存的文字函數,然後複製結果。為了能用修改過的文字取代掉原有的文字,請您先選定包含原先的文字的儲存格,由編輯功能表中選擇選擇性貼上指令,再選擇您所想要的數值選項並且按下確定鈕。然後,您就可以將暫存的公式刪除。

ISTEXT以及ISNONTEXT函數

ISTEXT以及ISNONTEXT函數能夠測試出一個輸入項是否是文字。這些函數用法如下=ISTEXT(value)

以及=ISNONTEXT(value)

假設您想要決定出在C5儲存格當中的輸入項是否是文字。假如您使用以下的公式=ISTEXT(value)

而在C5儲存格中是文字或是一個能傳回文字的公式,則Excel會傳回邏輯值TRUE。假如您用底下的公式測試相同的儲存格,=ISNONTEXT(C5)

則Excel會傳回邏輯值FALSE。

子字串函數

接下來的函數將會尋找並傳回一個文字字串的部份字串或是從較小的字串組合成較大的字串:FIND、SEARCH、RIGHT、LEFT、MID、SEARCH、SUBSTITUTE、REPT、REPLACE、以及CONCATENATE。

FIND以及SEARCH函數

您可以使用FIND以及SEARCH函數去尋找出在一個字串中的子字串部分。這兩個函數都會傳回在尋找的字串中,Excel首先發現的文字的字元位置。(Excel會將空格以及標點符號也當作是字元。)

這兩個函數是以相同的方式來運作的,除了FIND函數是有大小寫之分的以及SEARCH函數允許萬用字元。這些函數的用法如下=FIND(find_text,within_text,start_num)

以及=SEARCH(find_text,within_text,start_num)

find_text引數確認出要被搜尋的文字,而within_text引數是指出您所要尋找的文字。您可以使用雙引號包夾住字母文字或是用一個儲存格的參照來當作是引數。此可選擇性的start_num引數是指定出在within_text中您所要開始尋找的字元位置。當within_text中包含有超過一次的find_text時,start_num引數是非常有用的。假如您遺漏掉start_num引數,Excel將會報告出它第一次所尋找到的字元位置。

假如find_text並沒有包含在within_text中、假如start_num並沒有大於0、或是假如start_num大於在within_text中的字母數或是大於最後一次find_text出現的位置時,您將會得到一個錯誤值#VALUE!。

舉例而言,我們想要在字串A Night At the Opera中尋找出p的字元位置,您可以使用此公式=FIND("p","A Night At the Opera")

此公式會傳回17因為p在字串當中是第十七個字元。

假如您並不確定您所要尋找的字元順序時,您可以使用SEARCH函數並且在您的find_text字串中使用萬用字元。您可以使用問號字元(?)以便尋找出佔據特定單一位置的單一字元;您可以使用星號(*)以便尋找出佔據特定單一位置的任何順序的字元。

假設在您的工作表中您已經使用了Smith以及Smyth這兩個名稱。為了決定出哪一個名稱在A1儲存格中,您可以使用以下的公式=SEARCH("Sm?th",A1)

假如在A1儲存格中包含文字John Smith或是John Smyth,SEARCH函數會傳回6─Sm?th的開始點。假如您不確定字元的數目,請您使用*萬用字元。舉例而言,為了去找出存放在A1儲存格中的字串中的文字Allan或Alan,我們可以使用此公式=SEARCH("A*an",A1)

RIGHT以及LEFT函數

RIGHT函數會傳回在一個字串引數中最右邊序列的字元,而LEFT函數會傳回在一個字串引數中最左邊序列的字元,這些函數其用法如下=RIGHT(text,num_chars)

以及=LEFT(text,num_chars)

num_chars引數指定出您想要由文字引數中取出來的字元數。這些函數會把在文字引數中的空格當作是一個字元;假如文字中包含開頭的或蔓延的空白字元時,您也許想要在RIGHT以及LEFT函數中使用一個TRIM函數以確保您所預期的結果。

num_chars引數必須大於或等於0。假如您遺漏num_chars引數,Excel會假設其為1。假如num_chars引數大於在文字中的字元數,則RIGHT以及LEFT會傳回整個文字引數。

舉例而言,假設在工作表中您在A1儲存格中輸入了This is a test。則此公式為=RIGHT(A1,4)

會傳回test這個字。

MID函數

您可以使用MID函數由一個文字字串中取出一串字元。此函數用法如下=MID(text,start_num,num_chars)

text引數是指您所想要取出子字串的母字串,start_num引數是指子字串的開始點位置(相對於字串的最左邊),

而num_chars引數是指您想要取出的字元數。舉例而言,假如A1儲存格中包含了文字This Is A Long Text Entry,您可以使用此公式=MID(A1,11,10)

由A1儲存格的輸入項中去取出Long Text字元

REPLACE以及SUBSTITUTE函數

這兩個函數都會將舊有的文字取代為新的文字。REPLACE函數會以另一個字串字元來取代掉一個字串字元而其公式如下=REPLACE(old_text,start_num,num_chars,new_text)

old_text引數便是在文字字串中您想要取代掉的字元。下兩個引數,start_num以及num_chars特別指定出要取代掉哪些字元(相對於字串的最左邊)。new_text引數指定出您想要插入的文字字串。

假設A3儲存格中包含Millie Potter, Psychic。將此字串放在A6儲存格中並且將前六個字元以字串Mildred取代掉,選定A6並且使用此公式=REPLACE(A3,1,6,"Mildred")

新產生出來的字串為Mildred Potter, Psychic。在A3儲存格中的標籤依然不變而新的標籤只會出現在您輸入公式的A6儲存格中。

使用SUBSTITUTE函數,您不需要指定要取代掉的起始數字以及的字元數;相反地,您只要指定您想要取代掉的文字即可。SUBSTITUTE函數用法如下=SUBSTITUTE(text,old_text,new_text,instance_num)

假設在A4儲存格中包含文字candy,而您想要在D6儲存格中將其取代並且改變為dandy。使用此公式=SUBSTITUTE(A4,"c","d")

當您在D6儲存格中輸入此公式時,那麼在A4儲存格中的文字依然是不變的。此新的文字僅僅會出現在包含此公式的D6儲存格中。

instance_num引數是可選擇性的。它告訴Excel只要去取代在old_text中所出現的特定次數的字元。舉例而言,假如在A1儲存格中包含文字through the hoop而您想要將hoop取代為loop,在公式中的4=SUBSTITUTE(A1,"h","l",4)

告訴Excel將在A1儲存格中的第四個h用l取代掉。假如您沒有將instance_num納入公式中,Excel會將所有出現在A1儲存格中的old_text取代為new_text。

REPT函數

REPT函數讓您用一個重複特定次數的字元字串填滿一個儲存格。此函數用法如下=REPT(text,number_times)

text引數是用來要被重複用的字串,而此字串以雙引號包夾住的。number_times引數是指定此文字字串要被多少次;它可以是任何一個正數值,然而REPT函數的運算結果被限制為255個字元。假如您在number_times引數輸入0,那麼REPT會讓儲存格為空白的;假如number_times引數不是一個整數,REPT函數會忽略掉此引數的小數點部分。

假設您想要產生出一列150個字元寬的星號。請您輸入公式為=REPT("*",150)

其結果為一個150個星號長的字串。

text引數可以超過一個以上的字元。舉例而言,此公式為=REPT("-*",75)

其結果為一列150個星號以及連字號的字元寬度的列。number_times引數指定出您想要的text引數重複的次數而不是您所要產生的總字元字數。假如文字字串有兩個字元,那麼結果字串的長度將會是number_times引數的兩倍。

CONCATENATE函數

使用CONCATENATE函數與您使用"&"字元去將較小的字串組合成較大的字串是相同的。此函數用法如下=CONCATENATE(text1,text2,...)

在您所想要組合的文字片段中,您最多可以使用30個文字引數。

舉例而言,假如B4儲存格中包含文字strained,則此公式=CONCATENATE("The Koala Tea of Mercy, Australia, is not",B4,".")

會傳回The Koala Tea of Mercy, Australia, is not strained.

 
 

EmailAllen@Chen.name