Office TANAKA - [セル内改行の前後で分割する(2)]

昔に書いた古いコンテンツを「今だったら、もっと簡単にできるよな～」ってリライトするシリーズです。古いコンテンツでは、Alt + Enterキーを押してセル内改行をしたときに入力される"改行コード"であるCHAR(10)を探して、その文字位置で分割するという発想でした。昔は、それしかできなかったんです。ちなみに、CHAR関数の読みは「キャラクター関数」です。これを間違って「チャー関数」なんて読んだら、"気絶するほどバカらしい"って思われますのでご注意ください。さて、2024年の現在だったら、もっと簡単に実現できます。

古典的なやり方

そもそも昔は、任意の文字列を、何かの文字で分割するには、その分割する文字の位置をFIND関数などで調べて、LEFT関数やMID関数を使って分割していました。

最後のMID関数に指定した"256"に意味はありません。元の文字列の文字数よりも大きい数値を指定しただけです。本来なら「MID(A1,FIND("-",A1)+1,LEN(A1))」みたいに書くのがセオリーですが、まぁ、ここは手抜きです。

さて、セル入力中にAlt + Enterキーを押すと、そこには見えない"改行コード"が入力されます。

つまり「ABC-123」と「ABC 123」は、同じ形をしている文字列なんです。だったら、FIND関数などで"改行コード"の位置を調べれば、同じように分割できるはずです。

これが、今までの"古典的"な考え方です。こうした、いわば"ちからわざ"というのは、仕組みを理解するためにも、しっかり使えるようになっていてください。だた、この方法での問題は、次のように「区切り文字」が複数存在するケースです。

これは難しい、というか、超面倒くさいです。FIND関数には、ほとんどの人が知らない第3の引数「開始位置」があるのですが、2つめの"-"を探すには、まず普通に左端1文字目から"-"を探して、見つかった"1文字右"から2つめの"-"を探すという、面倒くさいことをしなければなりません。しかも、抜き出す文字数は、2つめの"-"の位置から、1つめの"-"の位置を引いて、そこに1を足すと…。考えただけでも憂鬱になります。実はここで「ちなみに、こうやるんですよ」って実際の例をご覧に入れようとしましたが、途中でやめましたｗ

現在のやり方

今なら、そんな面倒くさいことをしなくても簡単です。TEXTSPLIT関数で一発です。なお、本稿のテーマは"改行コード"で分割するって話ですけど、"改行コード"だろうが"ハイフン(-)"だろうが、本質は同じなので、ここからは"ハイフン(-)"で区切られているって前提で解説します。

拍子抜けするほど簡単ですね。このTEXTSPLIT関数では、行(下)方向に分割することも可能です。

もちろん、要素(ABCとか123など)の数が増減しても、しっかり対応してくれます。TEXTSPLIT関数に関する詳しい解説は、下記ページをご覧ください。複数の区切り記号を指定する方法なども書いてあります。

TEXTSPLIT 関数の解説

分割した後の処理

実務では「分割して終わり」ってことはないですよね。この分割した要素のうち"特定の要素"だけを抽出したいことが多いです。

・最初の要素/最後の要素だけ抽出する

まず「最初(先頭・左端)の要素」だけを抽出するのでしたら、実は専用の関数があります。それがTEXTBEFORE関数です。

まぁ、これは簡単なのですけど、じゃ「最後(末尾・右端)の要素」だけを抽出したいときは、どうしたらいいでしょう。TEXTBEFORE関数は、最初に見つかった区切り位置から左側を抜き出してくれるのですが、似た働きをするTEXTAFTER関数では、うまくいきません。試しにやってみてください。そんなときは、別の関数と組み合わせるしかないです。いくつか方法はありますが、お手軽なのはTAKE関数でしょう。

TAKE関数に関しては、下記のページをご覧ください。

TAKE 関数の解説

・指定した位置の要素だけ抽出する

"何番目の要素"みたいに、抽出したい位置を指定してみましょう。いくつかの方法がありますが、まず思いつくのはINDEX関数ですね。

INDEX関数は「INDEX(リスト,行,列)」のように書いて、指定した"行"と"列"が交差するセルを返します。今回のTEXTSPLIT関数が返す配列は1行しかありませんので、引数"行"は1です。実はこれ「INDEX(TEXTSPLIT(A1,"-"),0,B1)」や「INDEX(TEXTSPLIT(A1,"-"),,B1)」でも同じ結果になるのですが、この件は長くなるので割愛します。まぁ、無難に"1"って指定すればよろしいかと。

・複数の要素を抽出する

上述の「指定した位置の要素だけ抽出する」には、もうひとつCHOOSECOLS関数も使えます。

さらに、CHOOSECOLS関数を使うと"複数の要素"を好きな順番で抽出できます。

上図では、抽出する位置をB列で"行(下)方向"に入力していますが、もちろん"列(右)方向"に入力されていてもOKです。

・特定の要素を抽出する

では最後に、特定の要素だけを自動的に抽出する方法です。これにはFILTER関数を使います。まずは、結果をご覧ください。分割した結果のうち、どこに、いくつあるか分からないけど、"数値"の要素だけ抽出してみます。

FILTER関数が分からない方は、下記のページをご覧ください。

FILTER 関数の解説

今までよりも数式が長いので、ちょっと解説します。

これでもいいのですが、同じ「TEXTSPLIT(A1,"-")」が2回出てくるので、LET関数でまとめてやりましょう。LET関数に関しては、下記のページをご覧ください。

LET 関数の解説

FILTER関数の条件を工夫すれば自由自在です。

元データのスピルには注意

えぇと、本稿のテーマとは離れるので簡潔に書きますけど。TEXTSPLIT関数で複数の元データを扱うとき、スピルを利用しようとすると、ちょっと期待外れになりますので書いておきます。

上図のように、TEXTSPLIT関数で分割する元データが、1つのセルだったら成功します。しかし、下図のように、元データが複数セルに入力されており、それら複数セルを、スピルを使って指定すると、期待どおりにはなりません。

これは、TEXTSPLIT関数の結果が「配列の配列(Array of Array)」という状態だからです。"配列の配列"というのは、読んで字のごとくなんですが「配列の要素が配列になっている」ような配列で、一種の"ジャグ配列"です。特にこのような、一次元配列内の要素が二次元配列になっているケースは、スピルが対応していません。言い換えると、右方向にスピルような関数を、下方向にもスピらせることはできないってことです。ちなみ、TEXTSPLIT関数だけでなく、横方向にスピるXLOOKUP関数でも同様のことが起こります。これを、1つの数式で、強引に縦横方向へとスピらせることも可能ですが、めちゃくちゃ難しいのでお勧めしません。今回でしたら、セルB1の数式を、セルB4までコピーしてください。なお「配列の配列(Array of Array)」に関して詳しいことを知りたい方は、下記のページをご覧ください。

XLOOKUP関数など横にスピる関数を縦にスピらせようとすると失敗する件(配列の配列～Array of Array～)