エクセルで小数を含む数値の抽出

質問

セルに(1.5g)や(20g)や(1.0kg)と入力されていて、括弧やｇを除いた数値の1.5と20と1.0を抽出する方法はあるのでしょうか？

よろしくお願い致します。

回答No.10

KURUMITO · ベストアンサー

回答No9です。
B1セルには次の式を入力して式を確定する段階でCtrl+Shift+Enterで確定します。その式を下方にオートフィルドラッグします。

=IF(A1="","",MIN(IF(ISNUMBER(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1))),FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)),100)))

C1セルへはNo9の式を入力し下方にオートフィルドラッグします。

お礼

お返事が遅くなり申し訳ございません。

こちらを参考にさせていただきたいと思います！！
何度も何度もありがとうございました。

正直、こんなに多くのご回答を頂けると思っていませしたので、
とても感激しています。

ご回答を頂きました皆様、本当に本当にありがとうございました。
頑張ります！！

回答No.13

kagakusuki

回答番号ANo.7です。
　失礼しました、回答を入力している間に、補足によって新たな情報の追加があった様です。

>括弧がないこともあるし、必ず2番目から数字が入力されているわけではなく、

　それでしたら、作業列を設けて、別のセルで、文字列中で数字が最初に現れる位置を、求めておくと言う方法が使えます。

　今仮に、Ｓｈｅｅｔ1のA1セルに元となる「括弧で括られた単位付きの数値」を表す文字列データーが存在するものとします。
　又、Ｓｈｅｅｔ2のA列を作業列として使用して、Ｓｈｅｅｔ1のB1セルに、取り出した数値を表示させるものとします。

　まず、Ｓｈｅｅｔ2のA1セルに次の数式を入力して下さい。

=SUMPRODUCT((ISNUMBER(FIND("0",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("1",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("2",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("3",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("4",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("5",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("6",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("7",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("8",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))+ISNUMBER(FIND("9",LEFT(ASC(Sheet1!$A1),ROW(INDIRECT("Z1:Z"&LEN(Sheet1!$A1))))))=0)*1)+1

　次に、Ｓｈｅｅｔ2のA1セルをコピーして、Ｓｈｅｅｔ2のA2以下に貼り付けて下さい。
　次に、Ｓｈｅｅｔ1のA1セルに次の数式を入力して下さい。

=IF(ISNUMBER(MID($A1,Sheet2!$A1,1)+0),MID($A1,Sheet2!$A1-(MID("+"&ASC($A1),Sheet2!$A1,1)="-"),SUMPRODUCT(ISNUMBER(MID($A1,Sheet2!$A1,ROW(INDIRECT("Z1:Z"&LEN($A1)-Sheet2!$A1)))+0)*1)+(MID("+"&ASC($A1),Sheet2!$A1,1)="-"))+0,"")

　次に、Ｓｈｅｅｔ1のA1セルをコピーして、Ｓｈｅｅｔ1のA2以下に貼り付けて下さい。

　これで、数字の前に文字が何文字も存在している場合や、数値の前には文字がついていない場合、数値が何桁もある場合、数値が負の数値である場合、数値の後に何文字も文字が続く場合等でも、取り出した数値をＳｈｅｅｔ１のB1セルに表示させる事が出来ます。

お礼

お返事が遅くなり申し訳ございません。

すばらしいのですが、別シートだと私には難易度が高いです。
何度もご回答頂いたのに申し訳ございません。
ありがとうございました。

回答No.12

某HN クロメート(Chromate)（@CoalTar）

負がない、数字がひとまとまり の条件下で
=LOOKUP(8^3^8,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,16})*1)
左から数えて最初の数値を検索し、そこから１桁、2桁、3桁・・・16桁分抽出
数値として読み取れる一番大きな桁数のものを返す

お礼

お返事が遅くなり申し訳ございません。

全角だとダメな感じでしょうか？
ご回答ありがとうございました。

回答No.11

MackyNo1

参考までに

配列数式は多用すると再計算に時間がかかり動きが重くなりますが、前回提示した式よりも計算負荷を少なくするなら、以下のように配列の範囲を小さくした数式の方が良いと思います（この場合もCtrl+Shift+Enterで確定）。

=MAX(IF(ISNUMBER(LEFT(MID(A1,MATCH(0,-ISERR(-MID(A1,ROW($1:$30),1)),),9),ROW($1:$9))*1),LEFT(MID(A1,MATCH(0,-ISERR(-MID(A1,ROW($1:$30),1)),),9),ROW($1:$9))*1,0))

ちなみに上記の数式は、30文字までの文字列に対して、数字部分が9桁（小数点の数字の場合は数字8個まで）の数字を抽出する式になっています。

お礼

お返事が遅くなり申し訳ございません。

数字がほしいところが0になってしまうところがありました。
ご回答ありがとうございました。

回答No.9

KURUMITO

回答No2,4です。
作業列をB列に設け、B1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(A1="","",MIN(IF(ISNUMBER(FIND(0,ASC(A1))),FIND(0,ASC(A1)),100),IF(ISNUMBER(FIND(1,ASC(A1))),FIND(1,ASC(A1)),100),IF(ISNUMBER(FIND(2,ASC(A1))),FIND(2,ASC(A1)),100),IF(ISNUMBER(FIND(3,ASC(A1))),FIND(3,ASC(A1)),100),IF(ISNUMBER(FIND(4,ASC(A1))),FIND(4,ASC(A1)),100),IF(ISNUMBER(FIND(5,ASC(A1))),FIND(5,ASC(A1)),100),IF(ISNUMBER(FIND(6,ASC(A1))),FIND(6,ASC(A1)),100),IF(ISNUMBER(FIND(7,ASC(A1))),FIND(7,ASC(A1)),100),IF(ISNUMBER(FIND(8,ASC(A1))),FIND(8,ASC(A1)),100),IF(ISNUMBER(FIND(9,ASC(A1))),FIND(9,ASC(A1)),100)))

これで数値が文字列の何番目から始まっているかがわかります。
そこで答えですがNo4の式を利用します。C1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ISNUMBER(MID(A1,B1,5)*1),MID(A1,B1,5)*1,IF(ISNUMBER(MID(A1,B1,4)*1),MID(A1,B1,4)*1,IF(ISNUMBER(MID(A1,B1,3)*1),MID(A1,B1,3)*1,IF(ISNUMBER(MID(A1,B1,2)*1),MID(A1,B1,2)*1,IF(ISNUMBER(MID(A1,B1,1)*1),MID(A1,B1,1)*1,"")))))

お礼

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

回答No.8

MackyNo1

＞括弧がないこともあるし、必ず2番目から数字が入力されているわけではなく、文字列の間に数字がある場合もあるのです。

数式は簡略化できる可能性がありますが、文字列にはさまれた数字を取得するなら以下のような数式で表示できます。

=MID(LEFT(A1,MAX(ISNUMBER(MID(A1,COLUMN(1:1),1)*1)*COLUMN(1:1))),MIN(IF(ISNUMBER(MID(LEFT(A1,MAX(ISNUMBER(MID(A1,COLUMN(1:1),1)*1)*COLUMN(1:1))),COLUMN(1:1),1)*1),COLUMN(1:1),"")),10)*1

配列数式ですので入力後Ctrl+Shift+Enterで確定してください。

お礼

お返事が遅くなり申し訳ございません。

すみません。
エラーになってしまうところがあってダメでした。
ご回答ありがとうございました。

回答No.7

kagakusuki

括弧の種類や、単位の種類、数字の桁数には影響されない方法です。
　但し、6.022×10^23mol^-1や2.99792458E8m/sといった、指数で表示された数値には対応しておりません。
　又、元となる「単位付きの数値」が、必ず括弧等の「数字やマイナス記号ではない」文字から始まっている場合には対応していますが、最初の文字が数字となっている文字列に対しては、対応しておりません。
　今仮に、A1セルに元となる「括弧で括られた単位付きの数値」を表す文字列データーが存在するものとします。
　その場合には、数値のみを取り出す関数は、次の様なものになります。

=IF(ISNUMBER(MID(A1,2,1+(ASC(MID(A1,2,1))="+")+(ASC(MID(A1,2,1))="-"))+0),MID(A1,2,SUMPRODUCT(1*ISNUMBER(MID(A1,2,ROW(INDIRECT("Z1:Z"&LEN(A1)-2)))+0))+(ASC(MID(A1,2,1))="-")+(ASC(MID(A1,2,1))="+"))+0,"")

お礼

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

回答No.6

tom04

こんばんは！
VBAでの一例です。

一文字ずつ舐めるように検索していき、半角数字・ピリオドだけ表示するようにしてみました。

画面左下にある操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてマクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub test() 'この行から
Dim i, j As Long
Dim str, buf As String
For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
For j = 1 To Len(Cells(i, 1))
str = Mid(Cells(i, 1), j, 1)
If str Like "[0-9]" Or str = "." Then
buf = buf & str
End If
Next j
Cells(i, 2) = buf
buf = ""
Next i
End Sub 'この行まで

尚、データはA1セルからあるとしていますので、仮にA2セルからデータがある場合は
＞For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
の部分の「1」を「2」に変更してマクロを実行してみだください。

※　仮に1.0のように小数点以下が0になった場合は整数部のみ表示されてしまいます。

他に良い方法があればごめんなさいね。m(_ _)m

お礼

お返事が遅くなり申し訳ございません。

このような感じが理想でした！！
ですが、全角の場合もあり、データが数万あるので少し重いかもです。
せっかく考えてくださったのに申し訳ございません。

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

回答No.5

keithin

特に何も使用上の制限がないマクロを使った方法です。

ALT+F11を押す
現れた画面で挿入メニューから標準モジュールを挿入する
現れたシートに下記をコピーする

Function getnum(ByVal target As Variant)
　Dim i
　For i = 1 To Len(target)
　If IsNumeric(Mid(target, i, 1)) Then
　getnum = Val(StrConv(Mid(target, i, 15), vbNarrow))
　Exit Function
　End If
　Next i
End Function

ファイルメニューから終了してエクセルに戻る
A1セルに言葉が記入してあるとすると
=getnum(A1)
のように計算させる。

＃マクロはやっぱりイヤなときは
=IF(A1="","",LEFT(MID(A1,2,15),MAX(IF(ISNUMBER(MID(A1,ROW($A$1:$A$15),1)*1),ROW($A$1:$A$15)-1)))*1)
といった数式を「コントロールキーとシフトキーを押しながらEnterで入力」（＜必ず忘れずこう操作すること）します

お礼

お返事が遅くなり申し訳ございません。

思った通りにはいきませんでしたが、
ご回答ありがとうございました。

回答No.4

KURUMITO

回答No2です。
B1セルには次の式を入力して下方にオートフィルドラッグします。

=IF(ISNUMBER(MID(A1,2,5)*1),MID(A1,2,5)*1,IF(ISNUMBER(MID(A1,2,4)*1),MID(A1,2,4)*1,IF(ISNUMBER(MID(A1,2,3)*1),MID(A1,2,3)*1,IF(ISNUMBER(MID(A1,2,2)*1),MID(A1,2,2)*1,IF(ISNUMBER(MID(A1,2,1)*1),MID(A1,2,1)*1,"")))))

この式でしたら単位がどのように変わろうとも問題ありません。ただし数値の部分の文字数は小数点を含めて5つまでです。6つまでの場合にはさらに追加してください。

お礼

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

補足

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

すばらしいです！！
ほぼ思い通りになりました。
ですが、また言葉が足りなくて申し訳ありませんが、
括弧がないこともあるし、必ず2番目から数字が入力されているわけではなく、
文字列の間に数字がある場合もあるのです。（各1個など）

やはり無理難題ですね。
何度もお答え頂きありがとうございました。
助かりました！！

回答No.3

keithin

今データが入っている列(セル範囲)を選択して右クリックしてセルの書式設定のユーザー設定で
(G/標準"g")
などのように設定を追加して設定しておきます

今データが入っている列(セル範囲)を選択してCtrl+Hで置換のダイアログを出し
g)
を
何も入力しない
に全て置換します

さらに
(
を
何も入力しない
に全て置換します

以上で数値を取りだした上で今のままの表示で，ふつーに合計等も計算できる方式にやり替えできました。

お礼

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

補足

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

言葉が足りなくて申し訳ありませんが、出来れば関数、最悪マクロで処理したいと考えております。