使用函數將文字分成幾欄

文字函數對於操控您資料中的字串而言相當有用。例如,將名字、中間名及姓氏從儲存格名稱分配到三個不同的欄。

本文將示範如何使用下列文字函數的組合來抽選名稱元件,並將其複製到個別的儲存格中。

函數

語法

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. 選取表格中的範例文字和公式。

    附註: 請勿選取列標題或欄標題。

    選取 [說明] 中的範例

  3. 按 CTRL+C。

  4. 在工作表中,選取儲存格 A1,然後按 CTRL+V。

  5. 若要在檢視結果與檢視傳回結果的公式之間切換,請按 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 中從左算起的五個字元。

用於擷取名字的公式

使用 SEARCH 函數尋找 num_chars 的值:

在 A2 中搜尋空格的數值位置,從左邊開始算起。(5)

姓氏

姓氏的開頭是空格,離右邊有五個字元,結尾是最後一個字元 (h)。公式擷取的是 A2 裡從右邊開始算起的五個字元。

用於擷取姓氏的公式

使用 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 裡從左邊開始算起的前五個字元。

用於分隔名字、姓氏與中間名縮寫的公式

使用 SEARCH 函數尋找 num_chars 的值:

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

中間名

中間名的開頭是第六個字元位置 (S),結尾是第八個位置 (即第二個空格)。此公式使用巢狀 SEARCH 函數,尋找第二個空格實例。

公式擷取的是從第六個位置開始算起的三個字元。

用於分隔名字、中間名及姓氏的公式細節

使用 SEARCH 函數尋找 start_num 的值:

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

加 1 可得出第一個空格後的字元 (S) 位置。此數值位置是中間名的起始位置。(5 + 1 = 6)

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

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

加 1 可得出第一個空格後的字元 (S) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格實例。(5 + 1 = 6)

在 A2 中搜尋第二個空格實例,從步驟 4 得出之第六個位置 (S) 開始算起。此字元數是中間名的結尾位置。(8)

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

用步驟 5 得出之第二個空格之字元數,減去步驟 6 得出之第一個空格的字元數。結果得出 MID 從文字字串所擷取的字元數,從步驟 2 得出之第六個位置開始算起。(8 – 5 = 3)

姓氏

姓氏的開頭是從右邊數來的第六個字元 (K),結尾是右邊數來的第一個字元 (n)。此公式使用巢狀 SEARCH 函數,尋找第二個及第三個空格實例 (即是從左邊數來的第五個和第八個位置)。

公式擷取的是 A2 裡從右邊開始算起的六個字元。

用於分隔名字、中間名及姓氏之公式中的第二個 Search 函數

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

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

加 1 可得出第一個空格後的字元 (S) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格實例。(5 + 1 = 6)

在 A2 中搜尋第二個空格實例,從步驟 2 得出之第六個位置 (S) 開始算起。此字元數是中間名的結尾位置。(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 裡從左邊開始算起的前八個字元。

用於分隔名字、姓氏及兩個中間名縮寫的公式

使用 Search 函數尋找 num_chars 的值:

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(8)

中間名

中間名的開頭是第九個位置 (B),結尾是第十四個位置 (即第三個空格)。此公式使用巢狀 SEARCH 函數,尋找分別位於第八個、第十一個及第十四個位置的第一個、第二個及第三個空格實例。

公式擷取的是從第九個位置開始算起的五個字元。

用於分隔名字、姓氏及兩個中間名縮寫的公式

使用 SEARCH 函數尋找 start_num 的值:

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

加 1 可得出第一個空格後的字元 (B) 位置。此數值位置是中間名的起始位置。(8 + 1 = 9)

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

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

加 1 可得出第一個空格後的字元 (B) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格實例。(8 + 1 = 9)

在 A2 中搜尋第二個空格,從步驟 4 得出之第九個位置 (B) 開始算起。(11)

加 1 可得出第一個空格後的字元 (G) 位置。這個字元數是一起始位置,您從這裡開始搜尋第三個空格。(11 + 1 = 12)

在 A2 中搜尋第三個空格,從步驟 6 得出之第十二個位置開始算起。(14)

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

用步驟 7 得出之第三個空格之字元數,減去步驟 6 得出之第一個空格的字元數。結果得出 MID 從文字字串擷取的字元數,從步驟 2 得出之第九個位置開始算起。

姓氏

姓氏的開頭是從右邊數來的第五個字元 (B),結尾是右邊數來的第一個字元 (o)。此公式使用巢狀 SEARCH 函數,尋找第一個、第二個和第三個空格實例。

公式會擷取 A2 裡從全名的右邊開始算起的五個字元。

用於分隔名字、姓氏及兩個中間名縮寫的公式

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

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

加 1 可得出第一個空格後的字元 (B) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格實例。(8 + 1 = 9)

在 A2 中搜尋第二個空格,從步驟 2 得出之第九個位置 (B) 開始算起。(11)

加 1 可得出第一個空格後的字元 (G) 位置。這個字元數是一起始位置,您從這裡開始搜尋第三個空格實例。(11 + 1 = 12)

在 A2 中搜尋第三個空格,從步驟 6 得出之第十二個位置開始算起。(14)

計算 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 函數來擷取名字。

公式擷取的是從第七個位置開始算起的六個字元。

用於分隔姓氏後接名字和中間名的公式

使用 SEARCH 函數尋找 start_num 的值:

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

加 1 可得出第一個空格後的字元 (W) 位置。此數值位置是名字的起始位置。(6 + 1 = 7)

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

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

加 1 可得出第一個空格後的字元 (W) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格。(6 + 1 = 7)

在 A2 中搜尋第二個空格,從步驟 4 得出之第七個位置 (W) 開始算起。(12)

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

用步驟 5 得出之第二個空格之字元數,減去步驟 6 得出之第一個空格的字元數。結果得出 MID 從文字字串擷取的字元數,從步驟 2 得出之第七個位置開始算起。(12 - 6 = 6)

中間名

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

公式擷取的是從右邊開始算起的四個字元。

用於分隔姓氏後接名字和中間名的公式

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

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

加 1 可得出第一個空格後的字元 (W) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格。(6 + 1 = 7)

在 A2 中搜尋第二個空格實例,從步驟 2 得出之第七個位置 (W) 開始算起。(12)

計算 A2 裡文字字串的總長度,然後減去步驟 3 得出之從左邊算到第二個空格的字元數。結果得出從全名右邊所擷取的字元數。(16 - 12 = 4)

姓氏

姓氏的開頭是從左邊數來的第一個字元 (K),結尾是第四個字元 (n)。公式擷取的是從左邊開始算起的四個字元。

用於分隔姓氏後接名字和中間名的公式

使用 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 函數,尋找左邊數來的第二個空格實例。

公式擷取的是從左邊開始算起的九個字元。

用於分隔名字、中間名縮寫及姓氏的公式

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

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

加 1 可得出第一個空格後的字元 (K) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格實例。(5 + 1 = 6)

在 A2 中搜尋第二個空格實例,從步驟 2 得出之第六個位置 (S) 開始算起。結果得出 LEFT 從文字字串所擷取的字元數。(9)

中間名

中間名的開頭是第十個位置 (D),結尾是第十二個位置 (即第三個空格)。此公式使用巢狀 SEARCH 函數,尋找第一個、第二個和第三個空格實例。

公式擷取的是從第十個位置開始算起的中間兩個字元。

用於分隔名字、中間名縮寫及姓氏的公式

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

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

加 1 可得出第一個空格後的字元 (K) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格。(5 + 1 = 6)

在 A2 中搜尋第二個空格實例的位置,從步驟 2 得出之第六個位置 (K) 開始算起。結果得出 LEFT 從左邊所擷取的字元數。(9)

加 1 可得出第二個空格後的字元 (D) 位置。結果得出中間名的起始位置。(9 + 1 = 10)

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

搜尋第二個空格後之字元 (D) 的數值位置。結果得出一字元數,可從該字元數位置開始搜尋第三個空格。(10)

在 A2 中搜尋第三個空格的數值位置,從左邊開始算起。結果得出中間名的結尾位置。(12)

搜尋第二個空格後之字元 (D) 的數值位置。結果得出中間名的起始位置。(10)

用步驟 6 得出之第三個空格之字元數,減去步驟 7 得出之 "D" 的字元數。結果得出 MID 從文字字串擷取的字元數,從步驟 4 得出之第十個位置開始算起。(12 - 10 = 2)

姓氏

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

公式擷取的是從右邊算起的八個字元。

用於分隔名字、中間名縮寫及姓氏的公式

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

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

加 1 可得出第一個空格後的字元 (K) 位置。結果得出一字元數,可從該字元數位置開始搜尋空格。(5 + 1 = 6)

在 A2 中搜尋第二個空格,從步驟 2 得出之第六個位置 (K) 開始算起。(9)

加 1 可得出第二個空格後的字元 (D) 位置。結果得出中間名的起始位置。(9 + 1 = 10)

在 A2 中搜尋第三個空格的數值位置,從左邊開始算起。結果得出中間名的結尾位置。(12)

計算 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),結尾是第六個字元 (即第一個空格)。公式擷取的是從左邊算起的六個字元。

用於分隔名字及由三部分組成之姓氏的公式

使用 Search 函數尋找 num_chars 的值:

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(6)

姓氏

姓氏的開頭是從右邊數來的第十七個字元 (B),結尾是右邊數來的第一個字元 (s)。公式擷取的是從右邊開始算起的十七個字元。

用於分隔名字及由三部分組成之姓氏的公式

使用 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),結尾是第八個字元 (即第一個空格)。公式擷取的是從左邊算起的六個字元。

用於分隔名字及由兩部分組成之姓氏的公式

使用 Search 函數尋找 num_chars 的值:

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(6)

姓氏

姓氏的開頭是從右邊數來的第九個字元 (v),結尾是右邊數來的第一個字元 (n)。公式擷取的是從全名的右邊開始算起的九個字元。

用於分隔名字及由兩部分組成之姓氏的公式

使用 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 中搜尋第二個空格,從步驟 2 得出之第七個位置 (J) 開始算起。(11)

加 1 可得出第二個空格後的字元 (D) 位置。結果得出名字的起始位置。(11 + 1 = 12)

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

搜尋第二個空格後之字元 (D) 的數值位置。結果得出一字元數,可從該字元數位置開始搜尋第三個空格。(12)

在 A2 中搜尋第三個空格的數值位置,從左邊開始算起。結果得出名字的結尾位置。(15)

搜尋第二個空格後之字元 (D) 的數值位置。結果得出名字的起始位置。(12)

用步驟 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 中搜尋第二個空格,從步驟 2 得出之第七個位置 (J) 開始算起。(11)

加 1 可得出第二個空格後的字元 (D) 位置。結果得出名字的起始位置。(11 + 1 = 12)

在 A2 中搜尋第三個空格的數值位置,從左邊開始算起。結果得出中間名的結尾位置。(15)

計算 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)

在 A2 中搜尋第二個空格的數值位置,從步驟 4 得出之第七個字元開始算起。(11)

步驟 4 得出之第二個空格的字元數減去 1,即可取得 "," 的字元數。結果得出後稱謂的結尾位置。(11 - 1 = 10)

搜尋第一個空格後之字元 (J) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(7)

搜尋第一個空格後之字元 (J) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(7)

用步驟 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),結尾是第五個字元 (即第一個空格)。因此,公式擷取的是從全名的左邊開始算起的五個字元。

用於分隔名字、姓氏及稱謂的公式

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

姓氏

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

公式擷取的是從第六個字元開始算起的中間六個字元。

用於分隔名字、姓氏及稱謂的公式

使用 SEARCH 函數尋找 start_num 的值:

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

加 1 可得出第一個空格後的字元 (A) 位置。結果得出姓氏的起始位置。(5 + 1 = 6)

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

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

加 1 可得出第一個空格後的字元 (A) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格。(5 + 1 = 6)

在 A2 中搜尋第二個空格的數值位置,從步驟 4 得出之第六個字元開始算起。此字元數是姓氏的結尾位置。(12)

搜尋第一個空格後之字元 (A) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(6)

搜尋第一個空格後之字元 (A) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(6)

用步驟 5 得出之第二個空格的字元數,減去步驟 6 及 7 得出之 "A" 的字元數。結果得出 MID 從文字字串擷取的字元數,從步驟 2 得出之第六個位置開始算起。(12 - 6 = 6)

後稱謂

後稱謂的開頭是從右邊開始算起的三個字元。此公式使用巢狀 SEARCH 函數,尋找空格的位置。

用於分隔名字、姓氏及稱謂的公式

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

在 A2 中搜尋第一個空格的數值位置,從左邊開始算起。(5)

加 1 可得出第一個空格後的字元 (A) 位置。結果得出一字元數,可從該字元數位置開始搜尋第二個空格。(5 + 1 = 6)

在 A2 中搜尋第二個空格,從步驟 2 得出之第六個位置 (A) 開始算起。(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)

在 A2 中搜尋第二個空格的數值位置,從步驟 3 及 4 得出之第五個字元開始算起。此字元數是名字的結尾位置。(9)

搜尋第一個空格後之字元 (R) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(5)

搜尋第一個空格後之字元 (R) 的數值位置,這亦可在步驟 3 及步驟 4 中得出。(5)

用步驟 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 中搜尋第二個空格,從步驟 2 得出之第五個位置 (R) 開始算起。(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)

頁面頂端

這項資訊有幫助嗎?

太好了! 還有其他意見反應嗎?

我們應該如何改進?

感謝您的意見反應!

×