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

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

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

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

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

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

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

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

分解して解説

まずは、COLUMN(1:1)

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であると分かります。

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

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

RIGHT(A1,COLUMN(1:1))

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

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

INDEX(RIGHT(A1,COLUMN(1:1)),1)=０
INDEX(RIGHT(A1,COLUMN(1:1)),2)=００
INDEX(RIGHT(A1,COLUMN(1:1)),3)=０００
INDEX(RIGHT(A1,COLUMN(1:1)),4)=２０００
INDEX(RIGHT(A1,COLUMN(1:1)),8)=タウリン２０００

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

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)

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

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

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