LEFT、MID、RIGHT、SEARCH、LEN テキスト関数を使用して、データ内のテキスト文字列を操作できます。 たとえば、1 つのセルの名、ミドル ネーム、姓を 3 つの別々の列に分散できます。
テキスト関数を使用して名前コンポーネントを配布する際の鍵は、テキスト文字列内の各文字の位置です。 テキスト文字列内のスペースの位置も、文字列内の名前コンポーネントの先頭または末尾を示すため重要です。
たとえば名と姓のみが含まれるセルでは、姓はスペースの最初のインスタンスの後に始まります。 リストの名前によっては、ミドル ネームが含まれる場合があります。ミドル ネームの場合、姓はスペースの 2 番目のインスタンスの後に始まります。
この記事では、これらの便利な関数を使用して、さまざまな名前形式からさまざまなコンポーネントを抽出する方法について説明します。 テキストを列に変換ウィザードを使用して、テキストを複数の列に分割することもできます
| 名前の例 | 説明 | 名 | ミドル ネーム | 姓 | サフィックス | |
|---|---|---|---|---|---|---|
| 1 | Jeff Smith | ミドルネームなし | 由香 | 木山 勇 | ||
| 2 | Eric S. Kurjan | 1 つのミドル イニシャル | Eric | S. | クルジャン語 | |
| 3 | ジャナイナ B. G. ブエノ | 2 つの中央のイニシャル | Janaina | B. G. | ブエノ | |
| 4 | Kahn, Wendy Beth | 姓が先頭、コンマ付き | Wendy | Beth | カーン | |
| 5 | メアリー・ケイ・D・アンダーセン | 2 部構成の名 | Mary Kay | D. | Andersen | |
| 6 | Paula Barreto de Mattos | 3 部構成の姓 | Paula | Barreto de Mattos | ||
| 7 | James van Eaton | 2 部構成の姓 | ジェームズ | van Eaton | ||
| 8 | ベーコン・ジュニア、ダン・K. | 姓とサフィックスの先頭、コンマ付き | Dan | K. | ベーコン | Jr. |
| 9 | ゲイリー・アルトマン3世 | サフィックスあり | ゲーリー | Altman | III | |
| 10 | ライアン・イリッグ氏 | プレフィックスあり | Ryan | Ihrig | ||
| 11 | Julie Taft-Rider | ハイフンで囲まれた姓 | 原田 | Taft-Rider |
注
次の例のグラフィックで、フル ネームの強調表示部分は、一致する SEARCH 数式が探している文字を示しています。
例 1: Jeff と Smith の分離
この例では、名と姓の 2 つのコンポーネントを分離します。 2 つの名前を 1 つのスペースで区切ります。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| Jeff Smith | ミドルネームなし |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (姓) |
| '=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
名
名は、文字列内の先頭の文字 (J) から始まり、5 文字目 (スペース) で終わります。 次の式は、セル A2 の左から 5 文字を返します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 に含まれるスペースの位置を左から検索します。姓
姓は、右から 5 文字目のスペースから始まり、右端の文字 (h) で終了します。 次の式は、A2 の右から 5 文字を抽出します。
SEARCH 関数と LEN 関数を使って、"文字数" 引数に渡す値を取得します。
A2 に含まれるスペースの位置を左から検索します。 (5)文字列全体の長さを取得し、その文字数と、手順 1 で検索した最初のスペースの左までの文字数との差を求めます。
例 2: Eric S. Kurjan: 姓と名、およびミドル イニシャルを抽出します
この例では、名、ミドル イニシャル、姓を使用します。 各名前コンポーネントはスペースで区切られます。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| Eric S. Kurjan | 1 つのミドル イニシャル |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (ミドル イニシャル) |
| '=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
名
名は左から最初の文字 (E) から始まり、5 番目の文字 (最初のスペース) で終わります。 この数式は、左から順に A2 の最初の 5 文字を抽出します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 に含まれるスペースの位置を左から検索します。 (5)ミドル ネーム
ミドル ネームは 6 番目の文字の位置 (S) で始まり、8 番目の位置 (2 番目のスペース) で終わります。 この数式では、SEARCH 関数をネストしてスペースの 2 番目のインスタンスを見つけます。
この数式は、6 番目の位置から始まる 3 文字を抽出します。
SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (5).1 を加算すると、最初のスペース (S) の後の文字の位置が取得されます。 この数字の位置は、ミドル ネームの開始位置です。 (5 + 1 = 6)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (5)1 を加算すると、最初のスペース (S) の後の文字の位置が取得されます。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字番号です。 (5 + 1 = 6)
手順 4 で見つけた 6 番目の位置 (S) から始まる A2 のスペースの 2 番目のインスタンスを検索します。 この文字番号は、ミドル ネームの終了位置です。 (8)
左から最初の文字から始まる A2 のスペースの数値位置を検索します。 (5)
手順 5 で見つかった 2 番目のスペースの文字番号を取り、手順 6 で見つかった最初のスペースの文字番号を減算します。 この結果は、手順 2 で見つかった 6 番目の位置から始まるテキスト文字列から MID が抽出する文字数です。 (8 – 5 = 3)
姓
姓は右から 6 文字 (K) で始まり、右から最初の文字 (n) で終わります。 この数式には、SEARCH 関数を入れ子にして、スペースの 2 番目と 3 番目のインスタンス (左側から 5 番目と 8 番目の位置) を見つけます。
数式は、A2 の右から始まる 6 文字を抽出します。
LEN 関数とネストされた SEARCH 関数を使用して、num_charsの値を検索します。
左から最初の文字から始まる A2 のスペースの数値位置を検索します。 (5)1 を加算すると、最初のスペース (S) の後の文字の位置が取得されます。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字番号です。 (5 + 1 = 6)
手順 2 で見つけた 6 番目の位置 (S) から始まる、A2 内のスペースの 2 番目のインスタンスを検索します。 この文字番号は、ミドル ネームの終了位置です。 (8)
A2 のテキスト文字列の合計長を数え、手順 3 で見つけた空白の左側から 2 番目のインスタンスまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (14 – 8 = 6)。
例 3: Janaina B. G. Bueno
2 つのミドル イニシャルを抽出する方法の例を次に示します。 スペースの 1 番目と 3 番目のインスタンスは、名前コンポーネントを区切ります。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| ジャナイナ B. G. ブエノ | 2 つの中央のイニシャル |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (ミドル イニシャル) |
| '=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) |
名
先の名前は左から最初の文字 (J) から始まり、8 番目の文字 (最初のスペース) で終わります。 この数式は、左から順に A2 の最初の 8 文字を抽出します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (8)ミドル ネーム
ミドルネームは9番目の位置(B)から始まり、14番目の位置(3番目のスペース)で終わります。 この数式では、SEARCH をネストして、8 番目、11 番目、14 番目の位置にある空間の 1 番目、2 番目、3 番目のインスタンスを見つけます。
この数式は、9 番目の位置から始まる 5 文字を抽出します。
SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (8)1 を加算すると、最初のスペース (B) の後の文字の位置が得られます。 この数字の位置は、ミドル ネームの開始位置です。 (8 + 1 = 9)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (8)1 を加算すると、最初のスペース (B) の後の文字の位置が得られます。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字番号です。 (8 + 1 = 9)
手順 4 で見つけた 9 番目の位置 (B) から始まる A2 の 2 番目のスペースを検索します。 (11).
1 を加算すると、2 番目のスペース (G) の後の文字の位置が得られます。 この文字番号は、3 番目のスペースの検索を開始する開始位置です。 (11 + 1 = 12)
手順 6 で見つけた 12 番目の位置から始まる A2 の 3 番目のスペースを検索します。 (14)
A2 の最初のスペースの数値位置を検索します。 (8)
手順 7 で見つかった 3 番目のスペースの文字番号を取り、手順 6 で見つかった最初のスペースの文字番号を減算します。 結果は、手順 2 で見つけた 9 番目の位置から始まるテキスト文字列から MID 抽出する文字数です。
姓
姓は右から 5 文字 (B) で始まり、右から最初の文字 (o) で終わります。 この数式では、SEARCH をネストして、空間の 1 番目、2 番目、3 番目のインスタンスを見つけます。
数式は、フル ネームの右から始まる A2 の 5 文字を抽出します。
入れ子になった SEARCH 関数と LEN 関数を使用して、num_charsの値を検索します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (8)1 を加算すると、最初のスペース (B) の後の文字の位置が得られます。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字番号です。 (8 + 1 = 9)
手順 2 で見つけた 9 番目の位置 (B) から始まる A2 の 2 番目のスペースを検索します。 (11)
1 を加算すると、2 番目のスペース (G) の後の文字の位置が得られます。 この文字番号は、空間の 3 番目のインスタンスの検索を開始する開始位置です。 (11 + 1 = 12)
手順 6 で見つけた 12 番目の位置 (G) から始まる A2 の 3 番目のスペースを検索します。 (14)
A2 のテキスト文字列の合計長を数え、手順 5 で見つけた 3 番目のスペースまでの文字数を左から減算します。 結果は、フル ネームの右側から抽出する文字数です。 (19 - 14 = 5)
例 4: Kahn、Wendy Beth
この例では、名の前に姓があり、最後にミドル ネームがあります。 姓の末尾はコンマで示され、各名前コンポーネントはスペースで区切られます。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| Kahn, Wendy Beth | 姓が先頭、コンマ付き |
| 数式 | 結果 (名) |
| '=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
| 数式 | 計算結果 (姓) |
| '=LEFT(A2, SEARCH(" ",A2,1)-2) | =LEFT(A2, SEARCH(" ",A2,1)-2) |
名
名は、左から 7 文字目 (W) から始まり、12 文字目 (2 つ目のスペース) で終わります。 名は氏名の中央にあるため、MID 関数を使って抽出する必要があります。
この数式は、7 番目の位置から始まる 6 文字を抽出します。
SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (6)1 を加算すると、文字の最初のスペース (W) の後の位置が得られます。 この数字の位置は、名の開始位置です。 (6 + 1 = 7)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (6)1 を加算すると、文字の最初のスペース (W) の後の位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (6 + 1 = 7)
手順 4 で見つけた 7 番目の位置 (W) から始まる A2 の 2 番目のスペースを検索します。 (12)A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (6)
手順 5 で見つかった 2 番目のスペースの文字番号を取り、手順 6 で見つかった最初のスペースの文字番号を減算します。 結果は、手順 2 で見つけた 7 番目の位置から始まるテキスト文字列から MID が抽出する文字数です。 (12 - 6 = 6)
ミドル ネーム
ミドル ネームは、右から 4 文字目 (B) から始まり、右端の文字 (h) で終わります。 次の式では、ネストされた SEARCH 関数を使って、左から 6 文字目にある最初のスペースと左から 12 文字目にある 2 つ目のスペースを取得します。
この数式は、右から順に 4 文字を抽出します。
ネストされた SEARCH 関数と LEN 関数を使って、"開始位置" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (6)1 を加算すると、文字の最初のスペース (W) の後の位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (6 + 1 = 7)
手順 2 で見つけた 7 番目の位置 (W) から始まる A2 のスペースの 2 番目のインスタンスを検索します。 (12)
A2 のテキスト文字列の合計長を数え、手順 3 で見つけた左側から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (16 - 12 = 4)
姓
姓は、左端の文字 (K) から始まり、4 文字目 (n) で終わります。 次の式は、左から 4 文字を抽出します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (6)2 を引いて、姓 (n) の末尾の文字の数値位置を取得します。 結果は、取り出す文字数です。 (6 - 2 =4)
例5:メアリー・ケイ・D・アンダーセン
この例では、2 部構成の名 Mary Kay を使用します。 2 番目と 3 番目のスペースは、各名前コンポーネントを区切ります。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| メアリー・ケイ・D・アンダーセン | 2 部構成の名 |
| 数式 | 結果 (名) |
| LEFT(A2, SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) | =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)) | =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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) |
名
先の名前は左から最初の文字から始まり、9 番目の文字 (2 番目のスペース) で終わります。 この数式では、SEARCH をネストして左側から 2 番目の空間インスタンスを見つけます。
この数式は、左から始めて 9 文字を抽出します。
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (5)1 を加算すると、文字の最初のスペース (K) の後の位置が得られます。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字番号です。 (5 + 1 = 6)
手順 2 で見つけた 6 番目の位置 (K) から始まる A2 のスペースの 2 番目のインスタンスを検索します。 結果は、LEFT がテキスト文字列から抽出した文字数です。 (9)
ミドル ネーム
ミドルネームは10番目の位置(D)から始まり、12番目の位置(3番目のスペース)で終わります。 この数式では、SEARCH をネストして、空間の 1 番目、2 番目、3 番目のインスタンスを見つけます。
この数式は、中央から 10 番目の位置から 2 文字を抽出します。
入れ子になった SEARCH 関数を使用して、start_num値を検索します。
A2 の最初のスペースの数字の位置を、左から最初の文字から検索します。 (5)1 を加算すると、最初のスペース (K) の後の文字が取得されます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (5 + 1 = 6)
手順 2 で見つけた 6 番目の位置 (K) から始まる、A2 のスペースの 2 番目のインスタンスの位置を検索します。 結果は、LEFT が左側から抽出した文字の数です。 (9)
1 を加えると、2 番目のスペース (D) の後の文字が取得されます。 結果はミドルネームの開始位置です。 (9 + 1 = 10)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
2 番目のスペース (D) の後の文字の数字の位置を検索します。 結果は、3 番目のスペースの検索を開始する文字番号です。 (10)A2 の 3 番目のスペースの数字の位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (12)
2 番目のスペース (D) の後の文字の数字の位置を検索します。 結果は、ミドル ネームの開始位置です。 (10)
手順 6 で見つかった 3 番目のスペースの文字番号を取り、手順 7 で見つかった "D" の文字番号を引きます。 結果は、手順 4 で見つかった 10 番目の位置から始まるテキスト文字列から MID 抽出する文字数です。 (12 - 10 = 2)
姓
姓は右から 8 文字から始まります。 この数式では、SEARCH をネストして、5 番目、9 番目、12 番目の位置にある 1 番目、2 番目、3 番目の空間を見つけます。
この数式は、右から 8 文字を抽出します。
ネストされた SEARCH 関数と LEN 関数を使用して、num_charsの値を探します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (5)1 を加算すると、最初のスペース (K) の後の文字が取得されます。 結果は、スペースの検索を開始する文字番号です。 (5 + 1 = 6)
手順 2 で見つけた 6 番目の位置 (K) から始まる A2 の 2 番目のスペースを検索します。 (9)
1 を加算すると、2 番目のスペース (D) の後の文字の位置が得られます。 結果はミドルネームの開始位置です。 (9 + 1 = 10)
A2 の 3 番目のスペースの数字の位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (12)
A2 のテキスト文字列の合計長を数え、手順 5 で見つけた 3 番目のスペースまでの文字数を左から減算します。 結果は、フル ネームの右側から抽出する文字数です。 (20 - 12 = 8)
例 6: Paula Barreto de Mattos
この例では、Barreto de Mattos という 3 つの部分からなる姓を使用します。 最初のスペースは、名の終わりと姓の先頭を示します。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| Paula Barreto de Mattos | 3 部構成の姓 |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (姓) |
| RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
名
名は左から最初の文字 (P) から始まり、6 番目の文字 (最初のスペース) で終わります。 この数式は、左側から 6 文字を抽出します。
検索関数を使用して、num_charsの値を検索します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)姓
姓は右から 17 文字 (B) で始まり、右から最初の文字 (S) で終わります。 この数式は、右から 17 文字を抽出します。
LEN 関数と SEARCH 関数を使用して、num_chars値を検索します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)A2 のテキスト文字列の全長を数え、手順 1 で見つけた左から最初のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (23 - 6 = 17)
例 7: James van Eaton
この例では、2 部から成る姓 van Eaton を使用します。 最初のスペースは、名の終わりと姓の先頭を示します。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| James van Eaton | 2 部構成の姓 |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (姓) |
| '=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
名
先の名前は左から最初の文字 (J) から始まり、8 番目の文字 (最初のスペース) で終わります。 この数式は、左側から 6 文字を抽出します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)姓
姓は右から 9 番目の文字 (v) から始まり、右から最初の文字 (n) で終わります。 数式は、フル ネームの右側から 9 文字を抽出します。
LEN 関数と SEARCH 関数を使用して、num_chars値を検索します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)A2 のテキスト文字列の全長を数え、手順 1 で見つけた左から最初のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (15 - 6 = 9)
例 8: ベーコン ジュニア、ダン K.
この例では、姓が最初に来て、その後にサフィックスが続きます。 コンマは、姓とサフィックスを名とミドル イニシャルから区切ります。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| ベーコン・ジュニア、ダン・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)) | =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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)) |
| 数式 | 結果 (姓) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (サフィックス) |
| '=MID(A2,SEARCH(" ", A2,1)+1,(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-2)-SEARCH(" ",A2,1)) | =MID(A2,SEARCH(" ", A2,1)+1,(SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-2)-SEARCH(" ",A2,1)) |
名
名は 12 文字目 (D) で始まり、15 文字目 (3 番目のスペース) で終わります。 この数式は、12 番目の位置から始まる 3 文字を抽出します。
入れ子になった SEARCH 関数を使用して、start_num値を検索します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)1 を加算して最初のスペース (J) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字番号です。 (6 + 1 = 7)
手順 2 で見つけた A2 の 7 番目の位置 (J) から始まる 2 番目のスペースを検索します。 (11)
1 を加えると、2 番目のスペース (D) の後の文字が取得されます。 結果は名の開始位置です。 (11 + 1 = 12)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
2 番目のスペース (D) の後の文字の数字の位置を検索します。 結果は、3 番目のスペースの検索を開始する文字番号です。 (12)A2 の 3 番目のスペースの数字の位置を左から検索します。 結果は名の終了位置です。 (15)
2 番目のスペース (D) の後の文字の数字の位置を検索します。 結果は名の開始位置です。 (12)
手順 6 で見つかった 3 番目のスペースの文字番号を取り、手順 7 で見つかった "D" の文字番号を引きます。 この結果は、手順 4 で見つけた 12 番目の位置から始まるテキスト文字列から MID 抽出した文字数です。 (15 - 12 = 3)
ミドル ネーム
ミドル ネームは右から 2 番目の文字 (K) で始まります。 この数式は、右側から 2 文字を抽出します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)
1 を加算して最初のスペース (J) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字番号です。 (6 + 1 = 7)
手順 2 で見つけた A2 の 7 番目の位置 (J) から始まる 2 番目のスペースを検索します。 (11)
1 を加えると、2 番目のスペース (D) の後の文字が取得されます。 結果は名の開始位置です。 (11 + 1 = 12)
A2 の 3 番目のスペースの数字の位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (15)
A2 のテキスト文字列の全長を数え、手順 5 で見つけた 3 番目のスペースまでの文字数を左から減算します。 結果は、フル ネームの右側から抽出する文字数です。 (17 - 15 = 2)
姓
姓は左から最初の文字 (B) から始まり、6 番目の文字 (最初のスペース) で終わります。 したがって、この数式は左側から 6 文字を抽出します。
SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)サフィックス
サフィックスは左から 7 文字目 (J) から始まり、左から 9 文字目 (.) で終わります。 この数式は、7 文字目から始まる 3 文字を抽出します。
SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)1 を加算して最初のスペース (J) の後の文字を取得します。 結果はサフィックスの開始位置です。 (6 + 1 = 7)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)1 を加算すると、文字の最初のスペース (J) の後の数値位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (7)
手順 4 で見つかった 7 番目の文字から始まる A2 の 2 番目のスペースの数値位置を検索します。 (11)
手順 4 で見つかった 2 番目のスペースの文字数から 1 を引いて、"," の文字数を取得します。 結果はサフィックスの終了位置です。 (11 - 1 = 10)
最初のスペースの数値位置を検索します。 (6)
最初のスペースを見つけたら、1 を加算して次の文字 (J) を見つけます。これも手順 3 と 4 で見つけました。 (7)
手順 6 で見つかった "," の文字番号を取り、手順 3 と 4 で見つけた "J" の文字番号を減算します。 結果は、手順 2 で見つけた、7 番目の位置から始まるテキスト文字列から MID が抽出する文字数です。 (10 - 7 = 3)
例 9: Gary Altman III
この例では、名は文字列の先頭にあり、サフィックスは末尾にあるため、次のような数式を使用できます。例 2: LEFT 関数を使用して名を抽出し、MID 関数を使用して姓を抽出し、RIGHT 関数を使用してサフィックスを抽出します。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| ゲイリー・アルトマン3世 | 名と姓とサフィックス |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (姓) |
| '=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
名
名は左から最初の文字 (G) から始まり、5 番目の文字 (最初のスペース) で終わります。 したがって、数式はフル ネームの左側から 5 文字を抽出します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (5)
姓
姓は左から 6 文字目 (A) から始まり、11 文字目 (2 番目のスペース) で終わります。 この数式には、SEARCH を入れ子にしてスペースの位置を見つけることが含まれます。
この数式は、6 文字目から始まる中央から 6 文字を抽出します。
SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (5)1 を加算すると、最初のスペース (A) の後の文字の位置が得られます。 結果は姓の開始位置です。 (5 + 1 = 6)
ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (5)1 を加算すると、最初のスペース (A) の後の文字の位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (5 + 1 = 6)
手順 4 で見つかった 6 番目の文字から始まる A2 の 2 番目のスペースの数値位置を検索します。 この文字番号は、姓の終了位置です。 (12)
最初のスペースの数値位置を検索します。 (5)
1 を加算すると、最初のスペース (A) の後の文字の位置が求められます。これも手順 3 と 4 で見つけました。 (6)
手順 5 で見つかった 2 番目のスペースの文字番号を取り、手順 6 と 7 で見つかった "A" の文字番号を減算します。 この結果は、手順 2 で見つけた 6 番目の位置から始まるテキスト文字列から MID が抽出する文字数です。 (12 - 6 = 6)
サフィックス
サフィックスは右から 3 文字で始まります。 この数式には、SEARCH を入れ子にしてスペースの位置を見つけることが含まれます。
ネストされた SEARCH 関数と LEN 関数を使用して、num_charsの値を探します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (5)1 を加算して最初のスペース (A) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字番号です。 (5 + 1 = 6)
手順 2 で見つけた 6 番目の位置 (A) から始まる A2 の 2 番目のスペースを検索します。 (12)
A2 のテキスト文字列の合計長を数え、手順 3 で見つけた左側から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (15 - 12 = 3)
例 10: Ryan Ihrig 氏
この例では、フル ネームの前にプレフィックスを置き、例 2 のような数式を使用します: MID 関数で名を抽出し、RIGHT 関数で姓を抽出します。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| ライアン・イリッグ氏 | プレフィックスあり |
| 数式 | 結果 (名) |
| '=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+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)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,SEARCH(" ",A2,1)+1)) |
名
名は左から 5 文字目 (R) から始まり、9 文字目 (2 番目のスペース) で終わります。 この数式は SEARCH をネストして、スペースの位置を見つけます。 5 番目の位置から始まる 4 つの文字を抽出します。
SEARCH 関数を使用して、start_numの値を検索します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (4)1 を加算すると、最初のスペース (R) の後の文字の位置が得られます。 結果は名の開始位置です。 (4 + 1 = 5)
入れ子になった SEARCH 関数を使用して、次の値を検索しますnum_chars。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (4)1 を加算すると、最初のスペース (R) の後の文字の位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (4 + 1 = 5)
手順 3 と 4 で見つけた A2 の 2 番目のスペースの 5 番目の文字から始まる数値位置を検索します。 この文字番号は、名の終了位置です。 (9)
最初のスペースを検索します。 (4)
1 を加算すると、最初のスペース (R) の後の文字の位置が求められます。これも手順 3 と 4 で見つけました。 (5)
手順 5 で見つかった 2 番目のスペースの文字番号を取り、手順 6 と 7 で見つかった "R" の文字番号を減算します。 この結果は、手順 2 で見つかった 5 番目の位置から始まる、MID がテキスト文字列から抽出する文字数です。 (9 - 5 = 4)
姓
姓は右から 5 文字から始まります。 この数式には、SEARCH を入れ子にしてスペースの位置を見つけることが含まれます。
ネストされた SEARCH 関数と LEN 関数を使用して、num_charsの値を探します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (4)1 を加算すると、最初のスペース (R) の後の文字の位置が得られます。 結果は、2 番目のスペースの検索を開始する文字番号です。 (4 + 1 = 5)
手順 2 で見つけた 5 番目の位置 (R) から始まる A2 の 2 番目のスペースを検索します。 (9)
A2 のテキスト文字列の合計長を数え、手順 3 で見つけた左側から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出する文字数です。 (14 - 9 = 5)
例 11: Julie Taft-Rider
この例では、ハイフンで囲まれた姓を使用します。 各名前コンポーネントはスペースで区切られます。
表のセルをコピーし、Excel ワークシートのセル A1 に貼り付けます。 左側の数式は参照用に表示され、Excel は右側の数式を自動的に適切な結果に変換します。
ヒント ワークシートにデータを貼り付ける前に、列 A と列 B の列幅を 250 に設定します。
| 名前の例 | 説明 |
|---|---|
| Julie Taft-Rider | ハイフンで囲まれた姓 |
| 数式 | 結果 (名) |
| '=LEFT(A2, SEARCH(" ",A2,1)) | =LEFT(A2, SEARCH(" ",A2,1)) |
| 数式 | 結果 (姓) |
| '=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) | =RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1)) |
名
名は左から最初の文字から始まり、6 番目の位置 (最初のスペース) で終わります。 この数式は、左側から 6 文字を抽出します。
SEARCH 関数を使用して、num_chars値を探します。
左から始めて、A2 の最初のスペースの数値位置を検索します。 (6)姓
姓全体は、右から 10 文字 (T) で始まり、右から最初の文字 (r) で終わります。
LEN 関数と SEARCH 関数を使用して、num_chars値を検索します。
A2 のスペースの数字の位置を、左から最初の文字から検索します。 (6)抽出するテキスト文字列の合計長を数え、手順 1 で見つけた左から最初のスペースまでの文字数を減算します。 (16 - 6 = 10)