Excel のTEXTSPLIT関数で文章を別々のセルに入れる。
この記事は自分用の覚書です。
■MacBook Pro macOS Monterey
バージョン 12.6
■Microsoft 365 Apps for businessサブスクリプション
(旧称 Office 365 Business)
Excel for Mac バージョン 16.69.1 (22011600)
M.K さん
昨年の8月でしたか新関数が追加されました。そのなかの一つ「TEXTSPLIT関数」を使っていますか。我々の仕事ではとても役に立ちますよ。
今のところ、Microsoft365サブスクライバーなら使える関数です。
「区切り位置」の機能を関数で実現したのがTEXTSPLIT関数です。
指定した目印でテキストを区切ってくれます。
この記事やこの記事のように「区切り位置」機能を使わなくてもよくなりました。
使ってはいけないということではないですが、我々の仕事で今後は「区切り位置」の機能の利用は減るでしょうね。
ネット上ではたくさんの人がTEXTSPLIT関数の使い方を解説してくれていますから、詳しいことはご自分で調べてください。
ここでは、我々の仕事上でたまに遭遇する、だからこそ都度やるのは面倒な「セル内にある文章を区切って別々のセルに入れる」例を取り上げます。
■「区切り位置」にはない利点
「区切り位置」にはないTEXTSPLIT関数の利点は次の3点だと私は思います。
- 行方向に分割できる。
「区切り位置」では列方向にしか区切れません。
TEXTSPLIT関数では行方向にも区切れます。 - 元の文字列が無くならない。
「区切り位置」を実行すると元の文字列が消えてしまいます。
1つのセルに入っている状態を残しておきたければ、どこかにコピー貼り付けしておくしかありません。
TEXTSPLIT関数では元の文字列は無くなりません。 - 元の文字列が変わったら連動する。
なんらかの理由で元の文字列が変わった場合、「区切り位置」ではやり直しが必要です。
TEXTSPLIT関数ではそれが必要ありません。数式のなかで元の文字列のセルを参照しているだけなので。
■TEXTSPLIT関数の構文
TEXTSPLIT関数の構文は次のようです。
引数がまだ英語圏用の表示のままなのがちょっと残念ですが、そのうち日本語表示になるのではないかと思っています。
我々の仕事でなら、左から4番目の引数まで知っておけば大抵の用途は満たせると思います。
- text
「元の文字列」を指定します。
当たり前ですが省略できません。 - col_delimiter
「列に区切るときの目印」を指定します。省略可能です。 - row_delimiter
「行に区切るときの目印」を指定します。省略可能です。 - ignore_empty
「空欄を無視するか、しないか」を指定します。省略可能です。
省略すると「FALSE」（空欄を無視しない）を指定したことになります。
引数「ignore_empty」について少しふれておきます。
たとえば、
A,B,,C
となっている文字列があって , を目印に区切ると、B の右側に , が2つあるので次のようになります。
「正味の文字はそこにないのだからカンマで区切ったら、A B C でしょう」と考えることができるのは人間だからです。
エクセルは、人間が指定した目印で元のデータを区切るので、目印があるということは隣に値があってもなかってもそこで区切って値を入れるためのセルを確保します。
だから、A,B,,C は A B □ C になります。
こうなった場合に「空のセル」を無視しないで残すか、無視して詰めるかを指定するのが引数「ignore_empty」です。
目印で区切ったときにできる空欄を無視しないのは、「区切り位置」機能と同じです。
「区切り位置」機能がそうなっているため、TEXTSPLIT関数の標準をそちらに合わせたのでしょうね。
■セルに入っている文章を区切る。
考え方は「区切り位置」機能と同じです。
上記のリンク先にもあるように、どこで区切るかの位置として指定した "もの" は消えてしまいます。
だとしたら、「。」ごとに文章を区切る場合は「。」を「。と区切りのための目印」に置換してから、その目印をTEXTSPLIT関数の引数に指定すれば「。」を残して区切れます。
次図では、「。」を「。_」（。とアンダーバー）に置換してから「_」をTEXTSPLIT関数の引数に指定しています。
引数のなかでの「_」の位置に注目してください。
引数「col_delimiter」に「_」を指定した場合は「3列」に区切られています。
「row_delimiter」に指定した場合は「3行」になっています。上記の利点の 1 番、「区切り位置」機能ではできない区切り方です。
引数「ignore_empty」は TRUE （空欄は無視する）です。
以下では和文を例にしていますが英文でもやり方は変わりません。
英文ならば、「.」（ピリオド）を「.と目印」に置換することになります。
「スピル」については大丈夫ですか。TEXTSPLIT関数はスピルします。
これからのエクセルは「スピル」についての理解が必須です。
図では「作業列」を使って処理の過程を分けています。こうすることで、全体を通して何をどうやっているのかが誰にでも理解しやすくなります。
職場で作るファイルは誰のものでもありません。皆のものです。
他の人が引き継ぐこともありますし、流用することもあります。
そんなときに、ぱっと見て何をしているのかわかりづらいファイルは迷惑でしかありません。
仕事で作るファイルは、いつ誰が見ても理解しやすく作っておくべきです。
他社さんではどういう方針でやっているかは知りませんが、我々の職場ではそういう考え方をしてください。
誤解がないようにいっておきますが、「関数をネストとして長い数式を1つのセルに入れてはいけない」わけではないです。
そうするしかない場合だってありますから。
ただ、どんな場合も「いつ誰が見ても理解しやすく作っておく」ことが大事だということです。
ひょっとして、上図の数式を見て「1つの文章の終わりに「。」が複数あることはないから、空欄はできないので "TRUE" （空欄は無視する）は不要だろう」と感じていますか。
だとしたら、ちょっとした勘違いです。さっきの A,B,,C の例と同じです。
「アンダーバーで区切ったら最後の文章の次には何もないのだから空欄はできない」と考えることができるのは人間だからです。
引数「ignore_empty」を省略する（ FALSE を指定したことになる）と次のようになります。
最後の「……である。_」にもアンダーバーが付いていますからエクセルはそこで区切って、隣にあるであろう値を入れるためのセルを確保します。
だから、
……です。………ます。………である。[空のセル]
になります。
その「空のセル」はこの事例の場合は不要なので、引数「ignore_empty」を "TRUE" （空欄は無視する）にしています。
なお、上図では「_」を使いましたが、区切るための目印にする "もの" は「記号」でなくてもかまいません。
数字も英字でも漢字でもひらがなでもカタカナでもなんでもよいです。
元データのなかにあるものは目印にはできません。そこで区切られてしまうので。
「"。" で区切るために "。" を目印付きの "。" に置き換えるときは、元のデータのなかに絶対に存在しないものを目印にする」という注意点も「区切り位置」機能と同じです。
もっと言うと、「目印」は「1文字」である必要はないです。わざわざこんなことする人はいないでしょうけどね。
■セル内改行ごとに区切る。
セル内改行で区切る場合も「区切り位置」と考え方は同じです。
CHAR関数（キャラクター関数）が「改行コード」を返すように指定してそれを引数に使います。
改行コードを返す構文は、CHAR(10)です。
これらをテンプレートにしておけば、テキストを貼り付けるだけで済みます。
TEXTSPLIT関数の登場のおかげで「セルの中の文章を区切って別々のセルに入れる」作業はずいぶん楽になりました。
積極的に使って仕事の効率を上げていきたいですね。
最近のコメント