エクセルのセル内の文字列から不定の文字を抜く連続作業の簡単な方法

質問

EXCEL２００２で、セルから特定の文字を抜く連続作業の簡単な方法を教えて下さい。
状況は、セル内に[3枚]という文字があり、また、別のセルには[5個]や[6缶／ケース],[３本／Ｐ],[１００枚]等の情報が大量に有ったとします。ここから、「数字以外の文字」の部分を抜き出して数字だけのセルにする作業をしたいのです。
文字列の長さが不統一なので、困っています。簡単な方法はあるでしょうか？
当方は、マクロやVISUAL BASICなどの知識は或る程度は持っていますが、出来たら関数を使用して出来ないでしょうか？

なお、この質問は以下の質問を参考に致しました。nkndisk 様、お許し下さい。
http://okweb.jp/kotaeru.php3?q=541611

回答No.1

s_yoshi_6 · ベストアンサー

下記ページに書かれていますが、
数字が常に先頭に来る場合は、
=LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
先頭に文字列が入る可能性がある場合は、=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},))))
で抜き出すことができます。

【数字＋文字列から数字のみを取り出す】
http://www2.odn.ne.jp/excel/waza/function.html#SEC74

ただし、上記式はいずれも半角数字にしか対応していませんので、数字に半角全角が混じっている場合は、ASC関数を使って、半角に統一してやる必要があります。

=LEFT(ASC(A1),LEN(A1)*10-SUM(LEN(SUBSTITUTE(ASC(A1),{0,1,2,3,4,5,6,7,8,9},))))
または
=MID(ASC(A1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(ASC(A1),{0,1,2,3,4,5,6,7,8,9},))))

なお、上記で出力される数字は文字列になりますので、計算結果を他の計算に使用したい場合は、さらにVALUE関数を使って文字列を数値に変換して下さい。

=VALUE(LEFT(ASC(A1),LEN(A1)*10-SUM(LEN(SUBSTITUTE(ASC(A1),{0,1,2,3,4,5,6,7,8,9},)))))
または
=VALUE(MID(ASC(A1),MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC(A1)&1234567890)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(ASC(A1),{0,1,2,3,4,5,6,7,8,9},)))))

参考URL：

http://www2.odn.ne.jp/excel/waza/function.html#SEC74

お礼

これですこれです。これを探していました。物凄く助かりました。ありがとうございました。

補足

すみません。
上の数式をそのままコピー＆ペーストで貼り付けると、大丈夫なんですが、自分のシートに合うように改良しようとすると、エラーが出てしまいます。

在庫管理をするシートを作っており、Ｃ４に単位あたりの価格、Ｅ４に単位、Ｆ４に現在の在庫数を打ち込むと在庫品の価格が出るようにしたいのです。

たとえば、”６缶／ケース”で”９００”円の缶ビールが”１０”本有る時の値段を出したいのです。

とりあえず今は、下記の式を使っていますが....
=IF($E4="",$C4*$F4,$C4/VALUE(LEFT(ASC($E4),LEN($E4)-1))*$F4)

これと先ほどの式を組み合わせるとエラーになってしまいます。

よろしくお願いします。

回答No.4

s_yoshi_6

＃１です。
補足に書かれている式
＞=IF($E4="",$C4*$F4,$C4/VALUE(LEFT(ASC($E4),LEN($E4)-1))*$F4)
を拝見しましたが、
＞LEFT(ASC($E4),LEN($E4)-1)
の部分は、E4に入力されている文字列の、左から「LEN($E4)-1」の長さ分を取り出してしまいます。（例えば、”６缶／ケース”であれば、６文字のうちの５文字を取り出して”6缶／ケー”（「ケー」は半角）となります）
もし左から１文字を取り出すのであれば、
LEFT(ASC($E4),1)
とすべきでしょう。

ただ、
＞これと先ほどの式を組み合わせるとエラーになってしまいます。
ということですが、＃１の式を今回のケースに当てはめるのであれば、
=IF($E4="",$C4*$F4,$C4/[参照セルを変更した＃１の式]*$F4)
でいいはずで、上記の部分は関係が無いと思われます。

具体的には、
=IF($E4="",$C4*$F4,$C4/VALUE(LEFT(ASC($E4),LEN($E4)*10-SUM(LEN(SUBSTITUTE(ASC($E4),{0,1,2,3,4,5,6,7,8,9},)))))*$F4)
または、
=IF($E4="",$C4*$F4,$C4/VALUE(MID(ASC($E4),MIN(FIND({0,1,2,3,4,5,6,7,8,9},ASC($E4)&1234567890)),LEN($E4)*10-SUM(LEN(SUBSTITUTE(ASC($E4),{0,1,2,3,4,5,6,7,8,9},)))))*$F4)
でいいはずです。

上記の式で万が一予想と違う結果となった場合、「これと先ほどの式を組み合わせ」されたその数式と、エラーの種類（「#VALUE」など)を補足してもらうと、何らかのアドバイスはできると思います。

お礼

たびたび申し訳有りません。

元々の数式は、単位が”１個”とか、”１０本”しかないと想定して作ったのですが、例のような物が出てきたので困っていたものです。

エラーが出たのは、私の勘違いで、
=IF($B4="","",IF($E4="",$C4*$F4,$C4/”VALUE(LEFT(ASC($E4),LEN($E4)*10-SUM(LEN(SUBSTITUTE(ASC($E4),{0,1,2,3,4,5,6,7,8,9},)))))”*$F4))
上記の””で囲まれた部分にもう一つ閉じ括弧が加わっており、
=IF($B4="","",IF($E4="",$C4*$F4,$C4/VALUE(LEFT(ASC($E4),LEN($E4)*10-SUM(LEN(SUBSTITUTE(ASC($E4),{0,1,2,3,4,5,6,7,8,9},))))))*$F4))
となってしまっておりました。今は正常に動いています。

ただ、SUBSTITUTE()関数を数式入力ウィザードで入れようとすると、『関数の引数が足りません』といった意味のメッセージが出るのですが、気にしなくて良いですよね。正常動作していますから．．．．．．．

勉強になりました。ありがとうございます。

回答No.3

BLUEPIXY

数字で始まる場合は、
以下の様なユーザ定義関数を使うのが簡単・簡明です。
public Function Nvalue(x)
Nvalue = Val(x)
End Function
全角で始まる場合は、ASC関数を使って半角数字にしてから関数を使います。

回答No.2

noname#182251

「その他(コンピューター [技術者向け]) 」に投稿されたことから「エクセル内部で」解決しなくても良いと理解しました。

このような「規則性はあるがバラツキがある」対象を扱うには「正規表現:regular expression」が適当であると悪寒がえます。御存知なければ
http://www.mnet.ne.jp/~nakama/
に解説が。御存知であれば失礼しました(^^;

で、エクセルをcsv形式で保存し、文字列として正規表現で処理する。
このような処理だと、古典的なものでは awk、良く使われるのは、Perl ruby などでしょうか。

この方針であれば、カテゴリーをかえて Perl で問合わせれば、山のようにレスがつくかと。

参考URL：

http://www.mnet.ne.jp/~nakama/

お礼

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

正規表現、ツールによって微妙にルールに違いが有るようなのですが、次の機会に使うために勉強しておきます。