Excelでカンマ区切りの文字列から指定ブロックを取り出す方法
Excelでは、MID関数とFIND関数とSUBSTITUTE関数を組み合わせること、もしくはFILTERXML関数とSUBSTITUTE関数を組み合わせることで、カンマ区切りの文字列から指定したブロックを取り出すことができます。今回はその方法を詳しく解説します。
なお、Office 2013以降のExcelを使用している場合には、管理しやすい「FILTERXML関数によるブロックの取り出し」を採用することを推奨します。
Office 2021以降では「TEXTSPLIT関数によるブロック取り出し」が利用可能です。
例題
例えば、セルA1に
あああ,いい,ううううう,ええええ,おお,かかかかか
というカンマ区切りの文字列があるとします。この中から「3」ブロック目の「ううううう」を取り出したい場合、以下にかかげる手法を使います。
旧式の方法解説(管理が大変なので非推奨：FILTERXML関数を使わない)
まず、第3ブロックの開始位置を求めます。そのために、「あああ,いい,ううううう,ええええ,おお,かかかかか」の中の2番目のカンマの位置を求める必要があります。そのために以下の式を使います。
=FIND("☆",SUBSTITUTE(A1,",","☆",2))
この式では、SUBSTITUTE関数で2番目のカンマを特殊な文字（この場合は”☆”）に置き換え、その位置をFIND関数で求めています。
次に、3ブロック目の終了位置、つまり3番目のカンマの位置を求めます。そのために以下の式を使います。
=FIND("☆",SUBSTITUTE(A1,",","☆",3))
同様に、SUBSTITUTE関数で3番目のカンマを特殊な文字（この場合は”☆”）に置き換え、その位置をFIND関数で求めています。
これで、3ブロック目の開始位置と終了位置がわかりました。あとは、MID関数を使ってその範囲の文字列を取り出します。
=MID(A1, FIND("☆",SUBSTITUTE(A1,",","☆",2))+1, FIND("☆",SUBSTITUTE(A1,",","☆",3))-FIND("☆",SUBSTITUTE(A1,",","☆",2))-1)
この式では、MID関数で「あああ,いい,ううううう,ええええ,おお,かかかかか」から指定した範囲の文字列を取り出しています。開始位置は2番目のカンマの次の位置、終了位置は3番目のカンマの前の位置となります。そして、その間の文字数は終了位置から開始位置を引いたものとなります。
実際に使用する際は3ブロック目以外から取り出したいことが多いでしょうから、開始位置は取り出したいブロックの前にあるカンマの次の位置になるよう調整してください。
注意点
ただし、この方法では指定したブロックが文字列の最後にある場合、つまりその後ろにカンマがない場合、正しく動作しません。その場合は、元の文字列の最後にカンマを追加してから処理を行います。
=MID(A1&",", FIND("☆",SUBSTITUTE(A1&",",",","☆",2))+1, FIND("☆",SUBSTITUTE(A1&",",",","☆",3))-FIND("☆",SUBSTITUTE(A1&",",",","☆",2))-1)
これで、「あああ,いい,ううううう,ええええ,おお,かかかかか」の3ブロック目を正確に取り出すことができます。
まとめ
ExcelのMID関数とFIND関数とSUBSTITUTE関数を組み合わせることで、カンマ区切りの文字列から任意のブロックを取り出すことができます。しかし、文字列の最後のブロックを取り出す場合は少し工夫が必要です。この記事で紹介した方法を活用して、Excelの計算処理をより効率的に行いましょう。
FILTERXML関数によるブロックの取り出し(推奨：Office 2013以降)
ExcelのFILTERXML関数を用いると、カンマ区切りの文字列から特定のブロックを取り出すのが非常に簡単になります。この関数は、XML形式のデータから特定の情報を取り出すための関数ですが、一旦文字列をXML形式に変換することで、ブロックの取り出しも可能になります。
FILTERXML関数とSUBSTITUTE関数を組み合わせてブロック取り出しをする標準の計算式は以下の通りです。「n」はブロック番号です。
=FILTERXML("<t><s>"&SUBSTITUTE(セル名,",","</s><s>")&"</s></t>","//s[n]")
ここで、”<t>”と”<s>”は、文字列をXML形式に変換するためのタグです。”<t><s>”は文字列の開始を、”</s></t>”は文字列の終了を示します。
“//s[n]”はXPathと呼ばれるXML検索言語の一部で、n番目の”<s>”タグを含む要素を取得します。ここで、nは取り出したいブロックの番号を指定します。
サンプルとその解説
セルA1に「あああ,いい,ううううう,ええええ,おお,かかかかか」という文字列が入っているとします。この中から3番目のブロックを取り出すには、以下のようにします。
=FILTERXML("<t><s>"&SUBSTITUTE(A1,",","</s><s>")&"</s></t>","//s[3]")
親のFILTERXML関数のセクション
子のSUBSTITUE関数のセクション
この式では、まずSUBSTITUTE関数を使用して、元の文字列中のカンマをXMLタグに置換しています。そして、その結果をFILTERXML関数に渡して、3番目のブロックを取り出しています。
FILTERXML関数を使用すると、短い式で簡単に任意のブロックを取り出すことができます。しかし、FILTERXML関数はExcel 2013以降のバージョンでのみ使用可能なので、その点は注意が必要です。
新情報(Office 2021以降)
新情報1：カンマ区切りのデータを縦方向に分割して表示する
「スピル」を用いるのでOffice 2021でのみ使用可能な便利な数式を紹介します。下記のページではカンマ区切りのデータを自動的に縦に分割する計算式をご紹介します。
新情報2：TEXTSPLIT関数を使ってカンマ区切りのデータの指定ブロックを取り出す
Excel 365で追加されたTEXTSPLIT関数を活用すると、カンマ区切りのデータを分割し、さらに指定ブロックを取り出せるようになります。従来の手法より大幅に作業が簡略化されます。最新バージョンのExcel 365を利用可能な場合は以下のページをご参照ください。
まとめ
この記事では、カンマ区切りの文字列から任意のブロックを取り出す2つの方法について解説しました。一つ目はMID関数、FIND関数、SUBSTITUTE関数を組み合わせた方法、二つ目はFILTERXML関数を使用した方法です。どちらの方法もそれぞれ特性と利点があるため、自身のニーズに応じて適切な方法を選択しましょう。
コメント