今回は、ExcelのTEXTSPLIT関数を使い、区切り文字を含む文字列を列や行へ分割して整理する方法を紹介します。
TEXTSPLIT関数とは
TEXTSPLIT関数は、一つのセルに入った文字列を、指定した区切り文字の位置で分ける関数です。氏名、住所、商品コード、タグ、CSV形式に近い文字列などを、複数のセルへ展開できます。
従来の「区切り位置」機能は、実行した時点で文字列を複数セルへ分けます。元の文字列を変更しても、分割結果は自動更新されません。TEXTSPLIT関数は数式なので、元セルの内容が変わると結果も更新されます。
分割された結果は、数式を入力したセルから横方向または縦方向へスピルします。分割数が一定でないデータにも対応しやすいことが特徴です。
列方向へ文字列を分割する
氏名が「姓,名」、商品情報が「コード-色-サイズ」のように入力されている場合は、区切り文字を指定して横方向へ分割できます。
TEXTSPLIT関数の最初の引数に元の文字列、次の引数に列方向の区切り文字を指定します。区切りが二つあれば結果は三列へ展開されます。
分割先に別の値や数式があると、結果を展開できずスピルエラーになります。数式を入力する前に、右側へ必要な空きがあるか確認します。元データの列へ直接上書きするのではなく、隣の空き列で結果を確認すると安全です。
行方向へ文字列を分割する
セミコロンでつながったタグや、改行で区切られた項目を縦方向へ並べたい場合は、行方向の区切り文字を指定します。
列方向の区切り文字を使わない場合でも、関数の引数位置を保つために省略方法を確認します。数式入力時に表示される引数の案内を見ながら、行区切りの位置へ指定します。
縦方向の結果は、一覧としてFILTER関数やUNIQUE関数へ渡しやすくなります。複数セルの文字列をまとめて分割する場合は、結果の並び順や空白の扱いを小さなデータで確認してから広げます。
列と行を同時に分割する
一つの文字列内に、列を区切る記号と行を区切る記号の両方がある場合、TEXTSPLIT関数で二次元の表へ展開できます。
たとえば、項目間をカンマ、レコード間をセミコロンで区切った文字列なら、カンマを列区切り、セミコロンを行区切りとして指定します。結果は行列形式になります。
各行で項目数が異なると、足りない部分にエラー値が入る場合があります。埋める値を指定する引数を使い、空欄や任意の文字へ置き換えられます。分割後の表で列数が揃うかを確認することが重要です。
複数の区切り文字に対応する
元データによって区切りがカンマ、読点、スラッシュなどに揺れている場合は、複数の区切り文字を配列として指定できます。いずれかの記号が現れた位置で分割されます。
ただし、区切り文字の揺れをすべて数式で吸収すると、想定外の文字まで分割することがあります。たとえば住所内のハイフンと商品コード内のハイフンでは意味が異なります。
データの用途が同じなら、可能な範囲で入力時の区切りを統一します。外部から受け取るデータなど統一できない場合に、複数区切りを使って対応します。
連続する区切り文字と空欄を扱う
「A,,C」のように区切り文字が連続するデータでは、間に空の項目があると考えられます。TEXTSPLIT関数では、この空項目を結果へ残すか無視するかを指定できます。
列の意味が固定されているデータでは、空項目を残す必要があります。たとえば「姓,名,部署」の名が空欄でも、部署を二列目へ詰めると項目の意味が変わります。
一方、単なるタグ一覧で余計な区切りが混じっている場合は、空項目を無視すると扱いやすくなります。空欄が欠損項目を表すのか、不要な区切りを表すのかを判断して設定します。
大文字と小文字の扱いを指定する
区切り文字にアルファベットを使う場合は、大文字と小文字を区別するかを指定できます。通常の記号区切りでは意識する場面は少ないものの、特定の単語を境界として分ける場合に影響します。
製品コードなどで大文字と小文字に意味があるデータでは、意図しない位置で分割されないよう確認します。日本語の文字列でも、全角と半角の記号は別の文字として扱われるため、両方が混在している場合は区切り文字を追加するか、先にSUBSTITUTE関数などで統一します。
TEXTBEFORE関数やTEXTAFTER関数との使い分け
区切り文字より前だけ、または後ろだけが必要な場合は、TEXTBEFORE関数やTEXTAFTER関数のほうが目的を表しやすいことがあります。
TEXTSPLIT関数は、すべての項目を複数セルへ分けたい場合に向いています。メールアドレスの「@」より前だけを取り出す、ファイル名の最後の拡張子だけを取り出すといった処理では、前後を取得する関数を検討します。
数式は短さだけでなく、何をしたいか読み取れることも重要です。分割結果の一部しか使わないのに大きなスピル範囲を作ると、周囲のセルを占有するため、用途に合う関数を選びます。
TRIM関数と組み合わせて空白を整える
カンマの後に半角スペースが入るデータでは、分割結果の先頭へ空白が残ることがあります。見た目では分かりにくくても、検索や重複判定で別の値として扱われる原因になります。
TEXTSPLIT関数の結果へTRIM関数を適用すると、不要な半角スペースを整理できます。全角スペースや改行しない空白などはTRIM関数だけで除去できない場合があるため、CLEAN関数やSUBSTITUTE関数との組み合わせを検討します。
整形を複数段階で行う場合は、LET関数で元文字列、置換後、分割後に名前を付けると、処理の流れを読みやすくできます。
改行で区切られた文字列を分割する
一つのセル内でAltキーとEnterキーによる改行が使われている場合、改行コードを行区切りとして指定できます。CHAR関数などで改行文字を表し、TEXTSPLIT関数へ渡します。
アンケート回答、住所、箇条書きの貼り付けデータなどを一項目ずつ縦へ展開するときに便利です。Windowsとほかの環境から取得したデータでは、改行コードの組み合わせが異なることがあります。
分割できない場合は、CLEAN関数で制御文字を整理する、複数の改行候補を区切りとして指定するなどの方法を試します。元データを数件確認し、どの文字が入っているかを判断します。
スピル結果をほかの処理へつなげる
TEXTSPLIT関数の結果は、スピル範囲として別の関数から参照できます。分割後にUNIQUE関数で重複を除く、SORT関数で並べ替える、COUNTA関数で項目数を数えるといった処理が可能です。
たとえば、複数のタグを一つのセルから縦へ分割し、重複のない一覧へ整理できます。関数を入れ子にする場合は、まずTEXTSPLIT関数だけで期待する結果が出ることを確認し、その後で次の関数を追加します。
複雑になった場合は、途中結果を別セルへ表示して確認するか、LET関数で段階ごとに名前を付けます。エラー処理を最後にまとめて隠すより、どの段階で想定外の結果になったか確認できる構成にします。
使用時の注意点
- 分割先に値や結合セルがなく、展開できる空間があるか確認する
- 空項目を残すか無視するかをデータの意味に合わせて決める
- 全角と半角、前後の空白、改行コードの違いを確認する
- 一部だけ必要ならTEXTBEFORE関数やTEXTAFTER関数も検討する
- 共有先のExcelがTEXTSPLIT関数へ対応しているか確認する
大量データを定期的に分割する場合は、数式を多数コピーするよりPower
Queryが適することもあります。少数のセルを動的に処理するならTEXTSPLIT関数、外部データを繰り返し整形するならPower
Queryというように、更新方法で使い分けます。
まとめ
ExcelのTEXTSPLIT関数は、指定した区切り文字を基準に、一つの文字列を列方向または行方向へ分割します。元データの変更へ追随し、項目数が変わる場合もスピルによって結果範囲を調整できます。
利用時は、区切り文字の表記揺れ、連続する区切り、空項目、前後の空白を確認します。分割後のデータを検索や集計へ使うなら、TRIM関数などで表記を整えます。分割後の各位置が何の項目を表すかを保ちながら、必要な方向へ展開することが、TEXTSPLIT関数を使うポイントです。