使用函數將文字分割成不同的欄

使用函數將文字分割成不同的欄

附註: 我們想要以您的語言,用最快的速度為您提供最新的說明內容。 本頁面是經由自動翻譯而成,因此文中可能有文法錯誤或不準確之處。 讓這些內容對您有所幫助是我們的目的。 希望您能在本頁底部告訴我們這項資訊是否有幫助。 此為英文文章出處,以供參考。

您可以使用 LEFT、 MID、 RIGHT、 SEARCH 和 LEN 文字函數,來管理您的資料中的文字字串。 例如,您可以將名字、 中間名及姓氏從單一儲存格名稱分散到三個不同的欄。

散發使用文字函數名稱元件,關鍵是文字字串中的每個字元的位置。 文字字串中的空格的位置,也很重要,因為它們指示開頭或結尾的字串中的名稱元件。

例如,儲存格只包含名字和姓氏時,姓氏的開頭會在第一個空格實例的後面。 您清單中的某些名稱可能會含有中間名,在此情況下,姓氏的開頭會在第二個空格實例的後面。

本文將示範如何從不同的使用這些實用的函數名稱格式中擷取各種元件。 您可以也將文字分割成不同的欄,文字轉換為 [資料剖析精靈

範例名稱

描述

名字

中間名

姓氏

後稱謂

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

附註: 在下列範例中的圖形中,,全名中的醒目提示會顯示符合搜尋公式所尋找的字元。

這個範例中將兩個元件: 名字和姓氏。 以單一空格分隔的兩個名稱。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Jeff Smith

無中間名

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (「",A2,1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

  1. 名字

    名字的開頭是字串的第一個字元 (J),結尾是第五個字元 (空格)。 公式會傳回 A2 儲存格裡從左邊開始算起的五個字元。

    用於擷取名字的公式

    使用 SEARCH 函數尋找 num_chars 的值:

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

  2. 姓氏

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

    用於擷取姓氏的公式

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

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

  3. 計算文字字串的總長度,然後減去步驟 1 得出之第一個空格左邊的字元數。

這個範例使用名字、 中間名縮寫及姓氏的名稱。 每個名稱元件之間以空格分隔。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Eric S. Kurjan

有一個中間名縮寫

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (中間名縮寫)

' = MID (A2、 搜尋 (「 」,A2,1) + 1,搜尋 (「 」,A2,搜尋 (」 」,A2,1) + 1)-搜尋 (」 」,A2,1))

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))

公式

即時結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (「 」,A2,搜尋 (「 」,A2,1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元 (E),結尾是第五個字元 (即第一個空格)。 公式擷取的是 A2 裡從左邊開始算起的前五個字元。

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

    使用 SEARCH 函數尋找 num_chars 的值:

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

  2. 中間名

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

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

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

    使用 SEARCH 函數尋找 start_num 的值:

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

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

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

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

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

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

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

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

  8. 姓氏

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

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

    用於分隔名字、 中間名及姓氏的公式中的第二個 SEARCH 函數
  9. 使用 LEN 及巢狀 SEARCH 函數尋找 num_chars 的值:

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

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

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

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

以下是如何擷取有兩個中間名縮寫的範例。 名稱元件是由第一個和第三個空格實例分隔。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Janaina B. G. Bueno

有兩個中間名縮寫

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (中間名縮寫)

' = MID (A2、 搜尋 (」 」,A2,1) + 1,搜尋 (」 」,A2,搜尋 (」 」,A2,搜尋 (」 」,A2,1) + 1) + 1)-搜尋 (」 」,A2,1))

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1))

公式

即時結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (」 」,A2,搜尋 (」 」,A2,搜尋 (」 」,A2,1) + 1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元 (J),結尾是第八個字元 (即第一個空格)。 公式擷取的是 A2 裡從左邊開始算起的前八個字元。

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

    使用 SEARCH 函數尋找 num_chars 的值:

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

  2. 中間名

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

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

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

    使用 SEARCH 函數尋找 start_num 的值:

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

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

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

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

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

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

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

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

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

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

  10. 姓氏

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

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

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

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

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

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

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

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

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

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

在此範例中,姓氏在名字之前,而中間名出現在最後面。 逗號代表姓氏的結尾,每個名稱元件之間以空格分隔。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Kahn, Wendy Beth

姓氏置前,姓氏後有逗號

公式

結果 (名字)

' = MID (A2、 搜尋 (「 」,A2,1) + 1,搜尋 (「 」,A2,搜尋 (」 」,A2,1) + 1)-搜尋 (」 」,A2,1))

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))

公式

結果 (中間名)

' = 右 (A2,LEN (A2)-搜尋 (「 」,A2,搜尋 (「 」,A2,1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

公式

即時結果 (姓氏)

' = 左邊 (A2,SEARCH("",A2,1) 2)

=LEFT(A2, SEARCH(" ",A2,1)-2)

  1. 名字

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

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

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

    使用 SEARCH 函數尋找 start_num 的值:

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

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

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

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

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

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

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

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

  6. 中間名

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

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

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

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

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

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

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

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

  10. 姓氏

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

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

    使用 SEARCH 函數尋找 num_chars 的值:

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

  11. 減 2 以取得姓氏結尾字元 (n) 的數值位置。 結果得出 LEFT 要抽選的字元數。 (6 - 2 =4)

這個範例使用兩個部分組成的名字,Mary Kay。 由第二個和第三個空格分隔每個名稱元件。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Mary Kay D. Andersen

由兩個部分組成的名字

公式

結果 (名字)

左側 (A2、 搜尋 (「 」,A2,搜尋 (「 」,A2,1) + 1))

=LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

公式

結果 (中間名縮寫)

' = MID (A2、 搜尋 (」 」,A2,搜尋 (」 」,A2,1) + 1) + 1,搜尋 (」 」,A2、 搜尋 (「 」,A2、 搜尋 (」 」,A2,1) + 1) + 1)-(搜尋 (」 」,A2、 搜尋 (」 」,A2,1) + 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))

公式

即時結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (」 」,A2,搜尋 (」 」,A2,搜尋 (」 」,A2,1) + 1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元,結尾是第九個字元 (即第二個空格)。 此公式使用巢狀 SEARCH 函數,尋找左邊數來的第二個空格實例。

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

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

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

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

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

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

  4. 中間名

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

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

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

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

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

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

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

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

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

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

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

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

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

  11. 姓氏

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

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

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

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

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

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

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

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

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

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

此範例使用三個部分組成的姓氏: Barreto de Mattos。 第一個空格代表名字的結尾與姓氏的開頭。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Paula Barreto de Mattos

由三個部分組成的姓氏

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (姓氏)

向右 (A2,LEN (A2)-搜尋 ("",A2,1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元 (P),結尾是第六個字元 (即第一個空格)。 公式擷取的是從左邊算起的六個字元。

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

    使用 Search 函數尋找 num_chars 的值:

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

  2. 姓氏

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

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

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

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

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

此範例使用兩個部分組成的姓氏: van Eaton。 第一個空格代表名字的結尾與姓氏的開頭。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

James van Eaton

由兩個部分組成的姓氏

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (「",A2,1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元 (J),結尾是第八個字元 (即第一個空格)。 公式擷取的是從左邊算起的六個字元。

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

    使用 SEARCH 函數尋找 num_chars 的值:

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

  2. 姓氏

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

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

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

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

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

在此範例中,姓氏在最前面,後面接的是後稱謂。 姓氏和後稱謂,名字和中間名縮寫,則是以逗號隔開。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Bacon Jr., Dan K.

姓氏和後稱謂放在前面,後稱謂的後面有逗號

公式

結果 (名字)

' = MID (A2、 搜尋 (」 」,A2、 搜尋 (「 」,A2,1) + 1) + 1,搜尋 (」 」,A2,搜尋 (」 」,A2、 搜尋 (」 」,A2,1) + 1) + 1)-搜尋 (」 」,A2、 搜尋 (」 」,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))

公式

結果 (中間名縮寫)

' = 右 (A2,LEN (A2)-搜尋 (」 」,A2,搜尋 (」 」,A2,搜尋 (」 」,A2,1) + 1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1))

公式

結果 (姓氏)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (後稱謂)

' = MID (A2、 搜尋 (「 」,A2,1) + 1,(搜尋 (「 」,A2,搜尋 (」 」,A2,1) + 1)-2)-搜尋 (」 」,A2,1))

=MID(A2,SEARCH(" ", A2,1)+1,(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-2)-SEARCH(" ",A2,1))

  1. 名字

    名字的開頭是第十二個字元 (D),結尾是第十五個字元 (即第三個空格)。 公式擷取的是從第十二個位置開始算起的三個字元。

    用於分隔姓氏的公式命名詞與後置前,姓氏後有逗號

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

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

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

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

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

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

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

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

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

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

  8. 中間名

    中間名的開頭是從右邊數來的第二個字元 (K)。 公式擷取的是從右邊開始算起的兩個字元。

    用於分隔姓氏的公式命名詞與後置前,姓氏後有逗號

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

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

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

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

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

  13. 計算 A2 裡文字字串的總長度,然後減去步驟 5 得出之從左邊算到第三個空格的字元數。 結果得出從全名右邊所擷取的字元數。 (17 - 15 = 2)

  14. 姓氏

    姓氏的開頭是從左邊數來的第一個字元 (B),結尾是第六個字元 (即第一個空格)。 因此,公式擷取的是從左邊算起的六個字元。

    用於分隔姓氏的公式命名詞與後置前,姓氏後有逗號

    使用 SEARCH 函數尋找 num_chars 的值:

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

  15. 後稱謂

    後稱謂的開頭是從左邊數來的第七個字元 (J),結尾是左邊數來的第九個字元 (.)。 公式擷取的是從第七個字元開始算起的三個字元。

    用於分隔姓氏的公式命名詞與後置前,姓氏後有逗號

    使用 SEARCH 函數尋找 start_num 的值:

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

  16. 加 1 可得出第一個空格後的字元 (J) 位置。 結果得出後稱謂的起始位置。 (6 + 1 = 7)

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

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

  17. 加 1 可得出第一個空格後的字元 (J) 之數值位置。 結果得出一字元數,可從該字元數位置開始搜尋第二個空格。 (7)

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

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

  20. 第一個空格的數值位置搜尋。 (6)

  21. 後面找到第一個空格,新增 1 尋找下一個字元 (J),也會發現在步驟 3 和 4。 (7)

  22. 用步驟 6 得出之 "," 的字元數,減去步驟 3 及 4 得出之 "J" 的字元數。 結果得出 MID 從文字字串擷取的字元數,從步驟 2 得出之第七個位置開始算起。 (10 - 7 = 3)

在這個範例中,第一個姓名都字串的開頭,尾碼是在結束時,因此您可以使用公式類似範例 2: 擷取名字,MID 函數來擷取姓氏使用 LEFT 的函數並擷取稱謂正確的函數。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Gary Altman III

含後稱謂的姓氏和名字

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (姓氏)

' = MID (A2、 搜尋 (「 」,A2,1) + 1,搜尋 (「 」,A2,搜尋 (」 」,A2,1) + 1)-(搜尋 (」 」,A2,1) + 1))

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))

公式

結果 (後稱謂)

' = 右 (A2,LEN (A2)-搜尋 (「 」,A2,搜尋 (「 」,A2,1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元 (G),結尾是第五個字元 (即第一個空格)。 因此,公式擷取的是從全名的左邊開始算起的五個字元。

    用於分隔名字和姓氏後面後稱謂的公式

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

  2. 姓氏

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

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

    用於分隔名字和姓氏後面後稱謂的公式

    使用 SEARCH 函數尋找 start_num 的值:

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

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

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

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

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

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

  6. 第一個空格的數值位置搜尋。 (5)

  7. 若要尋找的字元之後第一個空格 (A),, 也在步驟 3 和 4 中找到的數值位置 1 新增。 (6)

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

  9. 後稱謂

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

    用於分隔名字和姓氏後面後稱謂的公式

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

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

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

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

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

在這個範例中,全名加前置詞,以及您使用公式類似範例 2: 擷取名字,RIGHT 函數來擷取姓氏 MID 函數。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Mr. Ryan Ihrig

含有前稱謂

公式

結果 (名字)

' = MID (A2、 搜尋 (「 」,A2,1) + 1,搜尋 (「 」,A2,搜尋 (」 」,A2,1) + 1)-(搜尋 (」 」,A2,1) + 1))

=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))

公式

結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (「 」,A2,搜尋 (「 」,A2,1) + 1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1))

  1. 名字

    名字的開頭是從左邊算起的第五個字元 (R),結尾是第九個字元 (即第二個空格)。 公式形成巢狀搜尋以尋找空格的位置。 它抽選四個字元從第五個位置開始。

    用於分隔名字的前面前置詞的公式

    使用 SEARCH 函數尋找 start_num 的值:

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

  2. 加 1 可得出第一個空格後的字元 (R) 位置。 結果得出名字的起始位置。 (4 + 1 = 5)

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

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

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

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

  5. 搜尋第一個空格。 (4)

  6. 若要尋找的字元之後第一個空格 (R),, 也在步驟 3 和 4 中找到的數值位置 1 新增。 (5)

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

  8. 姓氏

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

    用於分隔名字的前面前置詞的公式

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

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

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

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

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

這個範例使用連字號連接的姓氏。 每個名稱元件之間以空格分隔。

在表格中複製的儲存格,並貼到 Excel 工作表的 A1 儲存格。 Excel 會自動將在右側的公式轉換成適當的結果時,您在左方看見的公式將會顯示,供您參考。

提示    您將資料貼到工作表之前,請 A 和 B 欄的欄寬設為 250。

範例名稱

描述

Julie Taft-Rider

以連字號連接的姓氏

公式

結果 (名字)

' = 左邊 (A2、 搜尋 (「",A2,1))

=LEFT(A2, SEARCH(" ",A2,1))

公式

結果 (姓氏)

' = 右 (A2,LEN (A2)-搜尋 (「",A2,1))

=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))

  1. 名字

    名字的開頭是從左邊數來的第一個字元,結尾是第六個位置 (即第一個空格)。 公式擷取的是從左邊算起的六個字元。

    用於分隔名字和姓氏的斷字的公式

    使用 SEARCH 函數尋找 num_chars 的值:

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

  2. 姓氏

    整個姓氏的開頭是從右邊數來的第十個字元 (T),結尾是右邊的第一個字元 (r)。

    用於分隔名字和姓氏的斷字的公式

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

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

  3. 計算欲擷取之文字字串的總長度,然後減去步驟 1 得出之從左邊算到第一個空格的字元數。 (16 - 6 = 10)

請參閱

使用資料剖析精靈將文字分割成不同的欄

增進您的 Office 技巧
探索訓練
優先取得新功能
加入 Office 測試人員

這項資訊有幫助嗎?

感謝您的意見反應!

感謝您的意見反應! 我們將協助您與我們的其中一個 Office 支援專員連絡以深入了解您的意見。

×