皆さんこんにちは。
エクセルの使い方についての質問です。
添付画像の例のように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で質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel2019 列と列(2列)の数値の重複を調べたい 1 2023/05/11 13:35
- Excel(エクセル) Excelの文字列を数字に変換する方法について 6 2023/07/31 21:18
- Excel(エクセル) エクセルでセルに何らかの文字が入力されたらそれを任意の数値として認識させる方法がしりたいです。 3 2023/03/16 20:19
- Excel(エクセル) countif関数について質問 4 2022/06/14 12:11
- Visual Basic(VBA) 列を指定して値を左から5文字にそろえる 1 2022/06/10 20:28
- Excel(エクセル) Excelの関数についておしえてください。 3 2023/04/20 18:36
- Excel(エクセル) エクセル 自動計算 1 2023/01/30 13:28
- Visual Basic(VBA) VBAで、特定の文字より後を削除して残った数値を文字列に変換と特定の文字より前も削除したい 3 2022/04/15 19:21
- Visual Basic(VBA) A列にある値をB列・C列にVBAで切り出し 3 2022/04/09 19:20
- Visual Basic(VBA) エクセルVBAで『A列』に新規で数値を入力し『B列』から右方向の空白セルにその値を貼り付ける方法 4 2022/11/05 08:37
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
EXCELで2つの数値のうち大きい...
-
エクセルで、2種類のデータを...
-
Excelで中央揃えが出来ない?
-
エクセルで特定の文字が入って...
-
エクセルに入力した文字列に句...
-
Excelの結合でA列とB列の内容...
-
桁の違う数値での並び替え
-
Excelのデータ間引きについて
-
Excel 頭に「0」がついている...
-
ある列のセルに特定の文字が入...
-
連続データを1行おきに貼り付け...
-
excelで、1つのセルに入ってい...
-
WEEKDAY 空白も7になってしまう
-
Excel 指定行までコピーをおこ...
-
エクセル、11と1の区別
-
Excel強制終了
-
エクセルのまとめてカッコをつ...
-
数値の列から偶数のみを抽出す...
-
エクセルで、数値の範囲で仕分...
-
【EXCEL】別シートに同じ番号が...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
EXCELで2つの数値のうち大きい...
-
エクセルで、2種類のデータを...
-
ある列のセルに特定の文字が入...
-
Excelで中央揃えが出来ない?
-
エクセルで特定の文字が入って...
-
セル内文章から、英数字のみ取...
-
Excel 頭に「0」がついている...
-
エクセルのまとめてカッコをつ...
-
桁の違う数値での並び替え
-
エクセルに入力した文字列に句...
-
Excel強制終了
-
Excel 指定行までコピーをおこ...
-
Excelの結合でA列とB列の内容...
-
excelで、1つのセルに入ってい...
-
エクセルで、数字列の中にハイ...
-
連続データを1行おきに貼り付け...
-
エクセルの関数
-
【エクセル】2行に一行のように...
-
WEEKDAY 空白も7になってしまう
-
VLOOKUPのあいまい検索(スペー...
おすすめ情報