皆さんこんにちは。
エクセルの使い方についての質問です。
添付画像の例のようにA列に
「XXYYYZZ(XとZは文字、Yは数値で3桁または4桁)」というフォーマットで
数値と文字が入っているセルがあります。
同様にB列には「WWZ(Wは数値)」というフォーマットで、
同様に数値と文字が入っているセルが並んでいます。
これらからDやE列にあるように数値のみを取り出すためには
DやE列にどのような関数を入れてやればよいのでしょうか。
どなたか教えていただければ幸いです。
よろしくお願いします。
No.3ベストアンサー
- 回答日時:
>A列に「XXYYYZZ(XとZは文字、Yは数値で3桁または4桁)」
>B列には「WWZ(Wは数値)」
A列は7or8文字B列は4文字かつ数値は全角半角どちらでも
という前提で
1.MID関数での方法
D1に=VALUE(MID(A1,3,LEN(A1)-4))
E1に=VALUE(MID(B1,1,2))
2.RIGHT、LEFT関数での方法
D1に=VALUE(LEFT(RIGHT(A1,LEN(A1)-2),LEN(A1)-4))
E1に=VALUE(LEFT(B1,2))
2行目以降はどちらもオートフィル
ご注意としてVALUE()の関数は抽出数字(文字列)を確実に
数値にするために入れています。
VALUEを入れないと抜きだし結果は、数字の文字列として
展開されます。
EXCELの仕様にて数字の文字列でも計算には使えますので
数字であればVALUE関数なしでも使用可能ですがVALUE関数で
確実に数値に変換しておく方が宜しいかと思います。
又これにより、全角半角およびその入り混じりにも適応します。
SUBSTITUTE関数を使っていてでてきた数値が文字列扱いになっていて困っていましたが、
VALUEを使うことで解決できるのがわかって大変ためになりました。
今回はこのご回答をベストアンサーにさせていただきます。
ご回答ありがとうございました。
No.6
- 回答日時:
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
となります。
でも「年収」「万円」「歳」のように決まった文言であれば#01さんの回答にあるように
=SUBSTITUTE(SUBSTITUTE(A1,"万円",""),"年収","")*1
の方が簡単ですし、応用が利くと思いますよ
No.5
- 回答日時:
こんばんは!
一例です。
セル内の数値は必ず連続していて飛び飛びで存在しないという前提です。
画像の配置ですと・・・
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
- 回答日時:
エクセルの関数では苦手な課題です。
適当な関数が無い。そういうことを覚えないとこんなところへ質問して手間隙かけることになる。
ーー
関数では
http://www.geocities.jp/chiquilin_site/data/0502 …
などに解説が有る
関数マニアで無いと、普通の人はアイデアそのものを思いつかないでしょう。
Googleで「エクセル関数 数字抽出」で照会。
ーー
1つの方法は、VBAができれば、1文字ずつ数字かどうかを判定して、数字だけ残せば良い。
標準モジュールに
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
でも入れて、ユーザー関数として使う。
ーーー
下記例ではB1に =su(A1) と入れて下方向に式を複写。
例データ
当社1000円1000
定価1200円1200
定価1235円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円1235
単位が必要な場合は表示形式の設定で揶揄こと。
過去に何度かエクセルの使い方でここで質問をさせてもらってきたのですが、
その度に関数で処理するよりVBAを覚えたほうがいいのかなと思います。
これを機に少しずつ勉強しようと思います。ありがとうございました。
No.2
- 回答日時:
A列を コピーしてください。
コピーした列全体に
編集 → 置き換え
検索する文字列 年収
置換後の文字列 (何もいれません)
実行で「年収」が消えます。
もう一度 編集 置き換え
検索する文字列 万円
置換後の文字列 (何もいれません)
実行で 万円 が消えます。
実はこの方法は実は真っ先に思いついたのですが、
いかんせん複数のエクセルファイルで同じ事をしたかったので、
置換処理よりテンプレートを使えば処理しやすい関数で、と思って質問させていただきました。
ご回答ありがとうございました。
No.1
- 回答日時:
こんにちは
フォーマットが決まっているのなら、
SUBSTITUTE 関数 や
LEFT 関数、RIGHT 関数、MID 関数 (必要に応じて LEN 関数を組み合わせる)
が使えます。
ご質問の画像の例では
D1に
=SUBSTITUTE(SUBSTITUTE(A1,"年収",""),"万円","")
以下オートフィル
E1に
=SUBSTITUTE(B1,"歳","")
以下オートフィル
で出来ます。
ご回答ありがとうございます。
SUBSTITUDE関数を使う手は考えてみたのですが、
恥ずかしながら入れ子にする発想がなかったです。
もっと柔軟に考えなければいけませんね、参考になりました。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・人生のプチ美学を教えてください!!
- ・10秒目をつむったら…
- ・あなたの習慣について教えてください!!
- ・牛、豚、鶏、どれか一つ食べられなくなるとしたら?
- ・【大喜利】【投稿~9/18】 おとぎ話『桃太郎』の知られざるエピソード
- ・街中で見かけて「グッときた人」の思い出
- ・「一気に最後まで読んだ」本、教えて下さい!
- ・幼稚園時代「何組」でしたか?
- ・激凹みから立ち直る方法
- ・1つだけ過去を変えられるとしたら?
- ・【あるあるbot連動企画】あるあるbotに投稿したけど採用されなかったあるある募集
- ・【あるあるbot連動企画】フォロワー20万人のアカウントであなたのあるあるを披露してみませんか?
- ・映画のエンドロール観る派?観ない派?
- ・海外旅行から帰ってきたら、まず何を食べる?
- ・誕生日にもらった意外なもの
- ・天使と悪魔選手権
- ・ちょっと先の未来クイズ第2問
- ・【大喜利】【投稿~9/7】 ロボットの住む世界で流行ってる罰ゲームとは?
- ・推しミネラルウォーターはありますか?
- ・都道府県穴埋めゲーム
- ・この人頭いいなと思ったエピソード
- ・準・究極の選択
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで2つの数値のうち大きい...
-
エクセルで、2種類のデータを...
-
連続データを1行おきに貼り付け...
-
エクセル大量のセルのデータを...
-
エクセルのまとめてカッコをつ...
-
エクセルに入力した文字列に句...
-
Excel 頭に「0」がついている...
-
【EXCEL】別シートに同じ番号が...
-
WEEKDAY 空白も7になってしまう
-
桁の違う数値での並び替え
-
エクセルで、数字列の中にハイ...
-
エクセル、11と1の区別
-
ある列のセルに特定の文字が入...
-
エクセルで特定の文字が入って...
-
excel: 2行ずつ間隔を空けて関...
-
Excelで日付の異なる時間帯別集計
-
Excelで中央揃えが出来ない?
-
【エクセル】2行に一行のように...
-
Excelで隣のセルと同じ内容に列...
-
エクセルで二つの数字の小さい...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで2つの数値のうち大きい...
-
エクセルで、2種類のデータを...
-
エクセルで特定の文字が入って...
-
エクセルのまとめてカッコをつ...
-
Excelで中央揃えが出来ない?
-
桁の違う数値での並び替え
-
エクセルで、数字列の中にハイ...
-
Excel 頭に「0」がついている...
-
ある列のセルに特定の文字が入...
-
エクセルに入力した文字列に句...
-
連続データを1行おきに貼り付け...
-
Excel 指定行までコピーをおこ...
-
excelで、1つのセルに入ってい...
-
Excel強制終了
-
セル内文章から、英数字のみ取...
-
Excelでセルの高さを保ったまま...
-
Excelの結合でA列とB列の内容...
-
【エクセル】2行に一行のように...
-
excel: 2行ずつ間隔を空けて関...
-
エクセル、11と1の区別
おすすめ情報