LEFT関数・RIGHT関数・MID関数

2024年8月5日2024年8月10日

セルの中の文字の一部だけを取り出すLEFT関数、RIGHT関数、MID関数について解説します！

文字を取り出す

セルの中の文字を取り出すってどゆこと？って思うかもしれません。
例えば、果物の入荷があったときに、次のように記録していきました。

入荷コードってなんか日付に似てるって思いますよね。その通りで入荷日を表しているんです。でも、最後にアルファベットがついてます。これは入荷した商品をランク分けして付けたアルファベットなんです。
このアルファベットがついているおかげで、Excelはこの入荷コードは日付ではなく、文字としてみています。だから日付のデータで扱えれば、入荷から今日までの日数とかが計算できたんですけど、それができなくなってます。でも、入荷した商品のランクは最後の1文字を見ればわかるっていう状態です。

こんなとき、このデータから日付の部分だけを取り出したり、ランクだけを取り出せればいいと思いませんか？うまくいけばランクごとの入荷回数とかも集計できます。

こんな風に文字の中から一部を抜き出すってことなんです。

LEFT関数

この表だと、日付は入荷コードの左から9文字を取り出せばいいと思います。

左は英語でleftなのでLEFT関数です。

=LEFT(文字列,[文字数])

で指定しますので、セルB2に入る日付を取り出す計算式は次のものになります。

=LEFT(B2,9)

LEFT関数は、取り出したものを文字列として取り出します。
だから取り出したものは日付として扱っていないでただの文字列になっています。このままでも計算をやってくれるのですが、正式には文字列なので日付の計算には使えないんです。何かしようとしたときに計算できなくなるかも。
きちんとした日付にするためには、文字列を日付にするDATEVALUE関数で今回の計算式を囲みます。

=DATEVALUE(LEFT(B2,9))

計算式を確定すると一回、4万いくつといった、1900年1月1日からの日数の数字で表示されてしまいますが、表示形式を日付にすると日付になっていることがわかります。和暦表示が西暦表示に代わっているのでちゃんと日付データになったことがわかります。

RIGHT関数

右1文字がランクなので、それを取り出すには、右を英語にするとrightなのでRIGHT関数です。

これはLEFTがRIGHTになっただけです。

=RIGHT(文字列,[文字数])

セルD2にセルB2の文字から右1文字を抜き出すには次の計算式です。

=RIGHT(B2,1)

RIGHT関数も文字列で取り出しますが、ランクのアルファベットは文字なのでこのままでいいでしょう。

LEFT関数でもそうだったのですが文字数は省略して計算式を作ることができます。
このときは、文字数を1文字として取り出すので、ランクは左1文字なので省略してもよかったかもしれません。
でも、さとうせんせいは計算式がわかりやすくなるように1文字でも指定してるって言ってました。

MID関数

もし、入荷コードから入荷月の数字だけ取り出したいというときは、文字列の中にある5文字目からの2文字を取り出します。このように文字列の内側から文字を取り出すには、真ん中を表すMiddleの略でMID関数を使います。

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

セルE2にセルB2の文字から月を表す文字を取り出すために5文字目から2文字を取り出すには次の計算式です。

=MID(B2,5,2)

やっぱりMID関数も文字でしか取り出さないので、これを数字にするには、文字を数字にするVALUE関数でMID関数を囲みます。

06の文字を数字にしたので6になりました。

MID関数はどれも省略できません。文字列、開始位置、文字数を指定します。

あと、MID関数の文字数って余計に大きいものを入れても大丈夫なんですよ。
大きく入れても多かった文字はなかったことにいい感じにしてくれるんです。
だから、次のようなアンケートで、アンケート回答の項目には商品番号と感想が書いてますが、感想は5文字目以降で何文字書いてあるかわからないときでも、文字数を100とか大きい値を入れて指定すれば、感想だけ取り出せるんです。

=MID(A2,6,100)

困っちゃう例外

でも実際には何文字目から始まるっていうときと、何の文字の前や後っていう、どこにあるかわからない文字の位置で分けるっていうことがあるので、それはExcelのかなり新しい関数を使っていくしかありません。

TEXTAFTER関数：指定した文字の後の文字を取り出します

TEXTBEFORE関数：指定した文字の前の文字を取り出します

TEXTSPLIT関数：指定した文字で区切って区分けします

また『さとうせんせいは「エチオピアのコーヒーがだいすき」と言いました』という文章から『エチオピアのコーヒーがだいすき』を取り出すのは1つの関数ではできないです。

ただ、LEFT、RIGHT、MID関数とほかの関数をかなり複雑ですが組み合わせることによって抜き出すことはできます。関数の中でもFIND関数は、『「』の文字が10文字目と数えてくれる関数なのでMID関数と合わせやすいです。

ほんと参考までになんですけど、一例ですけど次のようになります。

=MID(LEFT(A1,FIND(“」",A1)),FIND(“「",LEFT(A1,FIND(“」",A1))),100)

もうサイアク。これ作るのに30分かかっちゃいました。

TEXTAFTER関数、TEXTBEFORE関数関数が使えるともう少しわかりやすくなりますが、両方使わないとできません。

まとめ

集計の前の処理として色で分けるとか、取引先で分けるとか、担当者で分けるとかするかもしれないですね。その時、なにかのコード番号として1つのセルに全部入っちゃってると、集計する前にそれをバラシてあげなきゃいけなくて、そんときにこういう文字から文字を取り出す関数が必要になります。
コード番号の中に値段や個数も入っていたら、それは合計することになるので数字に変換するためにVALUE関数で変換する必要もあるのです。日付だったらDATEVALUE関数で、ですね。

販売システムとかからダウンロードしたCSVデータとかは、わりとコード番号にいろんな情報が埋め込まれてる時が多いので、こういう文字を取り出しテクニックは大事です。

またこの関数に代わる機能として、正確性は完ぺきではないですがフラッシュフィル機能や、自動化ができなくなりますが区切り文字機能があります。またこれは大変ですけどデータの取得機能ではかなり高度にデータを切り分けて項目にすることができます。

コード番号に限らず、文字列を取り出したいなということは結構あるので、覚えて活用してくださいね！！