文字列から複数の数値を抽出するには【Excel】

またYahoo!知恵袋にあった質問から。一つの文字列の中に複数の数値が入っていた場合、どうやって２個目、3個目の数値を抽出したらよいか？という質問です。

質問の内容

Excel2016の関数についてです。

同一セル内の数字を順番に抽出したいです。

A１

あああ：ABC 45(あいうえお：8.70ｍｍ、あい：0.366kg)

↓

A2

45

A3

8.70

A4

0.366

A2に下記式で最初の数字を抽出できたのですが、次の数字からどうやってやるのか見当がつきません。

=LOOKUP(10^10,MID(A1,MATCH(0,INDEX(0/MID(A1,COLUMN($1:$1),1),),),COLUMN($1:$1))*1)

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12266059092

解析

正直言ってこういうのは私も全然分かりませんので、与えられたヒントをもとに、解析していきます。数式が何をしているかわかれば、おのずと答えも出てくると思うのです。

質問者が提示した、LOOKUP関数で始まる長い数式ですが、これを一つ一つ丁寧に細かく分けて分析していきます。

そうすると、何をしているかが分かるので、あとは質問者の要望に沿った形に修正していけばよいということになります。

COLUMN($1:$1)

まず最初にCOLUMN($1:$1)をExcelに落とし込んで、見てみます。

なるほど。1行目全体を指定することで、列番号を拾ってきているんですね。

MID(A1,COLUMN($1:$1),1)の部分

続いて、MID(A1,COLUMN($1:$1),1)の部分を見ていきます。

COLUMN($1:$1)の部分はすでにセルA3に落とし込んであるので、参照先を変えます。

COLUMN($1:$1)の部分は、行番号3のところに変えました。

3:3と手入力してもよかったのですが、セルA3をアクティブにして行末までを範囲選択して指定する方法をとったら、勝手にA3#とスピルしてくれました。便利♪

列番号が文字列の中の開始位置指定となり、抽出する文字数は1なので、結果行番号4にセルＡ1の文字列が1文字ずつ展開されました。

数式「0/MID(A1,COLUMN($1:$1),1)」の部分

続いて、INDEX関数に移ってもよかったのですが、0/MID(A1,COLUMN($1:$1),1)という数式部分が私には理解できなかったので、これも分割して見てみました。

落とし込んだところで、一目瞭然でした。なるほど、数字のところは0、文字のところは#VALUE!エラーになるんですね。

ということは、ここで文字か数値かを判断しているのかなという予想がつきます。

しかし、ここで疑問点が一つ。

数値0のところでは、DIVエラーが発生しています。

そうすると、0を割って数値かどうか判断するやり方はあまりうまいやり方ではないのかなという気がします。ExcelにはVALUE関数という、数値を表す文字列を数値に変換してくれる関数が用意されていますので、そっちを使った方がよいのではないか？と思いました。

まあ、まだ結論を出すのは早いので、続きを見ていきます。

INDEX(0/MID(A1,COLUMN($1:$1),1),)の部分

つづいて、INDEX(0/MID(A1,COLUMN($1:$1),1),)の部分を見ていきます。

なんと、セルA5に入れたのと同じ結果になりました。

無駄な関数ですね＾＾；

これ、どこかからかコピペしてきたのかな？ということがこれで分かります。

汎用化のために入っている関数なのでしょう。今回のこの文字列の場合は、あってもなくても結果はいっしょでした。

MATCH(0,INDEX(0/MID(A1,COLUMN($1:$1),1),),)の部分

さらにMATCH(0,INDEX(0/MID(A1,COLUMN($1:$1),1),),)の部分を見ていきます。

要は、行番号6に入っている配列の中から、0という値に一致する配列要素番号を抽出する関数なので、9番目の0がヒットして9という値が返ってきています。

これ、もし先頭にある数値が０.45という小数点のある数値だったら、いきなり不具合になるってことですね。あまり、汎用性のない抽出方法だなあ・・・。使いたくないな・・・・。

もう一つのMID関数の部分

さらに、もう一つのMID関数部分を見ていきます。

セルA1に入っている文字列のうち9番目の一から始まる文字列を、列番号数分抽出する結果となっていることが分かります。

セルA8には4だけ、セルB8には45、セルC8には45(、セルD8には45(あという風に、徐々に文字数が増えていっています。

なるほど、これで数値が何桁あるか把握しようとしているのでしょう。

文字列*1の部分

*1というのは、文字列を簡単に数字にする便利な数式ですので、これは確認するまでもないのですが、一応ちゃんと見ておきます。

LOOKUPの部分

最後にLOOKUP関数部分を見てみます。

LOOKUP関数というのは近似値をざっくりととってくる関数として使っているようです。

10^10というのは、10の10乗です。想定される、数値を十分に把握できる程度の大きな数値というだけの意味でしょうね。別にここは(今回のケースに限って言えば)10000でもよかったし、100でもよかったのでしょう。

今回候補が二つあり、4か45かでした。できるだけ大きい値を指定して抽出すれば、正解の45が抽出できるという寸法です。

LOOKUP関数のうまい使い方だなあと思いました。こんな使い方もあるんですね。

以上、解析終わりました。

だいたい分かったので、あとはどうやって質問者の要望に答えるかですが、まず途中で気が付いたように、0を文字列で割るのはやめます。あれはバグの元ですね！

それから、LOOKUPだと1個しか抽出できないので、数値は全部抽出してしまって、あとから必要な数値を取り出す方式にした方がよいと思いました。そうすると、TRIMとかTEXTJOINとかCONCATNATEとかの出番かな？？

回答

いったん、「45 8.70 0.366」という文字列にしてから、FINDとMIDで8.70、0.366を抽出するやり方なら、ご紹介できます。

=TRIM(CONCAT(IF(IF(ISERROR(VALUE(MID(A1,COLUMN($1:$1),1))),IF(ASC(MID(A1,COLUMN($1:$1),1))=”.”,ASC(MID(A1,COLUMN($1:$1),1)),””),VALUE(MID(A1,COLUMN($1:$1),1)))=””,” “,IF(ISERROR(VALUE(MID(A1,COLUMN($1:$1),1))),IF(ASC(MID(A1,COLUMN($1:$1),1))=”.”,ASC(MID(A1,COLUMN($1:$1),1)),””),VALUE(MID(A1,COLUMN($1:$1),1))))))

↑これで「45 8.70 0.366」が抽出できるはず。
この数式を例えばA6セルに入れてあるとします。

そしたらそのあとセルA3 に
=VALUE(MID(A6,FIND(” “,A6)+1,FIND(” “,A6,FIND(” “,A6)+1)-FIND(” “,A6)-1))
と入れると8.7が抽出できます(8.70と文字列で抽出したいときは冒頭部分のVALUE関数を取り除いてください)。

セルA4には
=VALUE(MID(A6,FIND(” “,A6,FIND(” “,A6)+1)+1,1000))
と入れると0.366が抽出できます。

Excelには文字列を分割するような便利な関数は(今のところ)ないので、文字列の中に4個目の数値が入っていたら、さらに4個目を分割するための数式が必要となります。今回は3個だけだったので、このやり方にしました。

https://detail.chiebukuro.yahoo.co.jp/qa/question_detail/q12266059092

回答の解説

回答で提示したのは、一行にしてしまっているので何が何だか分からないと思います。

解析のときにやった手法の逆バージョンをやっています。

1行ずつ試していき、最終的に目的の形になったところで、これまでの1行ずつ複数に分かれている数式を1つにまとめる作業をしています。

この1行ずつ試していったときに作ったExcelを共有にあげておきます。

興味があったらご覧ください。

https://docs.google.com/spreadsheets/d/1Grl7wCdTIaVHvfwXpvl2GFldAL0GHysE/edit?usp=sharing&ouid=106480533414957352105&rtpof=true&sd=true

一応さらっと解説しておくと、今回小数点があるので、途中でASC関数をかませています。これは、小数点が2バイト文字だったときの対応のためです。例示では半角文字だったので、いらないかな？とも思いましたが、念のため入れました。

数値かどうかはVALUE関数で把握しています。

IF文の入れ子で、数値かどうか、数値なら抽出する、数値でないなら小数点かどうか、小数点なら抽出する、そうでないなら” “(半角スペース)として抽出します。その後、それらの文字列をCONCATして連結し、TRIMで複数の空白を1つの空白にしています。

そうすると「45 8.70 0.366」という空白で区切られた文字列が得られるので、あとはこの文字列から、空白を目印にFIND関数とMID関数とで目的の数値を取り出します。

区切り文字を目印に文字列を抽出する手法はあちこちのサイトに紹介されているので、ここでは省略します。

まとめ(編集後記)

今回一番困ったのが、「45 8.70 0.366」にしてから3つに分割する方法でした。VBAならおなじみSplitがありますので簡単なんですが、Excelの数式だけでやるとなると面倒ですね。

質問者たちの環境がどのようなのか知る由もないのですが、毎度感じるのは、「これ数式でやる必要ある？VBAでいいんじゃね？」ということです。

文字列の中に含まれる数値を見つけ出すこと自体も、こんな面倒くさい数式でやらなくても、VBAで正規表現を使って一瞬で抽出できるよな・・・と思いました。

しかし、かつて私がそうだったように、質問者のおかれている環境は不自由で制限の多いところなのかもしれません。

以前私は日本年金機構のパート募集に応募したのですが、面接で「VBAが得意です！」と言ったら、「うち(日本年金機構)は以前データ流出があってから、そういうプログラミング系はことごとく使えないようになっているからいらない」と言われ、あえなく不採用となりましたorz

地方公務員だったときも不自由でした。職場ではインターネットに接続はできませんでした(個人情報を扱うので、不用意に外部接続できない仕組みでした)。みんなそれでは仕事にならないから、自前のケータイでインターネットの情報を検索して調べ物をしていたものです。毎月のネット代がすごいことになっていました。

知恵袋で質問してくる人も、そういった制限された環境の人なのかもしれません。

だから、なるべく質問者の要望に応えるようにと思って、日々せっせと回答しています。