今回は、ExcelのTEXTSPLIT関数を使って、住所、氏名、コード、メモ欄などの文字列を分割する方法を紹介します。
TEXTSPLITでできること
Excelでは、1つのセルに複数の情報が入っていることがあります。たとえば「姓 名」「部署-担当者」「商品コード/色/サイズ」のような文字列です。このままでは並べ替えや集計がしにくいため、必要な単位に分ける作業が発生します。
TEXTSPLIT関数は、区切り文字を指定して文字列を複数のセルに分ける関数です。従来の「区切り位置」機能でも分割できますが、TEXTSPLITは数式なので、元データが変わったときに結果も更新されます。繰り返し取り込むデータを整える作業に向いています。
基本の使い方
TEXTSPLITは、分割したい文字列と区切り文字を指定して使います。たとえばA2に「東京-営業-田中」と入っている場合、ハイフンで分けるなら次のような考え方になります。
TEXTSPLIT(A2,”-“)
この式を入力すると、区切り文字ごとに横方向へ結果が展開されます。左から順に「東京」「営業」「田中」のように分かれます。結果が複数セルへ広がるため、右側のセルに既存データがあると展開できないことがあります。先に出力先の範囲を空けておくと安心です。
横方向と縦方向の分割を使い分ける
TEXTSPLITでは、列方向の区切り文字と行方向の区切り文字を分けて指定できます。横に並べたいときは列方向、縦に並べたいときは行方向の指定を使います。
- 項目を列として分けたい場合は横方向に展開する
- 箇条書きのような内容を行として分けたい場合は縦方向に展開する
- 複数の区切り文字が混ざる場合は配列として指定する
たとえば、メモ欄に「確認済,要返信,請求書待ち」のようなタグが入っている場合、横に分ければ項目列として扱えます。縦に分ければ、タグ一覧として見直しやすくなります。目的に合わせて出力の向きを決めます。
複数の区切り文字に対応する
実務のデータでは、区切り文字が統一されていないことがあります。「A-B/C」のようにハイフンとスラッシュが混ざる場合、区切り文字を複数指定すると処理しやすくなります。
区切り文字が複数あるときは、ハイフン、スラッシュ、スペースなど、分割したい記号をまとめて指定します。ただし、スペースは氏名や住所の一部にも含まれることがあるため注意が必要です。何を区切りとみなすかを先に確認することで、必要な文字まで分けてしまう失敗を避けやすくなります。
空白の扱いを整える
分割後の文字列に前後の空白が残ることがあります。見た目ではわかりにくくても、検索や照合で一致しない原因になります。TEXTSPLITだけで整わない場合は、TRIM関数と組み合わせて余分な空白を除きます。
たとえば、分割前のデータが「営業部 / 田中 / 東京」のようにスラッシュの前後に空白を含む場合、分割後に「営業部 」のような余白が残ることがあります。結果を別の関数やピボットテーブルで使うなら、空白処理まで含めて式を作ると扱いやすくなります。
分割数がそろわないデータへの対応
すべての行が同じ数の項目を持つとは限りません。「部署-氏名」だけの行と、「部署-氏名-地域」まである行が混在することがあります。この場合、分割結果の列数が行によって変わります。
欠けた項目を空欄にするか、代わりの文字を表示するかを決めておくと、後続の確認がしやすくなります。たとえば、未入力を「未設定」と表示する設計にすれば、フィルターで確認できます。ただし、集計用の表では文字を入れず空欄にしたほうが使いやすい場合もあります。
元データを残して作業する
TEXTSPLITは数式なので、元データを残したまま分割結果を作れます。これは、取り込みデータの修正履歴を残したい場合に便利です。元の列を削除してしまうと、分割ルールが合っていたか確認しにくくなります。
作業用の列を作り、元データ、分割結果、確認用の列を並べると、後から見直しやすくなります。分割結果を別シートに出す場合も、元データの行番号やIDを一緒に持たせると、問題があったときに戻りやすくなります。
他の関数と組み合わせる
TEXTSPLITは単独でも使えますが、他の関数と組み合わせると実務向けになります。たとえば、分割後の先頭項目だけ取り出す、余分な空白を消す、空欄なら別の表示にする、といった処理です。
- TRIMで前後の空白を整える
- INDEXで分割結果の一部だけ取り出す
- IFERRORで想定外の形式に備える
- LOWERやUPPERで英字の表記をそろえる
複雑にしすぎると式の意味が追いにくくなります。作業列を分けて、分割、空白処理、確認のように段階を分けると、修正しやすい表になります。
分割後の確認列を用意する
TEXTSPLITで分けた後は、分割できたかどうかを確認する列を用意すると見落としを減らせます。たとえば、想定より項目数が少ない行、空欄が残った行、区切り文字が含まれていない行を確認します。
- 元データに区切り文字があるか確認する
- 分割後の必須項目が空欄ではないか見る
- 余分な空白が残っていないか確認する
- 例外的な入力形式を別シートに控える
取り込み元の表記が安定していない場合、分割式だけで全てを整えようとすると式が読みにくくなります。例外を確認し、元データ側の入力ルールを直せるなら、先にルールを整えるほうが管理しやすくなります。
区切り文字を変える前に控えを残す
区切り文字の指定を変えると、分割結果が大きく変わることがあります。特に、スペース、カンマ、スラッシュは、データの一部として使われている場合もあります。式を直す前に、元データと現在の分割結果を控えておくと、修正後に比較できます。複数人で使う表では、どの文字を区切りとして扱うのかをメモに残すと、後から式を変更する人も判断しやすくなります。取り込み元の入力ルールが変わったときは、分割式だけでなく確認列も見直します。
まとめ
ExcelのTEXTSPLIT関数は、区切り文字をもとに文字列を分けたいときに役立ちます。元データが変わると結果も更新されるため、繰り返し扱う名簿、コード、メモ欄の整理に向いています。区切り文字の種類、出力方向、空白の扱い、分割数がそろわない場合の処理を決めておくと、後続の検索や集計につなげやすくなります。元データを残し、必要に応じてTRIMやIFERRORと組み合わせることで、確認しやすい分割表を作れます。