複数の空白文字(全角/半角/タブ)を使ってExcel セルを分割する式と関数

Excel: 複数の空白文字(全角/半角/タブ)で賢く分割！TEXTSPLIT応用テクニック

Excelでデータを扱っていると、全角スペース、半角スペース、タブ文字などが混在していて、単純な区切り方ではうまくセルに分割できないことがありますよね。

これらの複数の空白文字をまとめて区切り文字として扱い、さらにスペースの連続などで発生しがちな「空のセル」も結果から除去したい。そんなニーズに応える、TEXTSPLIT関数を使ったスマートな解決策をご紹介します。

解決策: この計算式を使おう！

結論から言うと、以下の計算式でこの問題に対応できます（元データがセルA1にある場合）。全角スペース、半角スペース、タブ文字を区切り文字として元の文字列を分割する式です。

=TEXTSPLIT(A1, HSTACK("　", " ", CHAR(9)), , TRUE)

つまりは以下の式になります。

=TEXTSPLIT(文字列, HSTACK("　", " ", CHAR(9)), , TRUE)

一見少し複雑に見えるかもしれませんが、分解して見ていくと理解は難しくありません。この計算式がどのように機能するのか、各部分を詳しく解説します。

計算式の構造と関数の解説

この計算式の中心となっているのはTEXTSPLIT関数ですが、その引数の指定方法にポイントがあります。

TEXTSPLIT( ① text , ② col_delimiter , ③ [row_delimiter] , ④ [ignore_empty] , ... )
  1. text (対象文字列): A1分割したい元の文字列が入っているセルを指定します。この例ではA1セルです。
  2. col_delimiter (列区切り文字): HSTACK("　", " ", CHAR(9))ここが最大のポイントです。文字列を横方向（列方向）に分割するための区切り文字を指定します。ここでは、単一の文字ではなく、複数の文字をリスト（配列）として渡しています。そのリストを作成しているのがHSTACK関数です。
    • HSTACK関数とは？: 複数の値や配列を、引数として指定した順に水平方向（横方向）へ結合し、一つの配列として返す関数です。
    • "　": 全角スペースを区切り文字の候補として指定。
    • " ": 半角スペースを区切り文字の候補として指定。
    • CHAR(9): タブ文字を区切り文字の候補として指定。（CHAR関数は文字コード番号に対応する文字を返します。9番がタブ文字です。）

    これにより、TEXTSPLIT関数は「全角スペース、半角スペース、タブ文字のいずれかが見つかったら、そこで区切ってください」と指示されることになります。

  3. row_delimiter (行区切り文字): 省略今回は横方向に分割したいため、縦方向（行方向）の区切り文字は指定しません。引数を省略する場合は、カンマ(,)を続けて入力します。
  4. ignore_empty (空の要素を無視): TRUEこれも非常に重要なポイントです。この引数にTRUEを指定すると、区切り文字が連続していたり、文字列の先頭や末尾が区切り文字だったりした場合に生成される「空の要素（分割結果が空のセルになること）」を無視してくれます。例えば、「データA　　　　　　　　　　データB」（半角スペースとタブが連続）のような場合でも、間に空のセルが作られなくなります。

具体例

実際にどのように動作するか、例を見てみましょう。セルA1に以下のような、様々な空白文字が混在したデータがあるとします。

元データ (セル A1):

商品A　 商品B     商品C	商品D

（商品AとBの間には全角スペース、半角スペースが混在。商品BとCの間には連続した半角スペースがあります。商品CとDの間にはタブ文字があります。）

このA1セルに対して、セルB1に先ほどの計算式を入力します。

=TEXTSPLIT(A1, HSTACK("　", " ", CHAR(9)), , TRUE)

実行結果:

B1C1D1E1
商品A商品B商品C商品D

ご覧の通り、全角スペース、半角スペース、タブ文字のいずれも区切り文字として認識され、かつ、連続する区切り文字によって生じるはずだった間の空セルも除去されて、綺麗に分割されていることがわかります。

まとめ

ExcelのTEXTSPLIT関数を使って、全角スペース・半角スペース・タブ文字など、複数の空白文字をまとめて区切り文字として扱い、さらに空の要素を除去するには、以下の2つのポイントを押さえた計算式が有効です。

  1. 区切り文字引数 (`col_delimiter` または `row_delimiter`) に、HSTACK()関数または配列定数{ }を使って、区切り文字にしたい文字のリスト（配列）を指定する。
  2. ignore_empty引数に TRUE を指定して、連続する区切り文字などによる空の要素を無視させる。

コメント