使用函數將文字分成幾欄

如果要把資料中的字串做進一步的處理,例如將某個儲存格中的名字、中間名及姓氏分成三個不同的欄,文字函數是相當實用的方法。

本文將說明如何結合使用下列文字函數,將名稱元件擷取和複製到不同的儲存格。

函數 語法
LEFT LEFT(text,num_chars)
MID MID(text,start_num,num_chars)
RIGHT RIGHT(text,num_chars)
SEARCH SEARCH(find_text,within_text,start_num)
LEN LEN(text)

擷取名稱元件

使用文字函數分解名稱元件時,關鍵在於每個字元在文字字串中的位置。空格在文字字串中的位置十分重要,因為空格代表名稱元件在字串中的開頭或結尾。

例如,在只有一個名字和姓氏的儲存格中,第一個空格之後便是姓氏。如果是包含中間名的姓名,那麼姓氏是在第二個空格之後。

本文將說明如何從多種姓名格式擷取各種元件。在下表中按一下範例姓名,即可看到從該姓名擷取元件的公式。

 附註   將每個範例的公式複製到空白的工作表中,就會更容易了解這些公式。

顯示如何複製範例

  1. 建立空白活頁簿或工作表。
  2. 選取下表中的範例文字和公式。

 附註   請不要選取列或欄標題。

從 [說明] 中選取範例

  1. 按 CTRL+C 鍵。
  2. 在工作表上,選取儲存格 A1,然後按 CTRL+V 鍵。
  3. 若要在檢視結果與檢視公式 (會傳回結果) 之間切換,請按 CTRL+` (重音節符號) 鍵,或是在 [公式] 索引標籤的 [公式稽核] 群組中,按一下 [顯示公式] 按鈕。

範例姓名 說明 名字 中間名 姓氏 稱謂
1 Jeff Smith 沒有中間名 Jeff   Smith  
2 Eric S. Kurjan 一個中間名縮寫 Eric S. Kurjan  
3 Janaina B. G. Bueno 兩個中間名縮寫 Janaina B. G. Bueno  
4 Kahn, Wendy Beth 姓氏在前並加上逗號 Wendy Beth Kahn  
5 Mary Kay D. Andersen 名字由兩個部分組成 Mary Kay D. Andersen  
6 Paula Barreto de Mattos 姓氏由三個部分組成 Paula   Barreto de Mattos  
7 James van Eaton 姓氏由兩個部分組成 James   van Eaton  
8 Bacon Jr., Dan K. 姓氏與後稱謂在前,並加上逗號 Dan K. Bacon Jr.
9 Gary Altman III 加上後稱謂 Gary   Altman III
10 Mr. Ryan Ihrig 加上前稱謂 Ryan   Ihrig  
11 Julie Taft-Rider 姓氏中有連字號 Julie   Taft-Rider  

範例 1:Jeff Smith


這個範例只有兩個元件:名字和姓氏。兩個名稱元件之間由一個空格隔開。

 
1
2
A B C
名字 姓氏
Jeff Smith =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從字串中的第一個字元 (J) 開始,一直到第五個字元 (空格) 結尾。這個公式會傳回 A2 中的五個字元,從左邊第一個字元開始。


從範例 1:Lisa Smith 擷取名字的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋空格的數值位置,從左邊的第一個字元開始。(5)


姓氏

姓氏從空格開始,即右邊數來第五個字元,並以右邊最後一個字元 (h) 結尾。這個公式會擷取 A2 中的五個字元,從右邊的第一個字元開始。


從範例 1:Lisa Smith 擷取姓氏的公式

使用 SEARCH 函數與 LEN 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 計算文字字串的總長度,然後減去步驟 1 中從左邊開始算到第一個空格的字元數。(10 - 5 = 5)



向上箭號

頁首 頁首

範例 2:Eric S. Kurjan


在這個範例中,全名有三個元件,包括名字、中間名縮寫及姓氏。每個名稱元件之間都有一個空格隔開。

 
1
2
A B C D
姓名 名字 (Eric) 中間名縮寫 (S.) 姓氏 (Kurjan)
Eric S. Kurjan =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元 (E) 開始,一直到第五個字元 (第一個空格) 結尾。這個公式會擷取 A2 中的前五個字元,從左邊的第一個字元開始。


從範例 2:John Q. Public 擷取名字的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)


中間名

中間名從第六個字元位置 (S) 開始,一直到第八個位置 (第二個空格) 結尾。這個公式使用巢狀 SEARCH 函數,尋找第二個出現的空格。

這個公式會擷取三個字元,從第六個位置開始。


從範例 2:John Q. Public 擷取中間名的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (S) 的位置。這個數值位置會是中間名的起始位置。(5 + 1 = 6)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (S) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

圖說文字 5 在 A2 中搜尋第二個空格,從第六個位置 (S) 開始,這是步驟 4 中所找到的結果。這個字元數會是中間名的結尾位置。(8)

圖說文字 6 在 A2 中搜尋空格的數值位置,從左邊的第一個字元開始。(5)

圖說文字 7 將步驟 5 找到的第二個空格的字元數,減去步驟 6 找到的第一個空格的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第六個位置開始。(8 - 5 = 3)


姓氏

姓氏從右邊的第六個字元 (K) 開始,一直到右邊的第一個字元 (n) 結尾。這個公式使用巢狀 SEARCH 函數,尋找第二個和第三個出現的空格 (分別是從左邊開始的第五個及第八個位置)。

這個公式會擷取 A2 中的六個字元,從右邊的第一個字元開始。


從範例 2:John Q. Public 擷取姓氏的公式

使用 LEN 函數與巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (S) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

按鈕圖像 在 A2 中搜尋第二個空格,從第六個位置 (S) 開始,這是步驟 2 中所找到的結果。這個字元數會是中間名的結尾位置。(8)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 3 中從左邊開始算到第二個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(14 - 8 = 6)


頁首 頁首

範例 3:Janaina B. G. Bueno


這個範例有兩個中間名縮寫。第一個和第三個空格會隔開名稱元件。

 
1
2
A B C D
姓名 名字 (Janaina) 中間名 (B. G.) 姓氏 (Bueno)
Janaina B. G. Bueno =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元 (J) 開始,一直到第八個字元 (第一個空格) 結尾。這個公式會擷取 A2 中的前八個字元,從左邊的第一個字元開始。


從範例 3:J. R. T. Robins 擷取名字的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(8)


中間名

中間名從第九個位置 (B) 開始,一直到第十四個位置 (第三個空格) 結尾。這個公式使用巢狀 SEARCH 函數尋找分別在第八個、第十一個及第十四個位置的第一個、第二個及第三個空格。

這個公式會擷取五個字元,從第九個位置開始。


從範例 3:J. R. T. Robins 擷取中間名的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(8)

按鈕圖像 加入 1 以取得第一個空格之後字元 (B) 的位置。這個數值位置會是中間名的起始位置。(8 + 1 = 9)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(8)

按鈕圖像 加入 1 以取得第一個空格之後字元 (B) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(8 + 1 = 9)

圖說文字 5 在 A2 中搜尋第二個空格,從第九個位置 (B) 開始,這是步驟 4 中所找到的結果。(11)

圖說文字 6 加入 1 以取得第二個空格之後字元 (G) 的位置。這個字元數是要開始搜尋第三個空格的起始位置。(11 + 1 = 12)

圖說文字 7 在 A2 中搜尋第三個空格,從步驟 6 中所找到的第十二個位置開始。(14)

圖說文字 8 在 A2 中搜尋第一個空格的數值位置。(8)

圖說文字 9 將步驟 7 找到的第三個空格的字元數,減去步驟 6 找到的第一個空格的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第九個位置開始。


姓氏

姓氏從右邊的第五個字元 (B) 開始,一直到右邊的第一個字元 (o) 結尾。這個公式使用巢狀 SEARCH 函數尋找第一個、第二個及第三個空格。

這個公式會擷取 A2 中的五個字元,從全名右邊的第一個字元開始。


從範例 3:J. R. T. Robins 擷取姓氏的公式

使用巢狀 SEARCH 函數與 LEN 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(8)

按鈕圖像 加入 1 以取得第一個空格之後字元 (B) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(8 + 1 = 9)

按鈕圖像 在 A2 中搜尋第二個空格,從第九個位置 (B) 開始,這是步驟 2 中所找到的結果。(11)

按鈕圖像 加入 1 以取得第二個空格之後字元 (G) 的位置。這個字元數是要開始搜尋第三個空格的起始位置。(11 + 1 = 12)

圖說文字 5 在 A2 中搜尋第三個空格,從步驟 6 中所找到的第十二個位置 (G) 開始。(14)

圖說文字 6 計算 A2 中的文字字串總長度,然後減去步驟 5 中從左邊開始算到第三個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(19 - 14 = 5)


向上箭號

頁首 頁首

範例 4:Kahn, Wendy Beth


在這個範例中,姓氏在名字之前,而中間名出現在最後面。逗號表示是姓氏的結尾,而且每個名稱元件之間都有一個空格隔開。

 
1
2
A B C D
姓名 名字 (Wendy) 中間名 (Beth) 姓氏 (Kahn)
Kahn, Wendy Beth =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =LEFT(A2, SEARCH(" ",A2,1)-2)

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第七個字元 (W) 開始,一直到第十二個字元 (第二個空格) 結尾。由於名字出現在全名的中間,因此必須使用 MID 函數擷取名字。

這個公式會擷取六個字元,從第七個位置開始。


從範例 4:Kahn, Wendy Beth 擷取名字的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後字元 (W) 的位置。這個數值位置會是名字的起始位置。(6 + 1 = 7)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後字元 (W) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(6 + 1 = 7)

圖說文字 5 在 A2 中搜尋第二個空格,從第七個位置 (W) 開始,這是步驟 4 中所找到的結果。(12)

圖說文字 6 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

圖說文字 7 將步驟 5 找到的第二個空格的字元數,減去步驟 6 找到的第一個空格的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第七個位置開始。(12 - 6 = 6)


中間名

中間名從右邊的第四個字元 (B) 開始,一直到右邊的第一個字元 (h) 結尾。這個公式使用巢狀 SEARCH 函數,從左邊開始尋找分別位於第六個及第十二個位置的第一個及第二個空格。

這個公式會擷取四個字元,從右邊的第一個字元開始。


從範例 4:Kahn, Wendy Beth 擷取中間名的公式

使用巢狀 SEARCH 函數與 LEN 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後字元 (W) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(6 + 1 = 7)

按鈕圖像 在 A2 中搜尋第二個空格,從第七個位置 (W) 開始,這是步驟 2 中所找到的結果。(12)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 3 中從左邊開始算到第二個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(16 - 12 = 4)


姓氏

姓氏從左邊的第一個字元 (K) 開始,一直到第四個字元 (n) 結尾。這個公式會擷取四個字元,從左邊的第一個字元開始。


從範例 4:Kahn, Wendy Beth 擷取姓氏的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 減去 2 以取得姓氏結尾字元 (n) 的數值位置。結果會是您要 LEFT 擷取的字元數。(6 - 2 =4)


向上箭號

頁首 頁首

範例 5:Mary Kay D. Andersen


在這個範例中,名字是由兩個部分所組成:Mary Kay。第二個及第三個空格隔開了各名稱元件。

 
1
2
A B C D
姓名 名字 (Mary Kay) 中間名縮寫 (D.) 姓氏 (Andersen)
Mary Kay D. Anderson =LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元開始,一直到第九個字元 (第二個空格) 結尾。這個公式使用巢狀 SEARCH 函數尋找左邊開始的第二個空格。

這個公式會擷取九個字元,從左邊的第一個字元開始。


從範例 5:Mary Kay A. Wilde 擷取名字的公式

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (K) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

按鈕圖像 在 A2 中搜尋第二個空格,從第六個位置 (K) 開始,這是步驟 2 中所找到的結果。結果會是 LEFT 從文字字串擷取的字元數。(9)


中間名

中間名從第十個位置 (D) 開始,一直到第十二個位置 (第三個空格) 結尾。這個公式使用巢狀 SEARCH 函數尋找第一個、第二個及第三個空格。

這個公式會從中間擷取兩個字元,從第十個位置開始。


從範例 5:Mary Kay A. Wilde 擷取中間名的公式

使用巢狀 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (K)。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

按鈕圖像 在 A2 中搜尋第二個空格的位置,從第六個位置 (K) 開始,這是步驟 2 中所找到的結果。結果會是 LEFT 從左邊的第一個字元開始擷取的字元數。(9)

按鈕圖像 加入 1 以取得第二個空格之後的字元 (D)。結果會是中間名的起始位置。(9 + 1 = 10)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

圖說文字 5 搜尋第二個空格之後字元 (D) 的數值位置。結果會是您要開始用於搜尋第三個空格的字元數。(10)

圖說文字 6 在 A2 中搜尋第三個空格的數值位置,從左邊的第一個字元開始。結果會是中間名的結尾位置。(12)

圖說文字 7 搜尋第二個空格之後字元 (D) 的數值位置。結果會是中間名的起始位置。(10)

圖說文字 8 將步驟 6 找到的第三個空格的字元數,減去步驟 7 中 "D" 的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 4 找到的第十個位置開始。(12 - 10 = 2)


姓氏

姓氏從右邊的第八個字元開始。這個公式使用巢狀 SEARCH 函數尋找分別在第五個、第九個及第十二個位置的第一個、第二個及第三個空格。

這個公式會擷取八個字元,從右邊的第一個字元開始。


從範例 5:Mary Kay A. Wilde 擷取姓氏的公式

使用巢狀 SEARCH 函數與 LEN 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (K)。結果會是您要開始用於搜尋空格的字元數。(5 + 1 = 6)

按鈕圖像 在 A2 中搜尋第二個空格,從第六個位置 (K) 開始,這是步驟 2 中所找到的結果。(9)

按鈕圖像 加入 1 以取得第二個空格之後字元 (D) 的位置。結果會是中間名的起始位置。(9 + 1 = 10)

圖說文字 5 在 A2 中搜尋第三個空格的數值位置,從左邊的第一個字元開始。結果會是中間名的結尾位置。(12)

圖說文字 6 計算 A2 中的文字字串總長度,然後減去步驟 5 中從左邊開始算到第三個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(20 - 12 = 8)


向上箭號

頁首 頁首

範例 6:Paula Barreto de Mattos


在這個範例中,姓氏是由三個部分所組成:Barreto de Mattos。第一個空格表示是名字的結尾和姓氏的開頭。

 
1
2
A B D
姓名 名字 (Paula) 姓氏 (Barreto de Mattos)
Paula Barreto de Mattos =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元 (P) 開始,一直到第六個字元 (第一個空格) 結尾。這個公式會擷取六個字元,從左邊的第一個字元開始。


從範例 6:Job van den Wildenberg 擷取名字的公式

使用 Search 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)


姓氏

姓氏從右邊的第十七個字元 (B) 開始,一直到右邊的第一個字元 (s) 結尾。這個公式會擷取十七個字元,從右邊的第一個字元開始。


從範例 6:Job van den Wildenberg 擷取姓氏的公式

使用 LEN 函數與 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 1 中從左邊開始算到第一個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(23 - 6 = 17)


向上箭號

頁首 頁首

範例 7:James van Eaton


在這個範例中,姓氏是由兩個部分所組成:van Eaton。第一個空格表示是名字的結尾和姓氏的開頭。

 
1
2
A B D
姓名 名字 (James) 姓氏 (van Eaton)
James van Eaton =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元 (J) 開始,一直到第六個字元 (第一個空格) 結尾。這個公式會擷取六個字元,從左邊的第一個字元開始。


從範例 7:Michael St. James 擷取名字的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)


姓氏

姓氏從右邊的第九個字元 (v) 開始,一直到右邊的第一個字元 (n) 結尾。這個公式會擷取九個字元,從全名右邊的第一個字元開始。


從範例 7:Michael St. James 擷取姓氏的公式

使用 LEN 函數與 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 1 中從左邊開始算到第一個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(15 - 6 = 9)



向上箭號

頁首 頁首

範例 8:Bacon Jr., Dan K.


在這個範例中,姓氏在最前面,後面接著稱謂。逗號將姓氏和稱謂與名字和中間名縮寫隔開。

 
1
2
A B C D E
姓名 名字 (Dan) 中間名縮寫 (K.) 姓氏 (Bacon) 稱謂 (Jr.)
Bacon Jr., Dan K. =MID(A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ", A2,1)+1,(SEARCH(" ",A2,
SEARCH(" ",A2,1)+1)-2)-SEARCH(" ",A2,1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從第十二個字元 (D) 開始,一直到第十五個字元 (第三個空格) 結尾。這個公式會擷取三個字元,從第十二個位置開始。


從範例 8:Bacon Jr., Dan K. 擷取名字的公式

使用巢狀 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (J)。結果會是您要開始用於搜尋第二個空格的字元數。(6 + 1 = 7)

按鈕圖像 在 A2 中搜尋第二個空格,從第七個位置 (J) 開始,這是步驟 2 中所找到的結果。(11)

按鈕圖像 加入 1 以取得第二個空格之後的字元 (D)。結果會是名字的起始位置。(11 + 1 = 12)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

圖說文字 5 搜尋第二個空格之後字元 (D) 的數值位置。結果會是您要開始用於搜尋第三個空格的字元數。(12)

圖說文字 6 在 A2 中搜尋第三個空格的數值位置,從左邊的第一個字元開始。結果會是名字的結尾位置。(15)

圖說文字 7 搜尋第二個空格之後字元 (D) 的數值位置。結果會是名字的起始位置。(12)

圖說文字 8 將步驟 6 找到的第三個空格的字元數,減去步驟 7 中 "D" 的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 4 找到的第十二個位置開始。(15 - 12 = 3)


中間名

中間名從右邊的第二個字元 (K) 開始。這個公式會擷取兩個字元,從右邊的第一個字元開始。


從範例 8:Bacon Jr., Dan K. 擷取中間名的公式

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (J)。結果會是您要開始用於搜尋第二個空格的字元數。(6 + 1 = 7)

按鈕圖像 在 A2 中搜尋第二個空格,從第七個位置 (J) 開始,這是步驟 2 中所找到的結果。(11)

按鈕圖像 加入 1 以取得第二個空格之後的字元 (D)。結果會是名字的起始位置。(11 + 1 = 12)

圖說文字 5 在 A2 中搜尋第三個空格的數值位置,從左邊的第一個字元開始。結果會是中間名的結尾位置。(15)

圖說文字 6 計算 A2 中的文字字串總長度,然後減去步驟 5 中從左邊開始算到第三個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(17 - 15 = 2)


姓氏

姓氏從左邊的第一個字元 (B) 開始,一直到第六個字元 (第一個空格) 結尾。因此,這個公式會擷取六個字元,從左邊的第一個字元開始。


從範例 8:Bacon Jr., Dan K. 擷取姓氏的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)


稱謂

稱謂從左邊的第七個字元 (J) 開始,一直到左邊的第九個字元 (.) 結尾。這個公式會擷取三個字元,從第七個字元開始。


從範例 8:Bacon Jr., Dan K. 擷取稱謂的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (J)。結果會是稱謂的起始位置。(6 + 1 = 7)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 加入 1 以取得第一個空格之後字元 (J) 的數值位置。結果會是您要開始用於搜尋第二個空格的字元數。(7)

圖說文字 5 在 A2 中搜尋第二個空格的數值位置,從步驟 4 中所找到的第七個字元開始。(11)

圖說文字 6 從步驟 4 所找到的第二個空格字元數減去 1,以取得 "," 的字元數。結果會是稱謂的結尾位置。(11 - 1 = 10)

圖說文字 7圖說文字 8 搜尋第一個空格之後字元 (J) 的數值位置,這也可在步驟 3 和 4 中找到。(7)

圖說文字 9 將步驟 6 找到的 "," 字元數,減去步驟 3 和 4 中 "J" 的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第七個位置開始。(10 - 7 = 3)



向上箭號

頁首 頁首

範例 9:Gary Altman III


在這個範例中,名字位於字串的開頭,稱謂則位於結尾。這些名稱元件使用的公式與範例 2 類似,其中名字會使用 LEFT 函數擷取,姓氏會使用 MID 函數擷取,而稱謂則可使用 RIGHT 函數擷取。

 
1
2
A B C D
姓名 名字 (Gary) 姓氏 (Altman) 稱謂 (III)
Gary Altman III =LEFT(A2, SEARCH(" ",A2,1)) =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元 (G) 開始,一直到第五個字元 (第一個空格) 結尾。因此,這個公式會擷取五個字元,從全名左邊的第一個字元開始。


從範例 9:James Edwards III 擷取名字的公式

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)


姓氏

姓氏從左邊的第六個字元 (A) 開始,一直到第十一個字元 (第二個空格) 結尾。這個公式使用巢狀 SEARCH 函數尋找空格的位置。

這個公式會從中間擷取六個字元,從第六個字元開始。


從範例 9:James Edwards III 擷取姓氏的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (A) 的位置。結果會是姓氏的起始位置。(5 + 1 = 6)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後字元 (A) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

圖說文字 5 在 A2 中搜尋第二個空格的數值位置,從步驟 4 所找到的第六個字元開始。這個字元數會是姓氏的結尾位置。(12)

圖說文字 6圖說文字 7 搜尋第一個空格之後字元 (A) 的數值位置,這也可在步驟 3 和 4 中找到。(6)

圖說文字 8 將步驟 5 找到的第二個空格的字元數,減去步驟 6 和 7 中 "A" 的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第六個位置開始。(12 - 6 = 6)


稱謂

稱謂從右邊的第三個字元開始。這個公式使用巢狀 SEARCH 函數尋找空格的位置。


從範例 9:James Edwards III 擷取稱謂的公式

使用巢狀 SEARCH 函數與 LEN 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(5)

按鈕圖像 加入 1 以取得第一個空格之後的字元 (A)。結果會是您要開始用於搜尋第二個空格的字元數。(5 + 1 = 6)

按鈕圖像 在 A2 中搜尋第二個空格,從第六個位置 (A) 開始,這是步驟 2 中所找到的結果。(12)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 3 中從左邊開始算到第二個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(15 - 12 = 3)



向上箭號

頁首 頁首

範例 10:Mr. Ryan Ihrig


在這個範例中,全名前面加上了稱謂。這些名稱元件使用的公式與範例 2 類似,其中名字可使用 MID 函數擷取,姓氏則可使用 RIGHT 函數擷取。

 
1
2
A B C
姓名 名字 (Ryan) 姓氏 (Ihrig)
Mr. Ryan Ihrig =MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第五個字元 (R) 開始,一直到第九個字元 (第二個空格) 結尾。這個公式使用巢狀 SEARCH 函數尋找空格的位置。

這個公式會擷取四個字元,從第五個位置開始。


從範例 10:Mr. Ryan Ihrig 擷取名字的公式

使用 SEARCH 函數,尋找 start_num 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(4)

按鈕圖像 加入 1 以取得第一個空格之後字元 (R) 的位置。結果會是名字的起始位置。(4 + 1 = 5)

使用巢狀 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(4)

按鈕圖像 加入 1 以取得第一個空格之後字元 (R) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(4 + 1 = 5)

圖說文字 5 在 A2 中搜尋第二個空格的數值位置,從步驟 3 和 4 所找到的第五個字元開始。這個字元數會是名字的結尾位置。(9)

圖說文字 6圖說文字 7 搜尋第一個空格之後字元 (R) 的數值位置,這也可在步驟 3 和 4 中找到。(5)

圖說文字 8 將步驟 5 找到的第二個空格的字元數,減去步驟 6 和 7 中 "R" 的字元數。結果會是 MID 從文字字串擷取的字元數,從步驟 2 找到的第五個位置開始。(9 - 5 = 4)


姓氏

姓氏從右邊的第五個字元開始。這個公式使用巢狀 SEARCH 函數尋找空格的位置。


從範例 10:Mr. Ryan Ihrig 擷取姓氏的公式

使用巢狀 SEARCH 函數與 LEN 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(4)

按鈕圖像 加入 1 以取得第一個空格之後字元 (R) 的位置。結果會是您要開始用於搜尋第二個空格的字元數。(4 + 1 = 5)

按鈕圖像 在 A2 中搜尋第二個空格,從第五個位置 (R) 開始,這是步驟 2 中所找到的結果。(9)

按鈕圖像 計算 A2 中的文字字串總長度,然後減去步驟 3 中從左邊開始算到第二個空格的字元數。結果會是從全名右邊的第一個字元開始擷取的字元數。(14 - 9 = 5)



向上箭號

頁首 頁首

範例 11:Julie Taft-Rider


在這個範例中,姓氏加上了連字號。每個名稱元件之間都有一個空格隔開。

 
1
2
A B C
姓名 名字 (Julie) 姓氏 (Taft-Rider)
Julie Taft-Rider =LEFT(A2, SEARCH(" ",A2,1)) =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

 附註   在下列圖形下,全名中強調顯示的部分表示符合 SEARCH 公式所尋找的字元。

名字

名字從左邊的第一個字元開始,一直到第六個位置 (第一個空格) 結尾。這個公式會擷取六個字元,從左邊的第一個字元開始。


從範例 11:Julie Taft-Rider 擷取名字的公式

使用 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋第一個空格的數值位置,從左邊的第一個字元開始。(6)


姓氏

整個姓氏從右邊的第十個字元 (T) 開始,一直到右邊的第一個字元 (r) 結尾。


從範例 11:Julie Taft-Rider 擷取完整姓氏的公式

使用 LEN 函數與 SEARCH 函數,尋找 num_chars 的值:

按鈕圖像 在 A2 中搜尋空格的數值位置,從左邊的第一個字元開始。(6)

按鈕圖像 計算要擷取的文字字串總長度,然後減去步驟 1 中從左邊開始算到第一個空格的字元數。(16 - 6 = 10)



向上箭號

頁首 頁首

 
 
適用:
Excel 2007