【保存版】Excelでセルを2つに分割する5つの便利な方法

データベースやその他のソースからデータをインポートすると、1つのセルを2つ以上の列に分割しなければならない場面によく出くわします。

この記事では、実際の業務例を交えながら、Excelで1つのセルを2つに分割できる効果的な5つの方法をわかりやすく解説します。

Excelで1つのセルを2つに分割する基本の手順

ここでは、B列にフルネームが入力されたサンプルデータを使用します。このB列のセルを「名(ファーストネーム)」と「姓(ラストネーム)」の2つの列に分割していきましょう。以下の方法で実現できます。

方法1:「区切り位置」機能を使ってセルを分割する

区切り位置(Text to Columns)は、Excelに標準搭載されている便利な機能で、区切り文字を目印に、1つのセルまたは列内のテキストを複数の列へ分割できます。

区切り文字とは、カンマ、スペース、セミコロンなど、テキスト文字列やデータを区切る役割を持つ文字のことです。

今回のデータでは、スペースが区切り文字に該当します。

手順:

• 対象データ全体(例：B4:B11)を選択します。
• データタブから「区切り位置」を選択します。
• 「コンマやタブなどの区切り文字によってフィールドごとに区切られたデータ」を選びます。
• 「次へ」をクリックします。

• 区切り文字として「スペース」にチェックを入れます。
• 「次へ」をクリックします。

• 列のデータ形式で「文字列」を選択します。
• 必要に応じて表示先(貼り付け先)を調整し、「完了」をクリックします。

すると、以下のように氏名が「名」と「姓」に綺麗に分割されます。

方法2:フラッシュフィルを使ってセルを分割する

フラッシュフィル(Flash Fill)は、データ内のパターンを自動的に認識し、残りの値を補完してくれるExcelの特別なツールです。Microsoft Excel 2013以降のバージョンで利用可能です。

内部的には機械学習の手法が使われており、データパターンの評価・学習を行い、そのパターンに基づいてセルを自動入力します。

この機能を使えば、氏名を「名」と「姓」に簡単に分割できます。

手順:

• 空白セル(例:C5)を選択します。
• B5セルの「名(William)」をC5セルに入力します。
• セルの右下隅(フィルハンドル)をダブルクリックすると、範囲全体が自動的に入力されます。ほかにも、カーソルを最終行までドラッグする方法や、データ>データツール>フラッシュフィルから直接選択する方法もあります。

• 「フラッシュフィル」オプションを選択します。

すると、ツールが自動的に全セル範囲に「名」を入力してくれます。

同じ要領で「姓」も抽出できます。この場合、B5セルの「姓」をD5セルに入力し、同じ手順を繰り返すだけです。以下のような結果が得られます。

方法3:関数(数式)を使ってセルを分割する

i. 区切り文字でセルを分割する

Excelの関数を組み合わせれば、区切り文字を目印に「名」と「姓」を取り出すことも可能です。今回のデータでは、氏名の間にあるスペースが区切り文字になります。

ここで使用する主な関数を確認しておきましょう。

LEFT関数:文字列の左端から指定した数の文字を抽出します。
構文:=LEFT(文字列, [文字数])

• 文字列:抽出元となるテキスト
• 文字数:[省略可] 左端から抽出する文字数

RIGHT関数:文字列の右端から指定した数の文字を抽出します。
構文:=RIGHT(文字列, [文字数])

• 文字列:抽出元となるテキスト
• 文字数:[省略可] 右端から抽出する文字数

SEARCH関数:ある文字列が別の文字列の中で何文字目に位置するかを返します。
構文:=SEARCH(検索文字列, 対象, [開始位置])

• 検索文字列:探したいテキスト
• 対象:検索対象となるテキスト
• 開始位置:[省略可] 検索を開始する位置

FIND関数:ある文字列が別の文字列の中で何番目に位置するかを数値で返します。
構文:=FIND(検索文字列, 対象, [開始位置])

LEN関数:文字列の長さ(文字数)を返します。
構文:=LEN(文字列)

SUBSTITUTE関数:文字列内の指定したテキストを別のテキストに置き換えます。
構文:=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

それでは、LEFT関数とSEARCH関数を組み合わせて「名」を取り出してみましょう。

空白セル(C3など)を選択し、次の数式を入力します。B5は分割したいセルです。

=LEFT(B5,SEARCH(" ",B5))

Enterキーを押したら、フィルハンドルを使って列全体に数式をコピーします。

同様に、「姓」を取り出すには次の数式を使用します。B5は分割対象のテキスト、スペース(" ")が区切り文字です。

=RIGHT(B5,LEN(B5)-FIND("*",SUBSTITUTE(B5," ","*",LEN(B5)-LEN(SUBSTITUTE(B5," ","")))))

ii. 改行コードでセルを分割する

この方法も基本的には前述の方法と同じですが、CHAR関数を使用する点が異なります。

CHAR関数は、コード番号(ASCIIコード)に対応する文字を返す関数です。
構文:=CHAR(数値)

• 数値:1〜255の間の数値

「名」を取り出すには、空白セルに次の数式を入力し、フィルハンドルで他のセルにも適用します。B5は対象テキスト、10は改行(LF)のASCIIコードです。

=LEFT(B5,SEARCH(CHAR(10),B5,1)-1)

※1を引いているのは、区切り文字(スペース)自体を抽出しないためです。

同様に、「姓」を取り出すには次の数式を入力します。

=RIGHT(B5,LEN(B5)-SEARCH(CHAR(10),B5,SEARCH(CHAR(10),B5)))

Enterキーを押した後、フィルハンドルで同じ列の他のセルにも適用しましょう。

方法4:「文字+数値」パターンのセルを分割する

セルの中身が「文字+数値」の組み合わせになっている場合もあります。そんなときは、以下の手順とSUM関数などを活用しましょう。

SUM関数は、指定した値(範囲、配列、数値など)の合計を返します。
構文:=SUM(数値1, [数値2], [数値3], …)

このタイプのセルを分割する最も簡単な方法は、まず数値部分を取り出し、その後に文字部分を求めるという流れです。

まず空白セルに次の数式を入力して数値を抽出します。B5は分割したいセルです。

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

Enterキーを押したら、フィルハンドルで他のセルにも適用します。

続いて文字部分を求めます。空白セルに次の数式を入力してください。B5は分割対象のセル、D5は前の数式で抽出した数値です。

=LEFT(B5,LEN(B5)-LEN(D5))

方法5:Power Queryを使ってセルを分割する

Power Queryを使っても、氏名を「名」と「姓」に分割できます。Power Queryは、さまざまなソースからデータを取り込み、必要に応じてクリーニング・変換・整形を行える無料のMicrosoftアドインです。

手順:

• セル範囲B4:B11全体を選択します。
• データタブの「テーブルから」(From Table)をクリックします。
• OKを押します。

Power Query エディターが起動したら、ホーム>列の分割>区切り記号によるを選択します。

次に、下図のような画面が表示されるので、区切り文字として「スペース」を選択し、OKを押します。

これで分割処理は完了ですが、結果を既存のワークシートに読み込む必要があります。ホーム>閉じて読み込む>閉じて読み込む…を選択しましょう。

保存先を選択し、「読み込み」をクリックします。

最終的に、以下のように出力されます。

操作時の注意点

数式を数式バーに入力する際は、入力ミスがないか十分に確認しましょう。

また、ファイル名、ファイルの保存場所、Excelファイルの拡張子にも注意してください。

まとめ

今回は、Excelでセルを分割するための最も効果的で使いやすい5つの方法をご紹介しました。お手持ちのデータの内容や目的に合わせて、最適な方法を選んでみてください。ご意見やご不明な点があれば、ぜひお気軽にお知らせください。

最後までお読みいただき、ありがとうございました。

関連記事

• Excelで1つのセルを半分に分割する方法(斜め・水平)
• Excelの数式でカンマ区切りの文字列を分割する(5つの例)
• Excel VBA:文字単位で文字列を分割する(6つの実用例)
• Excelで1つのセル内に2行入力する方法(4つの方法)
• Excel VBA:文字列をセルに分割する(4つの便利な活用例)
• Excel VBA:文字数を指定して文字列を分割する(2つの簡単な方法)