Excelの関数だけで左詰めするには複数の関数を組合わせる
ちょっと前にセルに入力された文字に何らかの理由で空白スペースが入り込み、それを削除したいというケースがありました。
「TRIM系関数でいけるでしょ」と思っていたら、なんとExcelの関数に文字の左側だけを削除するものがないと分かりました。。
そこで今回はいくつかの関数を組み合わせることで、左詰めする方法について説明したいと思います。
今回使用したテスト用のデータですが、上記画像に加えて右側に列を加えています。
説明の便宜上、空白文字が分かりづらいので、空白文字を■に変換したものです。
特にテストデータには「末尾にも空白を入れている」セルもあります。
先頭だけでなく末尾も誤って削除されないかを確認するためでもあります。
空白だと見づらい、分かりづらいので■に変換しています。
こちらの部分も適宜ご覧になりながら確認いただければと思います。
「左詰め」するための関数について
今回の作業で使用した関数は複数あります。関数の中で関数を使う、いわゆる「ネスト」という形で実現しています。
最終的にはネストして使っているのですが、今回は説明のしやすさも兼ねて作業列を使った説明をしています。
なお、作業列とは、ネストを分解した列です。上記画像のように複数の関数を1つのセル(列)にまとめることでスッキリしますが、可読性(読みやすさ)が下がります。
一方、作業列は、関数ごとに関数専用の列を複数列作ります。1列目に関数を入れます。その1列目の関数結果を2列目の関数で使用する。そして2列目の関数結果を3列目の関数で使用する・・という形で使います。
後ほど当ページでもこの方法で説明していますので参考にしていただければと・・
とにかく試したいという方へ
とはいえ、「とにかく、まずは試したい」という方は、新規ブックを作成し、以下の文字をコピーして「セルC3」に貼り付けてください。
=MID(B2, SEARCH(LEFT(TRIM(B2),1),B2,1),LEN(B2))
貼り付けた瞬間は「#VALUE!」になりますが、そのまま「セルB2」に先頭に空白を含めた文字を入力してみてください。
セルC3に貼り付けた関数が動いた結果が表示されますので先頭の空白が削除されているのかを確認してみてください。
※セルB2に入力です。セルA1ではないことにご注意です。
ネストを分解して関数の内容をトレースしてみる
今回は、関数がネストされた状態で見るのではなく、それぞれの関数を分解し、作業列を作って見ていきたいと思います。
上記が作業列を追加した状態です。
関数はセットすると同時に結果が表示されますが、イメージとしては矢印のように使用していく形になります。
ざっくりいうと、「MID」の列と「関数結果」の列が同じになっていれば、ネスト方法と作業列での方法どちらもうまくいったということにします。
※LEN関数は文字数を取得するだけであり、画像が横に広がりすぎることを避けたかったので、LEN関数の列は作っていません。
最近、特に思うのですが、作業列は便利かもと・・
作業列は、関数の複雑さを「ある程度ほぐす役割」を持っていると思うようになりました。
シート上に「作業のための列」があることは、(非表示にしたとしても)なんとなく居心地の悪さもあるのですが、作業列があったほうが(作った事自体を忘れたくらいのタイミング)後から見た時、第三者が見たとき、こういったときにより早い理解の手助けになると思うのですね。
それでは、実際に各作業列を見ていきたいと思います。ネストされた状態の式も参考に入れておきます。
なお、表の一番下にある式はNo1に入力されている関数の内容です。
MID関数、LEN関数
今回の方法では、最終的に「MID関数を使えば左詰めできる」ということです。
ネストした形でも、複数ある関数の中でもMID関数を最後に実行されるよう最も外側に配置させています。
MID関数とは、指定した文字の一部を抜き出す関数ですね。
指定した文字の何文字目(開始位置)から何文字分(文字数)を指定します。
上記の例では、「あいうえお」の文字から開始位置が1文字目、すなわち「あ・・」を指し、文字数が1ですので、「あ」だけを表示している、ということになります。
そして、今回の作業で言い換えると、
開始位置：先頭の空白文字が終わった次の文字(の位置)
文字数 ：開始位置から末尾までの文字数
となります。
なお、「文字数」については、最大文字数を指定すれば事足ります。
したがいまして、それは「テスト文字のセルにある文字数が最大」となりますので、LEN関数を入れて対処しています(実際には文字の左側に空白があれば、必要とする最大文字数は減りますが、影響を受けないためこのようにしています)。
もし意味が不明という方は、LEN(B2)と入れている箇所に直接「1」、「2」と順番に入れて、表示される内容がどのように変化するのか確認してみてください。
MID関数の「開始位置」がポイントになる
MID関数でできるならば、他の関数は不要にしたいところですが、それをできなくさせているのが、「開始位置」なのですね。
上記の文字はNo4、5の内容ですが、このようにMID関数で指定するときに、文字により「開始位置が異なる」のですね。
そのため、開始位置は、入力されている文字によって変えなければなりません。
ということで、他の関数も必要になるということです。
すなわち、今回登場する他の関数たちは、「開始位置を確定する目的で使う関数」とも言えます。
TRIM関数
今回の作業では、最も最初に実行される関数です。この関数を使用している理由は、文字列の左側にある空白を削除することです。
TRIM関数は、文字の前後の空白を削除することができます。
このようにすることで「最初の1文字目は空白でない文字」にすることができるからです。
先ほど言いましたように、「開始位置を特定するため」の第一歩です。
このページをご覧になっている皆さんの中でも、末尾の空白だけ残したいということがなければこの関数でも事足りると思います。
言い換えると、当ページの方法は「先頭の空白スペースは削除するが、末尾の空白スペースは削除しない方法」とも言えます。
LEFT関数
文字の左側(1文字目)から指定した文字数分だけ表示します。
今回の作業では、(TRIM関数後の)最も左側にある1文字目が必要のためこの関数を使用しています。
下の図のように「TRIM関数後の文字から1文字目」ということで「あ」が表示されています。
(TRIM関数で左側にある空白文字を削除した後の1文字目ということに注目です)
サンプルの「テスト文字」では、左から始まる空白以外の最初の文字は「あ」から始まるにしていますが、他の文字にすると、空白を除いた最初の文字が表示されます。
これは、念のためにですが、常に「あ」という文字が表示されるわけではないといことを意味するために付け加えました。
SEARCH関数
指定した文字列を検索し、見つかった場合にその文字が「先頭から何文字目にあるのかの位置」を返します。
今回の作業では、上記のLEFT関数で取得した1文字目の文字(「あ」)が、テスト文字の何文字目にあるのかを知るために使用しています。
つまり、文字を左から見たときの空白の終わり(言い換えると空白以外の文字の開始)位置が分かります。
そうですね、つまりは、SEACH関数を使って「空白スペース直後の開始位置が分かる」ということですね。
たとえば、No4のテスト文字では「あ」という文字が2文字目から開始されていることが分かります。
同様に、No5では「あ」という文字が3文字目から開始されているということが分かる、という具合です。
そしてこのように関数を分割してやっと、「開始位置が分かる」ということになります。
そうして初めて「MID関数を使うことができる(必要な値が表示できる)」という流れになります。
「関数の結果じゃなくて”値”として欲しい」ときは
「いやいや関数の結果とかじゃなくて、そもそもの値として必要なんだけど・・」という方は、関数結果のセルをコピーし、「値貼り付け」してみてください。
関数が消えて、関数の結果のみの状態になってセルに入力されます。
「先頭じゃなくて末尾の空白スペースを削除したい」ときは
今回とは逆に末尾のスペース文字も削除したいこともあるかもしれません。
結論からいうと、やはり関数のみで対処できます。
が、考え的にも最終的には「MID関数を使う」でいけるのですが、やや面倒です。
SEACH関数の部分で「末尾から文字を探すということができない」ためですね。
そのため別の関数を使って行う(更にネストする)ということになります。
こちらは改めてご紹介したく思います。