MID関数の極意「長さが変わる文字列」の切り出し方

本稿では『文字列操作を極める』シリーズの第1回として、LEFT関数、MID関数を使った初歩的な文字列操作「切り出しテクニック」を紹介したいと思う。

当ウェブサイトは、エクセルを使ってかなり高度な業務を遂行しようとするビジネスマンに贈る、「マニアック・エクセルサイト」として立ち上げたものだ。

こうしたなか、エクセルを操作する際に、是非とも覚えておきたいのが、「文字列を操作する関数」である。

その基本は、LEFT関数RIGHT関数MIDDLE関数の3つ、そしてFIND関数LEN関数だ。。

何事も習うより慣れろ、と言われるが、本稿ではこのうちLEFT関数とMID関数、そしてLEN関数の3つを、さっそく使ってみたい。

財務省が作成する『普通貿易統計』と呼ばれる統計資料がある。

元データは政府の統計窓口『e-stat』からダウンロードできるが、ここでは2021年8月11日時点で取得したファイルの一部をダウンロードし、公表しておきたい。

【参考】普通貿易統計(2021年1月)

※著者の側にてウィルス検査をしてあるので、安心して開いていただいて良い。

ファイルを開くと、こんなフィールドが目に入る。

  • cat01_code
  • 概況品目(輸出)
  • cat02_code
  • 概況品目表の数量・金額
  • area_code
  • time_code
  • 時間軸(年次)
  • unit
  • value
図表1 今回のエクセルファイルのフォーマット

本日紹介したいのは、このうちF列、すなわち「国」から数字を取り出す方法である(次回以降はB列からデータを取り出す方法についても紹介したいと思っている)。

まず、「国」については、次のように、必ず「数字3桁」、「_(アンダーバー)」、「国名称」というフォーマットで構成されている。

  • 103_大韓民国
  • 105_中華人民共和国
  • 106_台湾
  • 107_モンゴル
  • 108_香港

このように、文字コードの長さが決まっている場合は、非常に簡単だ。LEFT関数とMID関数を使えば良い。

LEFT関数の具体的な書式は、次のとおり。

LEFT(文字列,文字数)

サンプルファイルの「国」データ(F列)については、必ず国コードが3ケタと決まっている。

ここではサンプルファイルのK2セルに、こんな数式を入力してみよう(図表2)。

=LEFT(F2,3)

図表2 LEFT関数の入力(※大文字でも小文字でも良い)

すると、F2セルに含まれる「103_大韓民国」という文字列から、前半の3文字目までの「103」を取り出すことができた(図表3)。

図表3 LEFT(F2,3)の入力結果

では、次にF2セルに含まれる「103_大韓民国」という文字列から、後半の「大韓民国」を取り出してみよう。

データを眺めていて気付くのは、すべてのデータは数字3桁と「_(アンダーバー)」が付いているため、国名はすべて5文字目から始まる、という点だが、ここで問題がある。

じつは、「決まった文字数を左から(あるいは右から)切り出す」のは、大変に簡単な作業だ。

しかし、国名の場合は単純ではない。「大韓民国」の場合は4文字だが、その次の「中華人民共和国」は7文字、「台湾」は2文字、と、文字数が一定しないからだ。

このような場合にどうすればよいか。

最も簡単なやり方が、MID関数である。

MID関数の具体的な書式は、次のとおり。

MID(文字列,開始位置,文字数)

開始位置とは「文字を切り出し始めるための場所」のことであり、先ほどのケースでいえば、「_(アンダーバー)」の直後、つまり5文字目となる。

一方、文字数については「最低限、これ以上切り出してほしい」という文字数を指定する。じつは、これについてはピッタリでなくても構わない

たとえば、開始位置に「5」、文字数に「100」と入力してやれば、「この文字列の5文字目から100文字目までを抜き出してほしい」、という指示となる。ということは、極端な話、すべてのデータの最長文字数を入れてやれば、「国名を抜き出す」という目的は達成できる、というわけだ(図表4図表5)。

図表4 MID関数の入力

図表5 MID(F2,5,100)の入力結果

これで、いちおうは形になった。

以上で本稿を終了としても良いのだが、ただ、せっかくだからもう少しスマートなやり方も紹介しておきたい。ここで使用するのはLEN関数だ。

LEN関数の具体的な書式は、次のとおり。

LEN(文字列)

これは、文字の長さを返す関数であり、先ほどの「F2」列を指定してやれば、文字の長さである「8」が返ってくる(図表6図表7)。

図表6 LEN関数の入力

図表7 LEN (F2)の入力結果

そして、すべてのセルにおける国名の文字の長さは、次の計算式で求められるはずだ。

(国名の文字の長さ)=(セルのデータの文字の長さ)-4

ということは、先ほどのMID関数についても、MID(F2,5,100)ではなく、MID(F2,5,LEN(F2)-4)と入力してやれば、ずばりの文字数を抜き出すことができるはずだ。

MID(F2,5,LEN(F2)-4)→F2セルの5文字目から、「LEN(F2)-4」で求まる数値の長さの文字を切り出す、という意味。

さっそくやってみよう(図表8図表9)。

図表8 MID+LEN関数の入力

図表9 MID(F2,5,LEN(F2)-4)の入力結果

いかがだろうか。

念のため、「大韓民国」以外の国名にも対応できるかどうか、関数をコピーしてみよう(図表10)。

図表10  MID(F2,5,LEN(F2)-4)を下にコピーした結果

ちゃんと出来上がった。

それぞれシンプルな関数なのだが、これらを組み合わせることで、かなり複雑な演算ができることがおわかりいただけるだろう。

※ ※ ※ ※ ※ ※ ※

本稿を『文字列操作を極める』シリーズの第1回と位置付けたい。第2回目以降では、さらに複雑な文字列操作にチャレンジしていくつもりだ。