エクセル 文字列から数字だけを抽出

質問

エクセル2016の操作について教えて下さい。
セル内の文字列から数字だけを抽出したいです。

例えばAの列にこのような文字列が入っているとします。
高度5km
高度10.1km

この中の数字だけを取り出して隣のB列の同じ行に数字だけを表示させる方法がありましたら教えて下さい。
宜しくお願いします。

回答No.8

bunjii · ベストアンサー

>この中の数字だけを取り出して隣のB列の同じ行に数字だけを表示させる方法がありましたら教えて下さい。
A1セルに文字列の"高度5km"と入力されているとします。
A2セルに同様に"高度10.1km"と入力されているとします。
B1セルに次の数式を入力すれば数値の5が代入されます。
=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},$A1&1234567890)),MAX(INDEX(ISNUMBER(MID($A1,{1;2;3;4;5;6;7;8;9;10},1)*1)*ROW($A$1:$A$10),0))-MIN(FIND({0;1;2;3;4;5;6;7;8;9},$A1&1234567890))+1)+0
B1セルをB2セルへコピーするとB2セルへは数値の10.1が代入されます。
但し、A1セルの文字列長が10文字までに対して有効です。
A列の文字列が11文字以上のときはROW($A$1:$A$10)を文字列の長さに合わせて修正してください。
不特定の長さに対応させる方法もありますので要望がありましたら補足してください。

考え方はA1セルの文字列中に数字の"0"～"9"までの文字が何番目に出現するかをチェックし、数値化すべき文字列の先頭位置が最小位置であり、数値化すべき文字列の長さが最大位置-最小位置+1になります。
A1セルの文字列"高度5km"の数字は3桁目に出現します。
MIN(FIND({0;1;2;3;4;5;6;7;8;9},$A1&1234567890)) → 3
MAX(INDEX(ISNUMBER(MID($A1,{1;2;3;4;5;6;7;8;9;10},1)*1)*ROW($A$1:$A$10),0)) → 3
因って、A1セルの文字列から切り出す文字列としての数字は次のようになります。
=MID(A1,MIN(FIND({0;1;2;3;4;5;6;7;8;9},$A1&1234567890)),MAX(INDEX(ISNUMBER(MID($A1,{1;2;3;4;5;6;7;8;9;10},1)*1)*ROW($A$1:$A$10),0))-MIN(FIND({0;1;2;3;4;5;6;7;8;9},$A1&1234567890))+1)+0 → MID(A1,3,3-3+1)+0 → MID(A1,3,1)+0 → 5
MID関数の戻り値は文字列の数字なので数値化するために0を加算しました。
Excelでは数値化できる文字列は加減乗除で数値化できます。
つまり、文字列に0を加減算するか1の乗除算で数値化できます。（VALUE関数の代用）

お礼

とても詳しく回答を下さいましてありがとうございます。
関数を組み合わせる方法をご教示下さり、大変助かっております。
エクセルは最近始めたばかりなのですが、ご教示下さいましたように関数を組み合わせることが出来るのでありましたら、ちょっとしたプログラムのように使うことが出来そうです。
思ったよりも高機能なので機能を存分に活用するべく勉強して参る所存であります。
この度は本当にありがとうございました。
心より感謝申し上げます。
失礼致します。

回答No.9

kagakusuki

アドバイスですが、ここに回答されて来た各回答の関数について、A列のセルに入力されている値が

高度4.321km

などの様なものである場合に正しい結果が表示されるかどうかも確認しておかれる事をお奨め致します。
　またその他にも、A列のセルに何も入力されていない場合や、「高度km」などの様に数値が含まれていない場合に関しても確認しておかれた方が良いでしょう。

お礼

セルに何も入力されていない場合や文字列の場合には、エクセルが固まってしまうことがあるようです。
なるべく正確を期して入力をした方が良さそうだと思いました。
気を付けようと思います。
回答を下さいましてありがとうございました。

回答No.7

kagakusuki

高度5km
高度10.1km
最大高度1234.56789mile

などといった文字列が例えばA1セルに入力されている場合において、

5
10.1
1234.56789

などの数値部分をB1セルに表示されセルのでしたら、B1セルに次の様な関数を入力して下さい。

=IFERROR(MID($A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A1&1234567890)),MAX({1,2,3,4,5,6,7,8,9,10,11,12,13,14,15}*ISNUMBER(MID($A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},$A1&1234567890)),{1,2,3,4,5,6,7,8,9,10,11,12,13,14,15})+0)))+0,"")

お礼

回答をありがとうございます。
ご教示下さいました方法で小数点以下どのような桁数でも抽出することが出来ました。
このような高度な関数を使えるようになりたいてず。
時間はかかるかもしれませんが、勉強して参る所存であります。
この度は、本当にありがとうございました。

回答No.6

webuser

＞その文字を外して得られた数字を隣のセルに表示させる方法はありますでしょうか？

式を隣のセルに書けば隣に表示されるし、
２０個右のセルに書けば２０個右に表示されるだけでしょう。

お礼

隣に限らず、式を記入した列に表示させることが出来ました。
ありがとうございました。

回答No.5

aokii

B1セルに以下の式を記入して下にドラッグコピーしてみて下さい。
=MAX(TEXT(MID(A1,{1,2,3,4,5},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}),"標準;;0;!0")*1)

お礼

再度の回答をありがとうございます。
ご教示下さいました方法で数値のみ抽出することが出来ました。
この度は本当に有難うございました。

回答No.4

Prome_Lin

あまり、いい回答ではありませんが、

=MID(A1,3,LEN(A1)-4)

セル「A1」の３番目の文字から、セル「A1」の文字列の長さ－４（すなわち、「高度」と「km」の４文字を削除した長さ）。

邪道ですので、無視して頂いて結構です。

お礼

回答ありがとうございました。

回答No.3

aokii

=MAX(TEXT(MID(A1,{1,2,3,4,5},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}),"標準;;0;!0")*1)

お礼

回答を下さいまして、ありがとうございます。
このようにする方法がありますこと、大変勉強になっております。
この度はご教示下さいまして本当にありがとうございました。

補足

回答ありがとうございます。
この式をどこに書き込めばいいでしょうか？^^;

回答No.2

webuser

抽出に拘るなら↓こんなの
http://qiita.com/mhara/items/82421d1b34e88a3efba1

お礼

教えて頂いた先、読みます。
回答をありがとうございました。

回答No.1

webuser

単位のように、まとわりつく文字が決まっているなら、それを外した方が簡単ですけどね。
=SUBSTITUTE(SUBSTITUTE(A2,"高度",""),"km","")

お礼

そのような方法もあるのですね。
これはA列の文字を外す方法なのですね。
その文字を外して得られた数字を隣のセルに表示させる方法はありますでしょうか？
回答ありがとうございました。