Excelで文字列を分割する8つの実証済みテクニック|フラッシュフィルから関数活用まで完全解説

本記事では、Excelで文字列を分割するための8つの方法を、実際の手順とともに詳しく解説します。初心者でもすぐに使える機能から、関数を組み合わせた応用テクニックまで幅広くカバーしています。

ここでは、学生の記録データをサンプルとして使用します。

方法1:フラッシュフィルで文字数ごとに文字列を分割する

学生IDには、大学名・年度・セクション・ロール番号が含まれています。

学生IDを「先頭3文字」「中間4文字」「末尾3文字」に分割して、それぞれのデータを抽出します。

ステップ1

• 出力先のセルC5を選択します。
• 先頭の3文字を入力します。

ステップ2

• Enterキーを押します。
• 次のセルC6を選択し、「データ」タブ→「データツール」→「フラッシュフィル」をクリックします。

結果:
大学名列に大学名が自動的に抽出されます。

ステップ3

• 出力先のセルD5を選択します。
• 中間の4文字を入力します。

• ステップ2と同じ操作を行います。

結果:
年度列に年度が抽出されます。

ステップ4

• 出力先のセルE5を選択します。
• 末尾の3文字を入力します。

• ステップ2と同じ操作を行います。

結果:
各学生のセクションとロール番号がセクション・ロール番号列に抽出されます。

方法2:「区切り位置」機能を使う

学生IDを分割して、大学名・年度・セクション・ロール番号を取得します。

ステップ1

• 学生ID列を選択します。
• 「データ」タブ→「データツール」→「区切り位置」をクリックします。

「テキストファイルウィザード」が表示されたら:

• 「固定長フィールド」を選択します。
• 「次へ」をクリックします。

• 下図の位置をクリックして区切り線を作成します(ここでは大学名の後と年度の後に設定)。
• 「次へ」を選択します。

• 列のデータ形式で「標準」を選択します。
• 表示先に出力セル$C$5を入力します。
• 「完了」をクリックします。

結果:
大学名・年度・セクション・ロール番号の3列に分割されたデータが表示されます。

方法3:補助データ(MID関数)を活用する

科目番号を3文字ずつに分割して、科目名と科目IDを取得します。

ステップ1

• 出力先のセルD6を選択します。
• 以下の数式を入力します。

=MID($C6,COLUMN()+D$4-COLUMN($D6)+1,3)

数式の解説

• COLUMN()+D$4-COLUMN($D6)+1:抽出開始位置を返します。
• COLUMN($D6):このセルの列番号を返します。
• 3:抽出する文字数です。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
科目名列に科目名が表示されます。

ステップ2

• 出力先のセルD6(隣接列)を選択します。
• 以下の数式を入力します。

=MID($C6,COLUMN()+E$4-COLUMN($E6)+3,3)

数式の解説

• COLUMN()+E$4-COLUMN($E6)+3:抽出開始位置を返します。
• COLUMN($E6):このセルの列番号を返します。
• 3:抽出する文字数です。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
科目ID列に科目IDが表示されます。

方法4:LEFT関数とRIGHT関数を使う

科目番号を3文字ずつ分割することで、科目名と科目IDを分離できます。

ステップ1

• 出力先のセルC5を選択します。
• 以下の数式を入力します。

C5:対象のテキスト
3:抽出する文字数

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

科目名列に科目名が表示されます。

ステップ2

• 出力先のセルE5を選択します。
• 以下の数式を入力します。

C5:対象のテキスト
3:抽出する文字数

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

科目ID列に科目IDが表示されます。

方法5:FIND関数で特殊文字を基準に文字列を分割する

メールアドレスを特殊文字「@」と「.」を基準に分割します。

ステップ1(@より前の部分を抽出)

• 出力先のセルC5を選択します。
• 以下の数式を入力します。

B5:対象のテキスト
FIND("@",B5)-1:抽出する文字数
FIND関数は「@」の位置を返し、そこから1を引いた値が文字数になります。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
メールアドレスの最初の部分(ユーザー名)が取得できます。

ステップ2(@と.の間の部分を抽出)

• 出力先のセルD5を選択します。
• 以下の数式を入力します。

=MID(B5,FIND("@",B5),FIND(".",B5,FIND("@",B5)+1)-FIND("@",B5))

B5:対象のテキスト
FIND("@", B5):抽出開始位置(「@」の位置)
FIND(".",B5,FIND("@",B5)+1)-FIND("@",B5):抽出する文字数(「.」の位置と「@」の位置の差)

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
メールアドレスの中間部分(ドメイン名)が表示されます。

ステップ3(.以降の部分を抽出)

• 出力先のセルE5を選択します。
• 以下の数式を入力します。

=RIGHT(B5,LEN(B5)-FIND(".",B5,FIND("@",B5))+1)

B5:対象のテキスト
LEN(B5)-FIND(".",B5,FIND("@",B5))+1:抽出する文字数
LEN関数で全体の文字数を取得し、「.」の位置を引くことで、「.」以降の文字数が求まります。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
メールアドレスの最後の部分(TLDなど)が取得できます。

方法6:SEARCH関数で特殊文字を基準に文字列を分割する

メールアドレスを特殊文字「@」と「.」を基準に分割します。SEARCH関数は大文字小文字を区別しない点がFIND関数との主な違いです。

ステップ1(@より前の部分を抽出)

• 出力先のセルC5を選択します。
• 以下の数式を入力します。

=LEFT(B5,SEARCH("@",B5)-1)

数式の解説

• B5:対象のテキスト
• SEARCH("@",B5)-1:抽出する文字数
• SEARCH関数は「@」の位置を返し、そこから1を引きます。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
メールアドレスの最初の部分が取得できます。

ステップ2(@と.の間の部分を抽出)

• 出力先のセルD5を選択します。
• 以下の数式を入力します。

=MID(B5,SEARCH("@",B5),SEARCH(".",B5,SEARCH("@",B5)+1)-SEARCH("@",B5))

数式の解説

• B5:対象のテキスト
• SEARCH("@", B5):抽出開始位置(「@」の位置)
• SEARCH(".",B5,SEARCH("@",B5)+1)-SEARCH("@",B5):抽出する文字数(「.」の位置と「@」の位置の差)

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

メールアドレスの中間部分が取得できます。

ステップ3(.以降の部分を抽出)

• 出力先のセルE5を選択します。
• 以下の数式を入力します。

=RIGHT(B5,LEN(B5)-SEARCH(".",B5,SEARCH("@",B5))+1)

B5:対象のテキスト
LEN(B5)-SEARCH(".",B5,SEARCH("@",B5))+1:抽出する文字数
LEN関数で全体の文字数を取得し、「.」の位置を引くことで、「.」以降の文字数が求まります。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

メールアドレスの最後の部分が取得できます。

方法7:改行を基準に文字列を分割する

学生IDと学生名が同じセル内で改行によって区切られている場合、改行を基準に文字列を分割してそれぞれを抽出します。

ステップ1(学生IDを抽出)

• 出力先のセルC5を選択します。
• 以下の数式を入力します。

=LEFT(B5,FIND(CHAR(10),B5))

B5:対象のテキスト
FIND(CHAR(10), B5):抽出する文字数
CHAR(10)は改行コードを表し、FIND関数で改行の位置を特定します。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

結果:
学生ID列に学生IDが表示されます。

ステップ2(学生名を抽出)

• 出力先のセルE5を選択します。
• 以下の数式を入力します。

=RIGHT(B5,LEN(B5)-FIND(CHAR(10),B5)+1)

B5:対象のテキスト
LEN(B5)-FIND(CHAR(10), B5)+1:抽出する文字数
LEN関数で全体の文字数を取得し、改行の位置を引くことで、改行以降の文字数が求まります。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

学生名列に学生名が表示されます。

方法8:数値と文字列が混在する文字列を分割する

点数と評価が同じセルに表示されている場合の分割方法です。

ステップ1(点数を抽出)

• 出力先のセルD5を選択します。
• 以下の数式を入力します。

=LEFT(C5, SUM(LEN(C5) - LEN(SUBSTITUTE(C5, {"0","1","2","3","4","5","6","7","8","9"}, ""))))

C5:対象のテキスト
SUBSTITUTE(C5, {"0","1","2","3","4","5","6","7","8","9"}, ""):すべての数字を空白に置き換え、LEN関数で数値以外の文字数を返します。
元の文字数から差し引くことで、数字の個数=抽出すべき文字数が求まります。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグします。

点数列に点数が表示されます。

ステップ2(評価を抽出)

• 出力先のセルE5を選択します。
• 以下の数式を入力します。

=RIGHT(C5,LEN(C5)-LEN(D5)-1)

C5:対象のテキスト
LEN(C5)-LEN(D5)-1:抽出する文字数を計算します。

• Enterキーを押します。
• フィルハンドルを下方向にドラッグすると、評価列に評価が表示されます。

練習セクション

ここで実際に練習してみましょう。

練習用ワークブックのダウンロードはこちら

関連記事

• Excelでテキストを複数行に分割する方法
• Excelで特定の単語の後のテキストを分割する方法
• Excelで数式を使ってスペースでテキストを分割する方法
• Excelで姓と名を分ける方法

<< テキスト分割に戻る | Excelでの分割 | Excelを学ぶ

無料の高度なExcel演習問題(解答付き)を受け取ろう!