このチュートリアルでは、数式とテキストの分割機能を使用してExcelでセルを分割する方法について説明します。 テキストをコンマ、スペース、またはその他の区切り文字で区切る方法と、文字列をテキストと数値に分割する方法を学びます。
テキストを1つのセルから複数のセルに分割することは、すべてのExcelユーザーが時々処理するタスクです。 以前の記事の1つで、テキストから列への機能、Flash FillおよびSplit Namesアドインを使用してExcelでセルを分割する方法について説明しました。 今日は、数式とテキストの分割機能を使用して文字列を分割する方法を詳しく見ていきます。
数式を使用してExcelでテキストを分割する方法
Excelで文字列を分割するには、通常、LEFT、RIGHT、またはMID関数をFINDまたはSEARCHと組み合わせて使用します。 一見すると、いくつかの式は複雑に見えるかもしれませんが、ロジックは実際には非常に単純であり、次の例はいくつかの手がかりを与えます。
文字列をコンマ、コロン、スラッシュ、ダッシュ、またはその他の区切り文字で分割します
Excelでセルを分割する場合、重要なのは、テキスト文字列内の区切り文字の位置を見つけることです。 タスクに応じて、これは大文字と小文字を区別しないSEARCHまたは大文字と小文字を区別するFINDを使用して実行できます。 区切り文字の位置を取得したら、RIGHT、LEFT、またはMID関数を使用して、テキスト文字列の対応する部分を抽出します。 よりよく理解するために、次の例を考えてみましょう。
Item-Color-SizeパターンのSKUのリストがあり、列を3つの別々の列に分割するとします。
アイテム名（最初のハイフンの前のすべての文字）を抽出するには、B2に次の数式を挿入し、それを列にコピーします。
= LEFT（A2、SEARCH（ “-“、A2,1）-1）
この式では、SEARCHは文字列内の最初のハイフン（ “-“）の位置を決定し、LEFT関数はそれに残っているすべての文字を抽出します（ハイフンを抽出しないため、ハイフンの位置から1を減算します） 自体）。
色（1番目と2番目のハイフンの間のすべての文字）を抽出するには、C2に次の数式を入力し、それを他のセルにコピーします。
= MID（A2、SEARCH（ “-“、A2）+ 1、SEARCH（ “-“、A2、SEARCH（ “-“、A2）+1）-SEARCH（ “-“、A2）-1）
ご存知のとおり、Excel MID関数の構文は次のとおりです。
MID(text, start_num, num_chars)
どこ：
text-テキストを抽出する場所。
start_num-抽出する最初の文字の位置。
num_chars-抽出する文字数。
上記の数式では、テキストはセルA2から抽出され、他の2つの引数は4つの異なるSEARCH関数を使用して計算されます。
開始番号（start_num）は、最初のハイフン+1の位置です。
SEARCH（ “-“、A2）+ 1
抽出する文字数（num_chars）：2番目のハイフンと1番目のハイフンの位置の差から1を引いた値。
SEARCH（ “-“、A2、SEARCH（ “-“、A2）+1）-SEARCH（ “-“、A2）-1
サイズ（3番目のハイフン以降のすべての文字）を抽出するには、D2に次の式を入力します。
= RIGHT（A2、LEN（A2）-SEARCH（ “-“、A2、SEARCH（ “-“、A2）+ 1））
この式では、LEN関数は文字列の全長を返し、そこから2番目のハイフンの位置を減算します。 違いは、2番目のハイフンの後の文字数であり、RIGHT関数はそれらを抽出します。
同様に、列を他の文字で分割できます。 必要なのは、「-」を必要な区切り文字に置き換えることだけです。たとえば、スペース（ “”）、スラッシュ（ “/”）、コロン（ “;”）、セミコロン（ “;”）などです。
ヒント。 上記の式で、+ 1と-1は区切り文字の文字数に対応します。 この例では、ハイフン（1文字）です。 区切り文字が2文字で構成されている場合。 カンマとスペースを使用し、カンマ（ “、”）のみをSEARCH関数に指定し、+ 1と-1の代わりに+2と-2を使用します。
Excelで改行によって文字列を分割する方法
テキストをスペースで分割するには、前の例で示したものと同様の数式を使用します。 唯一の違いは、式に直接入力できないため、改行文字を指定するにはCHAR関数が必要になることです。
分割するセルが次のようになっているとします。
前の例の式を使用して、ハイフン（ “-“）をCHAR（10）に置き換えます。10はラインフィードのASCIIコードです。
アイテム名を抽出するには：
= LEFT（A2、SEARCH（CHAR（10）、A2,1）-1）
色を抽出するには：
= MID（A2、SEARCH（CHAR（10）、A2）+ 1、SEARCH（CHAR（10）、A2、SEARCH（CHAR（10）、A2）+1）-SEARCH（CHAR（10）、A2）-1 ）
サイズを抽出するには：
= RIGHT（A2、LEN（A2）-SEARCH（CHAR（10）、A2、SEARCH（CHAR（10）、A2）+ 1））
そして、これは結果がどのように見えるかです：
Excelでテキストと数値を分割する方法
そもそも、すべての英数字文字列で機能する万能な解決策はありません。 使用する数式は、特定の文字列パターンによって異なります。 以下に、2つの一般的なシナリオの式を示します。
‘text + number’パターンの文字列を分割
テキストと数字を組み合わせた文字列の列があり、数字は常にテキストの後にあるとします。 次のように、元の文字列を分割して、テキストと数値が別々のセルに表示されるようにします。
結果は2つの異なる方法で達成できます。
方法1：桁を数え、その数の文字を抽出する
数値がテキストの後に来るテキスト文字列を分割する最も簡単な方法は次のとおりです。
数字を抽出するには、0から9までのすべての可能な数字を文字列で検索し、合計数を取得して、文字列の末尾からその数の文字を返します。
A2の元の文字列では、式は次のようになります。
= RIGHT（A2、SUM（LEN（A2）-LEN（SUBSTITUTE（A2、{“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7” 、 “8”、 “9”}、 “”））））
テキストを抽出するには、A2の元の文字列の全長から抽出された桁数（C2）を引くことにより、文字列に含まれるテキスト文字の数を計算します。 その後、LEFT関数を使用して、文字列の先頭からその数の文字を返します。
= LEFT（A2、LEN（A2）-LEN（C2））
スクリーンショットに示すように、A2は元の文字列で、C2は抽出された数値です。
方法2：文字列の1桁目の位置を見つける
別の解決策は、次の式を使用して文字列の最初の数字の位置を決定することです。
= MIN（SEARCH（{0,1,2,3,4,5,6,7,8,9}、A2＆ “0123456789”））
数式のロジックの詳細な説明はここにあります。
最初の桁の位置が見つかったら、非常に単純なLEFTおよびRIGHT式を使用して、テキストと数値を分割できます。
テキストを抽出するには：
= LEFT（A2、B2-1）
数を抽出するには：
= RIGHT（B2、LEN（A1）-B2 + 1）
ここで、A2は元の文字列で、B2は最初の数字の位置です。
最初の桁の位置を保持するヘルパー列を取り除くには、MIN数式をLEFT関数とRIGHT関数に埋め込むことができます。
テキストを抽出する式：
= LEFT（A2、MIN（SEARCH（{0,1,2,3,4,5,6,7,8,9}、A2＆ “0123456789”））-1）
数値を抽出する式：
= RIGHT（A2、LEN（A2）-MIN（SEARCH（{0,1,2,3,4,5,6,7,8,9}、A2＆ “0123456789”））+ 1）
「番号+テキスト」パターンの分割文字列
数値の後にテキストが表示されるセルを分割する場合は、次の式で数値を抽出できます。
= LEFT（A2、SUM（LEN（A2）-LEN（SUBSTITUTE（A2、{“0″、 “1”、 “2”、 “3”、 “4”、 “5”、 “6”、 “7” 、 “8”、 “9”}、 “”））））
式は、前の例で説明したものと似ていますが、文字列の左側から数値を取得するためにRIGHTではなくLEFT関数を使用する点が異なります。
数値を取得したら、元の文字列の全長から桁数を引いてテキストを抽出します。
= RIGHT（A2、LEN（A2）-LEN（B2））
以下のスクリーンショットに示すように、A2は元の文字列で、B2は抽出された数値です。
これは、さまざまな関数のさまざまな組み合わせを使用してExcelで文字列を分割する方法です。ご覧のとおり、数式は一目瞭然ではないため、Excelのセルを分割するワークブックのサンプルをダウンロードして、詳しく調べることができます。
Excel数式の難解なひねりを理解することが好きではない場合は、Excelでセルを分割する視覚的な方法をお勧めします。これは、このチュートリアルの次のパートで説明されています。
テキストの分割機能を使用してExcelでセルを分割する方法
Excelで列を分割する別の方法は、Cell Cleanerアドインに含まれているテキストの分割機能を使用することです。この機能には、次のオプションがあります。
文字でセルを分割
文字列でセルを分割
セルをマスクで分割（パターン）
わかりやすくするために、各オプションを1つずつ詳しく見ていきましょう。
文字でセルを分割する
指定した文字が出現するたびにセルの内容を分割する場合は常に、このオプションを選択します。
この例では、このチュートリアルの最初の部分で使用したItem-Color-Sizeパターンの文字列を取り上げます。覚えているかもしれませんが、3つの異なる数式を使用して3つの異なる列に分割しました。そして、2つの簡単な手順で同じ結果を得る方法を次に示します。
Cell Cleanerアドインがインストールされている（試用版はここからダウンロードできます）と仮定して、分割するセルを選択し、Ablebitsデータタブのテキストの分割アイコンをクリックします。
Excelウィンドウの右側に[テキストの分割]ウィンドウが開き、次の操作を行います。
[文字で分割]グループを展開し、定義済みの区切り文字の1つを選択するか、[カスタム]ボックスに他の文字を入力します。
セルを列または行に分割するかどうかを選択します。
「プレビュー」セクションで結果を確認し、「分割」ボタンをクリックします。
ヒント。 セルに複数の連続する区切り文字がある場合（たとえば、複数のスペース文字）、連続する区切り文字を1つのボックスとして扱うを選択します。
できた！ 3つの数式と5つの異なる関数を必要とするタスクは、数秒でボタンをクリックするだけですみます。
文字列でセルを分割
このオプションを使用すると、文字の任意の組み合わせを区切り文字として使用して文字列を分割できます。 技術的には、1つまたは複数の異なる部分文字列を各部分の境界として使用して、文字列を部分に分割します。
たとえば、「and」と「or」の論理積で文を分割するには、「文字列で分割」グループを展開し、区切り文字列を1行に1つ入力します。
その結果、ソースフレーズは、各区切り文字が出現するたびに区切られます。
ヒント。 「or」および「and」の文字は、「orange」や「Andalusia」などの単語の一部になることが多いため、前後にスペースを入力して、単語が分割されないようにしてください。
そして、ここに別の実際の例があります。 次のような外部ソースから日付の列をインポートしたとします。
5.1.2016 12:20
2016年5月2日14:50
この形式はExcelの従来の形式ではないため、Date関数は日付または時刻の要素を認識しません。 日、月、年、時間、分を別々のセルに分割するには、[文字列で分割]ボックスに次の文字を入力します。
日、月、年を区切るドット（。）
コロン（:)で時間と分を区切る
日時を区切るスペース
分割ボタンを押すと、すぐに結果が得られます。
マスクでセルを分割（パターン）
セルをマスクで区切るとは、パターンに基づいて文字列を分割することです。
このオプションは、同種の文字列のリストをいくつかの要素または部分文字列に分割する必要がある場合に非常に便利です。 複雑なのは、特定の区切り文字がある場合にのみ、特定の区切り文字が出現するたびにソーステキストを分割できないことです。 次の例は、物事を理解しやすくします。
いくつかのログファイルから抽出された文字列のリストがあるとします。
必要なのは、日付と時刻があれば、エラーコードと例外の詳細を3つの別々の列に含めることです。 1つの列に表示される日付と時刻の間にスペースがあり、1つの列にも表示される例外テキスト内にスペースがあるため、区切り文字としてスペースを使用することはできません。
解決策は、次のマスクで文字列を分割することです：*エラー：*例外：*
アスタリスク（*）は任意の数の文字を表します。
コロン（:)は、結果のセルに表示されないようにするため、区切り文字に含まれています。
次に、[テキストの分割]ウィンドウの[マスクで分割]セクションを展開し、[区切り文字を入力]ボックスにマスクを入力して、[分割]をクリックします。
結果は次のようになります。
注意。 文字列をマスクで分割すると、大文字と小文字が区別されます。 したがって、マスク内の文字は、ソース文字列に表示されているとおりに入力してください。
この方法の大きな利点は柔軟性です。 たとえば、元のすべての文字列に日付と時刻の値があり、それらを別の列に表示する場合は、次のマスクを使用します。
* *エラー：*例外：*
プレーンな英語に翻訳されたマスクは、元の文字列を4つの部分に分割するようアドインに指示します。
文字列内にある最初のスペースより前のすべての文字（日付）
最初のスペースとERRORという単語の間の文字：（時間）
ERROR：とException：（エラーコード）の間のテキスト
例外の後のすべて：（例外テキスト）
Excelで文字列を分割するこのすばやく簡単な方法が気に入っていただけたことを願っています。 試してみたいとお考えの場合は、こちらからダウンロードできる試用版です。 また、スプリットテキストのパフォーマンスに満足している場合は、このアドインを個別に、またはExcel用Ultimate Suiteの一部として購入できます。 どちらの方法でも、ブログの読者のみに提供する15％割引のプロモーションコードを入手してください：AB14-BlogSpo
読んでいただきありがとうございます。来週のブログでお会いしましょう。
