関数を使用してテキストを複数の列に分割する

By /

Excel

データの入力と書式設定

レイアウト

レイアウト

関数を使用してテキストを複数の列に分割する

関数を使用してテキストを複数の列に分割する

Excel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 for Mac Excel 2016 Excel 2013 Excel Web App その他…表示数を減らす

LEFT、MID、RIGHT、SEARCH、LEN の各テキスト関数を使用して、データ内のテキストの文字列を操作できます。 たとえば、1 つのセルの先頭、中央、および姓を 3 つの個別の列に分散できます。

テキスト関数を使用して名前コンポーネントを分散するキーは、テキスト文字列内の各文字の位置です。 文字列内のスペースの位置も重要です。これは、文字列内の名前コンポーネントの先頭または末尾を示すからです。

たとえば、姓と名のみを含むセルでは、姓はスペースの最初のインスタンスの後から始まります。 リスト内の一部の名前にはミドル ネームが含まれている場合があります。その場合、姓はスペースの 2 番目のインスタンスの後から始まります。

この記事では、これらの便利な関数を使用して、さまざまな名前形式からさまざまなコンポーネントを抽出する方法について説明します。 テキストを列に変換ウィザードを使用して、テキストを異なる target=”_blank” title=”列に” rel=”noopener”>列に分割することもできます

名前の例説明名ミドル ネーム姓サフィックス
1Jeff Smithミドル ネームなし由香木山 勇
2Eric S. Kurjan1 つの中間の初期EricS.Kurjan
3Janaina B. G. ブエノ2 つの中間イニシャルJanainaB. G.ブエノ
4カーン、ウェンディ・ベス名の先頭にコンマを付けますウェンディベスカーン
5メアリー ケイ D. アンダーセン2 部構成の名メアリーケイD.Andersen
6Paula Barreto de Mattos3 部構成の姓ポーラバレート デ マトス
7James van Eaton2 部構成の姓ジェームズvan Eaton
8ベーコン Jr.、Dan K.姓とサフィックスの先頭にコンマを付けますダンK.ベーコンジュニア。
9Gary Altman IIIサフィックス付きゲーリーアルトマンIII
10Ryan Ihrig 氏プレフィックス付きRyanIhrig
11Julie Taft-Riderハイフンで区切られた姓原田Taft-Rider

注: 次の例の図では、完全な名前の強調表示は、一致する SEARCH 式が探している文字を示しています。

__

例 1: Jeff と Smith の分離

この例では、名と姓の 2 つのコンポーネントを区切ります。 2 つの名前は 1 つのスペースで区切られます。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、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))
  1. 名

名は、文字列内の先頭の文字 (J) から始まり、5 文字目 (スペース) で終わります。 次の式は、セル A2 の左から 5 文字を返します。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 に含まれるスペースの位置を左から検索します。

  1. 姓

姓は、右から 5 文字目のスペースから始まり、右端の文字 (h) で終了します。 次の式は、A2 の右から 5 文字を抽出します。

SEARCH 関数と LEN 関数を使って、"文字数" 引数に渡す値を取得します。

A2 に含まれるスペースの位置を左から検索します。 (5)

  1. 文字列全体の長さを取得し、その文字数と、手順 1 で検索した最初のスペースの左までの文字数との差を求めます。

__

例 2: Eric S. Kurjan: 姓と名を抽出し、中間のイニシャルを抽出する

この例では、名、中間の頭文字、姓を使用します。 各名前コンポーネントはスペースで区切られます。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
Eric S. Kurjan1 つの中間の初期
数式結果 (名)
‘=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,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))
  1. 名

最初の名前は、左 (E) の最初の文字で始まり、5 番目の文字 (最初のスペース) で終わります。 数式は、A2 の最初の 5 文字を左から抽出します。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 に含まれるスペースの位置を左から検索します。 (5)

  1. ミドル ネーム

ミドル ネームは 6 番目の文字位置 (S) から始まり、8 番目の位置 (2 番目のスペース) で終わります。 この数式では、SEARCH 関数を入れ子にして、スペースの 2 番目のインスタンスを検索します。

数式は、6 番目の位置から 3 文字を抽出します。

SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (5).

  1. 1 を追加して、最初のスペース (S) の後の文字の位置を取得します。 この数値の位置は、ミドル ネームの開始位置です。 (5 + 1 = 6)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (S) の後の文字の位置を取得します。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 4 で見つかった 6 番目の位置 (S) から始まる、A2 内のスペースの 2 番目のインスタンスを検索します。 この文字番号は、ミドル ネームの終了位置です。 (8)

  3. 左側の最初の文字から始まる、A2 のスペースの数値位置を検索します。 (5)

  4. 手順 5 で見つかった 2 番目のスペースの文字数を取り、手順 6 で見つかった最初のスペースの文字数を減算します。 結果は、手順 2 で見つかった 6 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (8 – 5 = 3)

  5. 姓

姓は右から 6 文字 (K) で始まり、右側 (n) の最初の文字で終わります。 この数式では、SEARCH 関数を入れ子にして、スペースの 2 番目と 3 番目のインスタンス (左側から 5 番目と 8 番目の位置) を検索します。

数式では、A2 で右側から 6 文字が抽出されます。

9. LEN 関数と入れ子になった SEARCH 関数を使用して、num_charsの値を検索します。

左側の最初の文字から始まる、A2 のスペースの数値位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (S) の後の文字の位置を取得します。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 2 で見つかった 6 番目の位置 (S) から始まる、A2 内のスペースの 2 番目のインスタンスを検索します。 この文字番号は、ミドル ネームの終了位置です。 (8)

  3. A2 のテキスト文字列の長さの合計をカウントし、手順 3 で見つかった領域の 2 番目のインスタンスまでの左から文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (14 – 8 = 6)。

__

例 3: Janaina B. G. ブエノ

2 つの中間イニシャルを抽出する方法の例を次に示します。 スペースの最初と 3 番目のインスタンスは、名前コンポーネントを区切ります。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
Janaina B. G. ブエノ2 つの中間イニシャル
数式結果 (名)
‘=LEFT(A2, SEARCH(" ",A2,1))=LEFT(A2, SEARCH(" ",A2,1))
数式結果 (中間イニシャル)
‘=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1))=MID(A2,SEARCH(" ",A2,1)+1,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))
  1. 名

最初の名前は、左 (J) の最初の文字で始まり、8 番目の文字 (最初のスペース) で終わります。 数式は、A2 の最初の 8 文字を左から抽出します。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (8)

  1. ミドル ネーム

ミドル ネームは 9 番目の位置 (B) から始まり、14 番目の位置 (3 番目のスペース) で終わります。 この数式では、SEARCH を入れ子にして、8 番目、11 番目、14 番目の位置のスペースの最初、2 番目、および 3 番目のインスタンスを検索します。

数式は、9 番目の位置から 5 文字を抽出します。

SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (8)

  1. 1 を追加して、最初のスペース (B) の後の文字の位置を取得します。 この数値の位置は、ミドル ネームの開始位置です。 (8 + 1 = 9)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (8)

  1. 1 を追加して、最初のスペース (B) の後の文字の位置を取得します。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字数です。 (8 + 1 = 9)

  2. 手順 4 で見つかった 9 番目の位置 (B) から始まる、A2 の 2 番目のスペースを検索します。 (11).

  3. 1 を追加して、2 番目のスペース (G) の後の文字の位置を取得します。 この文字番号は、3 番目のスペースの検索を開始する開始位置です。 (11 + 1 = 12)

  4. 手順 6 で見つかった 12 番目の位置から、A2 の 3 番目のスペースを検索します。 (14)

  5. A2 の最初のスペースの数値位置を検索します。 (8)

  6. 手順 7 で見つかった 3 番目のスペースの文字数を取り、手順 6 で見つかった最初のスペースの文字数を減算します。 結果は、手順 2 で見つかった 9 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。

  7. 姓

姓は右から 5 文字 (B) で始まり、右側の最初の文字 (o) で終わります。 この数式では、SEARCH を入れ子にして、スペースの最初、2 番目、および 3 番目のインスタンスを検索します。

数式では、完全な名前の右側から始まる、A2 で 5 文字が抽出されます。

入れ子になった SEARCH 関数と LEN 関数を使用して、num_charsの値を検索します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (8)

  1. 1 を追加して、最初のスペース (B) の後の文字の位置を取得します。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字数です。 (8 + 1 = 9)

  2. 手順 2 で見つかった 9 番目の位置 (B) から始まる、A2 の 2 番目のスペースを検索します。 (11)

  3. 1 を追加して、2 番目のスペース (G) の後の文字の位置を取得します。 この文字番号は、スペースの 3 番目のインスタンスの検索を開始する開始位置です。 (11 + 1 = 12)

  4. 手順 6 で見つかった 12 番目の位置 (G) から、A2 で 3 番目のスペースを検索します。 (14)

  5. A2 のテキスト文字列の長さの合計をカウントし、手順 5 で見つかった 3 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (19 – 14 = 5)

__

例 4: Kahn、Wendy Beth

この例では、名の前に姓があり、最後にミドル ネームがあります。 コンマは姓の末尾にマークを付け、スペースは各名前コンポーネントを区切ります。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
カーン、ウェンディ・ベス名の先頭にコンマを付けます
数式結果 (名)
‘=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1))=MID(A2,SEARCH(" ",A2,1)+1,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)
  1. 名

名は、左から 7 文字目 (W) から始まり、12 文字目 (2 つ目のスペース) で終わります。 名は氏名の中央にあるため、MID 関数を使って抽出する必要があります。

数式では、7 番目の位置から 6 文字が抽出されます。

SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (W) の後の文字の位置を取得します。 この数値の位置は、名の開始位置です。 (6 + 1 = 7)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (W) の後の文字の位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (6 + 1 = 7)

手順 4 で見つかった 7 番目の位置 (W) から開始して、A2 の 2 番目のスペースを検索します。 (12)

  1. 左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (6)

  2. 手順 5 で見つかった 2 番目のスペースの文字数を取り、手順 6 で見つかった最初のスペースの文字数を減算します。 結果は、手順 2 で見つかった 7 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (12 – 6 = 6)

  3. ミドル ネーム

ミドル ネームは、右から 4 文字目 (B) から始まり、右端の文字 (h) で終わります。 次の式では、ネストされた SEARCH 関数を使って、左から 6 文字目にある最初のスペースと左から 12 文字目にある 2 つ目のスペースを取得します。

数式では、右側から 4 文字が抽出されます。

ネストされた SEARCH 関数と LEN 関数を使って、"開始位置" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (W) の後の文字の位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (6 + 1 = 7)

  2. 手順 2 で見つかった 7 番目の位置 (W) から始まる A2 のスペースの 2 番目のインスタンスを検索します。 (12)

  3. A2 のテキスト文字列の長さの合計をカウントし、手順 3 で見つかった左から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (16 – 12 = 4)

  4. 姓

姓は、左端の文字 (K) から始まり、4 文字目 (n) で終わります。 次の式は、左から 4 文字を抽出します。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (6)

  1. 2 を減算して、姓 (n) の終了文字の数値位置を取得します。 結果は、LEFT で抽出する文字数です。 (6 – 2 =4)

__

例 5: Mary Kay D. Andersen

この例では、2 部構成の名である Mary Kay を使用します。 2 番目と 3 番目のスペースは、各名前コンポーネントを区切ります。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、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,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-(SEARCH(" ",A2,1)+1)+1)-(SEARCH(" ",A2,1)+1)+1))=MID(A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-(SEARCH(" ",A2,1)+1)+1)-(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))
  1. 名

最初の名前は、左側の最初の文字で始まり、9 番目の文字 (2 番目のスペース) で終わります。 この数式では、SEARCH を入れ子にして、左側から 2 番目のスペース インスタンスを見つけます。

数式では、左から 9 文字が抽出されます。

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (K) の後の文字の位置を取得します。 結果は、スペースの 2 番目のインスタンスの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 2 で見つかった 6 番目の位置 (K) から始まる、A2 内のスペースの 2 番目のインスタンスを検索します。 結果は、テキスト文字列から LEFT が抽出した文字数です。 (9)

  3. ミドル ネーム

ミドル ネームは 10 番目の位置 (D) から始まり、12 番目の位置 (3 番目のスペース) で終わります。 この数式では、SEARCH を入れ子にして、スペースの最初、2 番目、および 3 番目のインスタンスを検索します。

数式は、10 番目の位置から 2 文字を中央から抽出します。

入れ子になった SEARCH 関数を使用して、start_numの値を検索します。

左側の最初の文字から始まる、A2 の最初のスペースの数値位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (K) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 2 で見つかった 6 番目の位置 (K) から始まる、A2 内の空間の 2 番目のインスタンスの位置を検索します。 結果は、左から左から抽出された文字数です。 (9)

  3. 1 を追加して、2 番目のスペース (D) の後の文字を取得します。 結果は、ミドル ネームの開始位置です。 (9 + 1 = 10)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

2 番目のスペース (D) の後の文字の数値位置を検索します。 結果は、3 番目のスペースの検索を開始する文字数です。 (10)

  1. A2 の 3 番目のスペースの数値位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (12)

  2. 2 番目のスペース (D) の後の文字の数値位置を検索します。 結果は、ミドル ネームの先頭位置です。 (10)

  3. 手順 6 で見つかった 3 番目のスペースの文字数を取得し、手順 7 で見つかった "D" の文字数を減算します。 結果は、手順 4 で見つかった 10 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (12 – 10 = 2)

  4. 姓

姓は右側から 8 文字で始まります。 この数式では、SEARCH を入れ子にして、5 番目、9 番目、12 番目の位置のスペースの最初、2 番目、および 3 番目のインスタンスを検索します。

数式では、右側から 8 文字が抽出されます。

入れ子になった SEARCH 関数と LEN 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (K) の後の文字を取得します。 結果は、スペースの検索を開始する文字番号です。 (5 + 1 = 6)

  2. 手順 2 で見つかった 6 番目の位置 (K) から開始して、A2 の 2 番目のスペースを検索します。 (9)

  3. 1 を追加して、2 番目のスペース (D) の後の文字の位置を取得します。 結果は、ミドル ネームの開始位置です。 (9 + 1 = 10)

  4. A2 の 3 番目のスペースの数値位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (12)

  5. A2 のテキスト文字列の長さの合計をカウントし、手順 5 で見つかった 3 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (20 – 12 = 8)

__

例 6: Paula Barreto de Mattos

この例では、3 部構成の姓を使用します。Barreto de Mattos。 最初のスペースは、名の末尾と姓の先頭を示します。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
Paula Barreto de Mattos3 部構成の姓
数式結果 (名)
‘=LEFT(A2, SEARCH(" ",A2,1))=LEFT(A2, SEARCH(" ",A2,1))
数式結果 (姓)
RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
  1. 名

最初の名前は、左 (P) の最初の文字で始まり、6 番目の文字 (最初のスペース) で終わります。 数式では、左側から 6 文字が抽出されます。

Search 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 姓

姓は右から 17 文字 (B) で始まり、右側の最初の文字で終わります。 数式では、右側から 17 文字が抽出されます。

LEN 関数と SEARCH 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. A2 のテキスト文字列の長さの合計をカウントし、手順 1 で見つかった左から最初のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (23 – 6 = 17)

__

例 7: James van Eaton

この例では、2 部構成の姓 van Eaton を使用します。 最初のスペースは、名の末尾と姓の先頭を示します。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
James van Eaton2 部構成の姓
数式結果 (名)
‘=LEFT(A2, SEARCH(" ",A2,1))=LEFT(A2, SEARCH(" ",A2,1))
数式結果 (姓)
‘=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))=RIGHT(A2,LEN(A2)-SEARCH(" ",A2,1))
  1. 名

最初の名前は、左 (J) の最初の文字で始まり、8 番目の文字 (最初のスペース) で終わります。 数式では、左側から 6 文字が抽出されます。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 姓

姓は右から 9 文字目 (v) で始まり、右側 (n) の最初の文字で終わります。 数式は、フル ネームの右側から 9 文字を抽出します。

LEN 関数と SEARCH 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. A2 のテキスト文字列の長さの合計をカウントし、手順 1 で見つかった左から最初のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (15 – 6 = 9)

__

例 8: ベーコン Jr.、Dan K。

この例では、姓の先頭にサフィックスが続きます。 コンマは、姓とサフィックスを名と中間のイニシャルから区切ります。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
ベーコン Jr.、Dan K.姓とサフィックスの先頭にコンマを付けます
数式結果 (名)
‘=MID(A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1)+1)-SEARCH(" ",A2,1)+1))=MID(A2,SEARCH(" ",A2,1)+1)+1,SEARCH(" ",A2,SEARCH(" ",A2,1)+1)+1)-SEARCH(" ",A2,1)+1)-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))
  1. 名

最初の名前は 12 番目の文字 (D) で始まり、15 番目の文字 (3 番目のスペース) で終わります。 数式は、12 番目の位置から 3 文字を抽出します。

入れ子になった SEARCH 関数を使用して、start_numの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (J) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (6 + 1 = 7)

  2. 手順 2 で見つかった 7 番目の位置 (J) から始まる A2 の 2 番目のスペースを検索します。 (11)

  3. 1 を追加して、2 番目のスペース (D) の後の文字を取得します。 結果は、名の開始位置です。 (11 + 1 = 12)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

2 番目のスペース (D) の後の文字の数値位置を検索します。 結果は、3 番目のスペースの検索を開始する文字数です。 (12)

  1. A2 の 3 番目のスペースの数値位置を左から検索します。 結果は、名の終了位置です。 (15)

  2. 2 番目のスペース (D) の後の文字の数値位置を検索します。 結果は、名の先頭位置になります。 (12)

  3. 手順 6 で見つかった 3 番目のスペースの文字数を取得し、手順 7 で見つかった "D" の文字数を減算します。 結果は、手順 4 で見つかった 12 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (15 – 12 = 3)

  4. ミドル ネーム

ミドル ネームは、右側の 2 番目の文字 (K) で始まります。 数式は右側から 2 文字を抽出します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (J) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (6 + 1 = 7)

  2. 手順 2 で見つかった 7 番目の位置 (J) から始まる A2 の 2 番目のスペースを検索します。 (11)

  3. 1 を追加して、2 番目のスペース (D) の後の文字を取得します。 結果は、名の開始位置です。 (11 + 1 = 12)

  4. A2 の 3 番目のスペースの数値位置を左から検索します。 結果は、ミドル ネームの終了位置です。 (15)

  5. A2 のテキスト文字列の長さの合計をカウントし、手順 5 で見つかった左から 3 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (17 – 15 = 2)

  6. 姓

姓は左 (B) の最初の文字から始まり、6 文字目 (最初のスペース) で終わります。 そのため、数式は左から 6 文字を抽出します。

SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. サフィックス

サフィックスは、左から 7 文字目 (J) で始まり、左 (.) から 9 文字目で終わります。 数式は、7 文字目から 3 文字を抽出します。

SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (J) の後の文字を取得します。 結果はサフィックスの開始位置です。 (6 + 1 = 7)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 1 を追加して、最初のスペース (J) の後の文字の数値位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (7)

  2. 手順 4 で見つかった 7 文字目から始まる、A2 の 2 番目のスペースの数値位置を検索します。 (11)

  3. 手順 4 で見つかった 2 番目のスペースの文字数から 1 を減算して、"," の文字番号を取得します。 結果はサフィックスの終了位置です。 (11 – 1 = 10)

  4. 最初のスペースの数値位置を検索します。 (6)

  5. 最初のスペースを見つけたら、1 を追加して次の文字 (J) を見つけます。手順 3 と 4 でも見つかります。 (7)

  6. 手順 6 で見つかった "," の文字数を取り、手順 3 と 4 で見つかった "J" の文字数を減算します。 結果は、手順 2 で見つかった 7 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (10 – 7 = 3)

__

例 9: Gary Altman III

この例では、名は文字列の先頭にあり、サフィックスは末尾にあります。例 2 のような数式を使用できます。LEFT 関数を使用して名を抽出し、MID 関数を使用して姓を抽出し、RIGHT 関数を使用してサフィックスを抽出します。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
Gary Altman IIIサフィックスを持つ姓と名
数式結果 (名)
‘=LEFT(A2, SEARCH(" ",A2,1))=LEFT(A2, SEARCH(" ",A2,1))
数式結果 (姓)
‘=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))=MID(A2,SEARCH(" ",A2,1)+1,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))
  1. 名

最初の名前は、左 (G) の最初の文字から始まり、5 番目の文字 (最初のスペース) で終わります。 したがって、数式はフル ネームの左側から 5 文字を抽出します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (5)

  1. 姓

姓は、左 (A) の 6 文字目から始まり、11 文字目 (2 番目のスペース) で終わります。 この数式では、SEARCH を入れ子にしてスペースの位置を検索します。

数式は、6 番目の文字から始まる中央から 6 文字を抽出します。

SEARCH 関数を使って、"開始位置" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (A) の後の文字の位置を取得します。 結果は、姓の開始位置です。 (5 + 1 = 6)

ネストされた SEARCH 関数を使って、"文字数" 引数に渡す値を取得します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (A) の後の文字の位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 4 で見つかった 6 番目の文字から始まる、A2 の 2 番目のスペースの数値位置を検索します。 この文字番号は、姓の終了位置です。 (12)

  3. 最初のスペースの数値位置を検索します。 (5)

  4. 1 を追加して、最初のスペース (A) の後の文字の数値位置を検索します。手順 3 と 4 でも見つかります。 (6)

  5. 手順 5 で見つかった 2 番目のスペースの文字番号を取り、手順 6 と 7 で見つかった "A" の文字数を減算します。 結果は、手順 2 で見つかった 6 番目の位置から始まるテキスト文字列から MID が抽出した文字数です。 (12 – 6 = 6)

  6. サフィックス

サフィックスは、右側から 3 文字で始まります。 この数式では、SEARCH を入れ子にしてスペースの位置を検索します。

入れ子になった SEARCH 関数と LEN 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (5)

  1. 1 を追加して、最初のスペース (A) の後の文字を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (5 + 1 = 6)

  2. 手順 2 で見つかった、6 番目の位置 (A) から始まる A2 の 2 番目のスペースを検索します。 (12)

  3. A2 のテキスト文字列の合計長をカウントし、手順 3 で見つかった左から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (15 – 12 = 3)

__

例 10: Ryan Ihrig 氏

この例では、完全な名前の前にプレフィックスが付き、例 2 のような数式を使用します。MID 関数を使用して名を抽出し、RIGHT 関数を使用して姓を抽出します。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、Excel は右側の数式を適切な結果に自動的に変換します。

ヒント ワークシートにデータを貼り付ける前に、列 A と B の列幅を 250 に設定します。

名前の例説明
Ryan Ihrig 氏プレフィックス付き
数式結果 (名)
‘=MID(A2,SEARCH(" ",A2,1)+1,SEARCH(" ",A2,1)+1)-(SEARCH(" ",A2,1)+1))=MID(A2,SEARCH(" ",A2,1)+1,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))
  1. 名

最初の名前は、左 (R) の 5 文字目から始まり、9 番目の文字 (2 番目のスペース) で終わります。 数式は SEARCH を入れ子にして、スペースの位置を検索します。 5 番目の位置から 4 文字を抽出します。

SEARCH 関数を使用して、start_numの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (4)

  1. 1 を追加して、最初のスペース (R) の後の文字の位置を取得します。 結果は、名の開始位置です。 (4 + 1 = 5)

入れ子になった SEARCH 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (4)

  1. 1 を追加して、最初のスペース (R) の後の文字の位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (4 + 1 = 5)

  2. 手順 3 と 4 の 5 番目の文字から始まる、A2 の 2 番目のスペースの数値位置を検索します。 この文字番号は、名の終了位置です。 (9)

  3. 最初のスペースを検索します。 (4)

  4. 1 を追加して、最初のスペース (R) の後の文字の数値位置を検索します。手順 3 と 4 でも見つかります。 (5)

  5. 手順 5 で見つかった 2 番目のスペースの文字数を取得し、手順 6 と 7 で見つかった "R" の文字数を減算します。 結果は、手順 2 で見つかった 5 番目の位置から始まる、テキスト文字列から MID が抽出した文字数です。 (9 – 5 = 4)

  6. 姓

姓は右側から 5 文字で始まります。 この数式では、SEARCH を入れ子にしてスペースの位置を検索します。

入れ子になった SEARCH 関数と LEN 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (4)

  1. 1 を追加して、最初のスペース (R) の後の文字の位置を取得します。 結果は、2 番目のスペースの検索を開始する文字数です。 (4 + 1 = 5)

  2. 手順 2 で見つかった、5 番目の位置 (R) から始まる A2 の 2 番目のスペースを検索します。 (9)

  3. A2 のテキスト文字列の合計長をカウントし、手順 3 で見つかった左から 2 番目のスペースまでの文字数を減算します。 結果は、フル ネームの右側から抽出される文字数です。 (14 – 9 = 5)

__

例 11: Julie Taft-Rider

この例では、ハイフネーションされた姓を使用します。 各名前コンポーネントはスペースで区切られます。

テーブル内のセルをコピーし、セル A1 の Excel ワークシートに貼り付けます。 左側に表示される数式は参照用に表示され、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))
  1. 名

最初の名前は、左から最初の文字から始まり、6 番目の位置 (最初のスペース) で終わります。 数式では、左側から 6 文字が抽出されます。

SEARCH 関数を使用して、num_charsの値を検索します。

A2 の最初のスペースの左側から始まる数値の位置を検索します。 (6)

  1. 姓

姓全体が右から 10 文字 (T) で始まり、右側 (r) の最初の文字で終わります。

LEN 関数と SEARCH 関数を使用して、num_charsの値を検索します。

左側の最初の文字から始まる、A2 内のスペースの数値位置を検索します。 (6)

  1. 抽出するテキスト文字列の長さの合計をカウントし、手順 1 で見つかった左から最初のスペースまでの文字数を減算します。 (16 – 6 = 10)

target=”_blank” title=”区切り位置指定ウィザードを使用して、テキストをさまざまな列に分割する” rel=”noopener”>区切り位置指定ウィザードを使用して、テキストをさまざまな列に分割する

上部へスクロール