スプレッドシートで作業中、一つのセルに「名前(ふりがな)」のように入力されたデータを分割したい場面はありませんか？
まずSPLIT関数を思い浮かべるでしょう。しかし、区切り文字に)を指定して分割すると、その)自体が結果から消えてしまい、データが不完全になって困った経験をお持ちの方も多いはずです。
この記事では、SPLIT関数のこの「落とし穴」を、SUBSTITUTE関数を組み合わせるという少し意外な方法で、スマートに解決するテクニックを解説します。
なぜSPLIT関数だけでは不十分なのか？
まず、SPLIT関数だけではなぜうまくいかないのか、その原因をはっきりさせておきましょう。
問題の具体例
例えば、セルに「名前(ふりがな)」というテキストが入っているとします。これを)で分割しようとして、以下のような数式を入力します。
=SPLIT("名前(ふりがな)", ")")この数式を実行すると、結果は「名前(ふりがな」となり、期待していた閉じ括弧)が消えてしまいます。
原因の解説
この現象が起こる原因は、SPLIT関数の仕様そのものにあります。特に、外部システムからインポートしたデータや手入力されたデータなど、フォーマットが統一されていない不規則なデータを扱う際によく直面する問題です。
SPLIT関数は、指定された「区切り文字」をテキスト分割の目印として使いますが、その区切り文字自体は結果から削除されます。これは、'A,B,C' のようなCSV形式のデータを分割する際に、カンマ(,)自体は不要な「純粋な区切り役」であるため非常に便利な仕様です。
しかし今回の問題は、区切り文字である)が、同時にデータとして残したい意味のある文字でもあるために発生します。SPLIT関数は、区切り文字がデータの一部であるケースを想定して設計されていないのです。
発想の転換：「分割するために、まず文字を追加する」
この問題を解決する鍵は、発想の転換にあります。「区切り文字を消してしまうなら、消えても良い別の文字を区切り文字にすればいい」のです。
ここでのポイントは、「残したい文字（)）を区切り文字にするのではなく、分割用の『目印』となる別の文字を一時的に追加する」というアプローチです。
SUBSTITUTE関数の役割
この「目印の追加」を実現するキーとなるのがSUBSTITUTE関数です。SUBSTITUTE関数は、文字列内にある特定のテキストを、別のテキストに置き換える機能を持っています。
この関数を使って、元の文字列に含まれる)を、)@のように「元の文字 + 目印」という形式に置換します。@は目印の一例で、データ内で使われていない記号であれば何でも構いません。
例えば、以下の数式を実行してみましょう。
=SUBSTITUTE("名前(ふりがな)", ")", ")@")この結果は「名前(ふりがな)@」となり、)の後ろに目印の@が追加された新しい文字列が生成されます。
完成形：SPLITとSUBSTITUTEを組み合わせた数式
これで準備は整いました。SUBSTITUTE関数で作った文字列を、SPLIT関数で分割します。
A1セルに元のデータが入っていると仮定した場合、完成形の数式は以下のようになります。
=SPLIT(SUBSTITUTE(A1, ")", ")@"), "@")
数式の動作解説
この数式は、以下の2ステップで動作します。
• ステップ1 (内側の関数): まずSUBSTITUTE(A1, ")", ")@")が実行されます。A1セルのデータ内にある)がすべて)@に変換され、「名前(ふりがな)@」のような一時的な文字列が作られます。
• ステップ2 (外側の関数): 次にSPLIT関数が、ステップ1で生成された文字列に対して処理を行います。今度の区切り文字は、追加した目印である@です。SPLIT関数は@を基準にデータを分割し、区切り文字である@自体は消去します。
この結果、1列目のセルには、元の)が消えることなく「名前(ふりがな)」が完璧に出力されます。2列目には空白のセルが生成されますが、これは意図した動作です。私たちが追加した一時的な区切り文字@が文字列の末尾にあったため、SPLIT関数はその後に続くデータ用のセル（つまり空白のセル）を作成します。業務で利用する上では、この2列目のセルは無視して、1列目の完璧な結果だけを活用すれば問題ありません。
結論：まとめ
今回ご紹介したテクニックの要点はSPLITしたい文字を直接区切り文字に指定するのではなく、SUBSTITUTEで一時的な区切り文字を追加し、それをSPLITするという点です。
このように、一つの関数だけでは解決が難しい問題も、複数の基本的な関数を組み合わせる創造性によって、シンプルかつ強力な解決策を生み出すことができます。
