EXCEL – セルの文字列から数字のみを抽出するLOOKUP関数を分かりやすく解説

EXCEL
この記事は約4分で読めます。
スポンサーリンク

Excelセルに入力された、長さが不特定の「数字以外の文字列+数字」または「数字+数字以外の文字列」から、数字のみを抽出する関数について、きちんと理解するための解説。

例えば、A1セルに「数字以外の文字列+数字」が入力されていて、そこから数字を取り出す関数なら、

=LOOKUP(10^17,RIGHT(A1,COLUMN(1:1))*1)

例えば、A1セルが“タウリン2000”なら、“2000”を取り出す(1を掛けるので、数字は全角でもOK。後に解説)。

その逆で「数字+数字以外の文字列」の場合はRIGHT関数をLEFT関数に置き換えて、

=LOOKUP(10^17,LEFT(A1,COLUMN(1:1))*1)

例えば、A1セルに“35億”と入れれば、“35”を取り出す。

スポンサーリンク

分解して解説

まずは、COLUMN(1:1)

EXCEL
COLUMN(1:1)

EXCELシートの列数(例えばEXCEL2013の場合は、1,2,3…,16384)が入る配列数式です。COLUMN([範囲])関数は、範囲の列番号が横方向の配列として返されます。要するにここでは、A1セルから取り出す数字のランダムな桁数に対応する、1から連続する数字が欲しいだけです。例えば、桁数が5桁以内と決まっているのなら、COLUMN(A1:E1) でも良いし、5桁と決まっているのならCOLUMN関数なんて必要ないのです。COLUMN($1:$1) でも COLUMN(2:2) でも、あるいは、ROW(A:A) でも良いのです。

ちなみに、INDEX(配列,行番号,[列番号])関数で確認すると、第二引数を16835とした場合にエラー #REF! が返りますので、列数は16834であると分かります。

EXCEL
INDEX(COLUMN(1:1),16384)=16834
INDEX(COLUMN(1:1),16385)=#REF!

次に、RIGHT(A1,COLUMN(1:1))

EXCEL
RIGHT(A1,COLUMN(1:1))

RIGHT(文字列,[文字数])関数は、文字列の右から文字数分を取り出します。この場合、A1セルの値の右から、COLUMN(1:1)文字、つまり、1文字,2文字,3文字…,16384文字をそれぞれ取り出して配列とします。この時点では数字ではなく文字列です。

INDEX(配列,行番号,[列番号])関数で確認すると、取り出される内容が分かります。

INDEX(RIGHT(A1,COLUMN(1:1)),1)=0
INDEX(RIGHT(A1,COLUMN(1:1)),2)=00
INDEX(RIGHT(A1,COLUMN(1:1)),3)=000
INDEX(RIGHT(A1,COLUMN(1:1)),4)=2000
INDEX(RIGHT(A1,COLUMN(1:1)),8)=タウリン2000

これに1を掛けて、RIGHT(A1,COLUMN(1:1))*1

EXCEL
RIGHT(A1,COLUMN(1:1))*1

1を掛けることで、文字列を数字に変換します。数字に変換されると先頭の0は表示されなくなります。数字以外の文字が含まれているとエラーとなります。

INDEX(RIGHT(A1,COLUMN(1:1))*1,1)=""
INDEX(RIGHT(A1,COLUMN(1:1))*1,2)=""
INDEX(RIGHT(A1,COLUMN(1:1))*1,3)=""
INDEX(RIGHT(A1,COLUMN(1:1))*1,4)=2000
INDEX(RIGHT(A1,COLUMN(1:1))*1,8)=#VALUE!

完成形、LOOKUP(10^17,RIGHT(A1,COLUMN(1:1))*1)

EXCEL
LOOKUP(10^17,RIGHT(A1,COLUMN(1:1))*1)

LOOKUP(検査値,配列)関数は、検査値が見つからない場合、配列内で検査値以下の最大値が、一致する値とされますので、検査値に取り出す数字の桁数以上の数値を入れます。
10^17は10の17乗ですので、10京(100,000,000,000,000,000)ということになり、必要十分な桁数です。もっと少ない桁数で十分な場合のほうが多いと思います。

ここまでを簡単に説明しますと、文字列の右(左)から、1文字,2文字,3文字…,16384文字と取り出したものの中から、1を掛けてもエラーとならない文字列(つまり数字のみ)を数値に変換して、それらの中から最大桁数のものを返しています。

以上、ご参考になれば幸いです。

EXCEL
スポンサーリンク
スポンサーリンク
J.をフォローする
スポンサーリンク
アトリエJ.

コメント

  1. […] セルの文字列から数字のみを抽出する LOOKUP 関数を分かりやすく解説 | SEBLO […]

  2. J.J. より:

    追記です。他の計算式の中で使う場合などに、数値として扱われない場合は、VALUE()関数で囲っていただくと数値として扱われます。

  3. 斎藤 より:

    非常に有効な関数群をありがとうございます。
    ほぼ希望通りの動作ができるのですが、「001」等の値をそのまま(先頭のゼロを落とさずに)取得したい場合、なんらかの関数で対応可能でしょうか?
    ネット上に情報が見当たらず、もし筆者様が思いつくものがあればぜひご教示いただきたいです。

    • J.J. より:

      コメントありがとうございます。
      「001」は数値ではなく文字列となりますので、この記事の方法ではできないと思います。
      文字列の中での「001」の位置や、「001」以外にどのようなパターンがあるのかにもよりますが、例えば、A1セルに「001」を含む文字列があるとしまして、

      ・A1セルが 001文字列 の場合(前に 001 がある場合)

      =LEFT(A1,LENB(JIS(A1))-LENB(A1))

      ・A1セルが 文字列001 の場合(後に 001 がある場合)

      =RIGHT(A1,LENB(JIS(A1))-LENB(A1))

      ・A1セルが 文字列001文字列 の場合(途中に 001 がある場合)

      =MID(A1,MIN(SEARCH({1,2,3,4,5,6,7,8,9,0},A1&1234567890)),LEN(A1)-(LENB(A1)-LEN(A1)))

      のような感じではどうでしょうか?

      • 斎藤 より:

        J.さま
        まさかこんなにすぐにお返事いただけると思いませんでした!
        結論としましては3つ目の関数で無事望む結果を得ることができました。

        対象のパターンとしては2つ目の文字列にあたるのですが、「文字列」がアルファベットを含むものだったせいで、LENB(今日初めて知った関数です)では正しく計算できないようでした。
        3つ目のほうは含まれる文字列の文字種類に関わらず求める結果が返るようで、こちらを保存させていただきました。
        大量のデータから数字だけいくつも抜き出さなくてはいけない作業がありまして、間違うと大変なことになるものでしたのでとても助かりました!
        コメント欄=質問箱ではないのは百も承知だったので、お返事がいただけなくても当然と思っておりましたのに、こんなに早く、またご親切に回答してくださり本当にありがとうございます。

        • J.J. より:

          いえいえ、お役に立てて嬉しいです。こちらも勉強になり、感謝します。ありがとうございます。

  4. […] EXCELの関数で以下を入力して先頭の9文字を削除します。(参考:https://www.offinet.com/news/entry_62325.html) […]

タイトルとURLをコピーしました