エクセルで数値と文字が入ったセルからの数値の抽出

質問

皆さんこんにちは。

エクセルの使い方についての質問です。
添付画像の例のようにA列に
「XXYYYZZ(XとZは文字、Yは数値で3桁または4桁)」というフォーマットで
数値と文字が入っているセルがあります。
同様にB列には「WWZ(Wは数値)」というフォーマットで、
同様に数値と文字が入っているセルが並んでいます。

これらからDやE列にあるように数値のみを取り出すためには
DやE列にどのような関数を入れてやればよいのでしょうか。
どなたか教えていただければ幸いです。
よろしくお願いします。

回答No.3

a987654 · ベストアンサー

>A列に「XXYYYZZ(XとZは文字、Yは数値で3桁または4桁)」
>B列には「WWZ(Wは数値)」
Ａ列は７ｏｒ８文字Ｂ列は４文字かつ数値は全角半角どちらでも
という前提で
１．ＭＩＤ関数での方法
　Ｄ１に=VALUE(MID(A1,3,LEN(A1)-4))
　Ｅ１に=VALUE(MID(B1,1,2))
２．RIGHT、ＬＥＦＴ関数での方法
　Ｄ１に=VALUE(LEFT(RIGHT(A1,LEN(A1)-2),LEN(A1)-4))
　Ｅ１に=VALUE(LEFT(B1,2))

２行目以降はどちらもオートフィル

ご注意としてVALUE（）の関数は抽出数字（文字列）を確実に
数値にするために入れています。
VALUEを入れないと抜きだし結果は、数字の文字列として
展開されます。
ＥＸＣＥＬの仕様にて数字の文字列でも計算には使えますので
数字であればＶＡＬＵＥ関数なしでも使用可能ですがＶＡＬＵＥ関数で
確実に数値に変換しておく方が宜しいかと思います。
又これにより、全角半角およびその入り混じりにも適応します。

お礼

SUBSTITUTE関数を使っていてでてきた数値が文字列扱いになっていて困っていましたが、
VALUEを使うことで解決できるのがわかって大変ためになりました。
今回はこのご回答をベストアンサーにさせていただきます。
ご回答ありがとうございました。

回答No.6

zap35

A1に「年収330万円」や「40歳」が入力されているとき

=MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&123456789)),LEN(A1)*10-SUM(LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6,7,8,9},""))))*1

で、それぞれ「330」、「40」を取り出すことが出来ます

なお「年収3,300万円」のように「カンマ」で桁区切りが使われている場合の式は

=MID(A3,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A3&123456789)),LEN(A3)*11-SUM(LEN(SUBSTITUTE(A3,{0,1,2,3,4,5,6,7,8,9,","},""))))*1

となります。

でも「年収」「万円」「歳」のように決まった文言であれば＃０１さんの回答にあるように

=SUBSTITUTE(SUBSTITUTE(A1,"万円",""),"年収","")*1

の方が簡単ですし、応用が利くと思いますよ

お礼

皆さんの回答は本当に参考になります。
エクセルの勉強もまだまだしないといけませんね。
今回はご回答ありがとうございました。

回答No.5

tom04

こんばんは！
一例です。
セル内の数値は必ず連続していて飛び飛びで存在しないという前提です。

画像の配置ですと・・・

D1セルに
=IF(A1="","",MID(A1,MATCH(TRUE,ISNUMBER(MID(A1,ROW($1:$20),1)*1),0),COUNT(MID(A1,ROW($1:$20),1)*1)))

これは配列数式になってしまいますので、この画面からD1セルにコピー＆ペーストする場合は
D1セルに貼り付け後、数式バー内でクリック　→　編集可能になりますので
Shift+Ctrlキーを押しながらEnterキーで確定！
数式の前後に｛　｝マークが入り配列数式になります。
これを列方向と行方向にオートフィルでコピーしてみてください。

※　とりあえず、セル内の文字数が20文字まで対応できるようにしています。

以上、参考になればよいのですが・・・m(_ _)m

お礼

いくつかのご回答を見ていますと、今回のケースではMIDを使うのがよさそうですね。
配列については今まで何度かやってみたことがあるのですが、
理屈については今ひとつわからずじまいなのでまた勉強させていただきます。
ご回答ありがとうございました。

回答No.4

imogasi

エクセルの関数では苦手な課題です。適当な関数が無い。
そういうことを覚えないとこんなところへ質問して手間隙かけることになる。
ーー
関数では
http://www.geocities.jp/chiquilin_site/data/050223_numerical_extraction.html
などに解説が有る
関数マニアで無いと、普通の人はアイデアそのものを思いつかないでしょう。
Ｇｏｏｇｌｅで「エクセル関数 数字抽出」で照会。
ーー
１つの方法は、ＶＢＡができれば、１文字ずつ数字かどうかを判定して、数字だけ残せば良い。
標準モジュールに
Function su(a)
st = "n"
For i = 1 To Len(a)
If IsNumeric(Mid(a, i, 1)) Then
s = s & Mid(a, i, 1)
st = "y"
Else
If st = "y" Then
su = Val(s)
Exit Function
End If
End If
Next i
End Function
でも入れて、ユーザー関数として使う。
ーーー
下記例ではＢ１に　=su(A1)　と入れて下方向に式を複写。
例データ
当社1000円 1000
定価1200円 1200
定価１２３５円 0
ーーーー
全角だとうまく行かない。
さらに上記プログラムに手を加える必要がある。
Function su(a)
st = "n"
MsgBox a
For i = 1 To Len(a)
If IsNumeric(Mid(a, i, 1)) Then
s = s & StrConv(Mid(a, i, 1), 8)
st = "y"
Else
If st = "y" Then
su = Val(s)
Exit Function
End If
End If
Next i
End Function
ーー
結果
当社1000円 1000
定価1200円 1200
定価１２３５円 1235
単位が必要な場合は表示形式の設定で揶揄こと。

お礼

過去に何度かエクセルの使い方でここで質問をさせてもらってきたのですが、
その度に関数で処理するよりVBAを覚えたほうがいいのかなと思います。
これを機に少しずつ勉強しようと思います。ありがとうございました。

回答No.2

tamiemon96

A列を　コピーしてください。

コピーした列全体に
　編集　→　置き換え

　　検索する文字列　　年収
　　置換後の文字列　　　　　（何もいれません）

　実行で「年収」が消えます。

　もう一度　編集　置き換え

　　検索する文字列　　万円
　　置換後の文字列　　　　　（何もいれません）

実行で　万円　が消えます。

お礼

実はこの方法は実は真っ先に思いついたのですが、
いかんせん複数のエクセルファイルで同じ事をしたかったので、
置換処理よりテンプレートを使えば処理しやすい関数で、と思って質問させていただきました。
ご回答ありがとうございました。

回答No.1

rukuku

こんにちは

フォーマットが決まっているのなら、
SUBSTITUTE 関数 や
LEFT 関数、RIGHT 関数、MID 関数 （必要に応じて LEN 関数を組み合わせる）
が使えます。

ご質問の画像の例では
D1に
=SUBSTITUTE(SUBSTITUTE(A1,"年収",""),"万円","")
以下オートフィル

E1に
=SUBSTITUTE(B1,"歳","")
以下オートフィル

で出来ます。

お礼

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

SUBSTITUDE関数を使う手は考えてみたのですが、
恥ずかしながら入れ子にする発想がなかったです。
もっと柔軟に考えなければいけませんね、参考になりました。