質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

新規登録して質問してみよう
ただいま回答率
85.51%

Q&A

5回答

6662閲覧

エクセル関数で数字のみを塊で抜き出したい

hiroman

総合スコア6

0グッド

1クリップ

投稿2017/09/08 07:55

###前提・実現したいこと
A列:数字(半角・全角を含む1文字以上)+文字列(漢字・半角全角英字等)から
※想定パターン(A1~A3セルの3パターン)
B列:A列右から最初の数字(半角・全角を含む1文字以上)の塊のみを抜き出したい
※数字の抜き出す塊は半角数字希望

【下記完成イメージ】A列:元データ、B列:抜き出す数値の塊
A1=111AbCd(222AbCd333AbCd) B1=333
A2=111AbCd(222AbCd333) B2=333
A3=111AbCd B3=111



A1000

気になる質問をクリップする

クリップした質問は、後からいつでもMYページで確認できます。

またクリップした質問に回答があった際、通知やメールを受け取ることができます。

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PineMatsu

2017/09/08 08:08

完成イメージをみると抜き出してB列に入れたい数字というのは文字列の後ろから探してはじめに出てきた数字の部分ということでしょうか?
hiroman

2017/09/08 08:10

その通りです。数字の塊を抜き出したいです。
coco_bauer

2017/09/08 08:12

マクロ(VBA)なら出来るでしょうが、エクセル関数だけでは無理かと。
PineMatsu

2017/09/08 08:13

いや、例えば示されているイメージのA1だと「111」「222」「333」が数字の塊になりますが、B1=333になっているということは111と222は抜き出さないということでいいのですか?という質問です。
hiroman

2017/09/08 08:18

B1=333 以外は抜き出しません。
PineMatsu

2017/09/08 08:23

くどいですが、333以外は抜き出さないというのは、数字の塊が複数ある時はその最後に現れた数字の塊なのか?塊の数字の中で一番大きな数字の塊なのか?どちらでしょうか?イメージ例だけだとどちらでも結果が同じになります。こういうことは厳密に定義しておく必要があります。それと、coco_bauerさんの言うように、VBAならできますがエクセル関数だけでは無理だと思います。
hiroman

2017/09/08 08:28

最後に現れた数字の塊になります。
PineMatsu

2017/09/08 08:38

処理はVBAで良いのでしょうか?
hiroman

2017/09/08 08:53

申し訳ありませんがどうしても関数が必要なので質問を諦めます。
hiroman

2017/09/08 08:54

PineMatsuさん、coco_bauerさん ありがとうございました。申し訳ありませんでした。
退会済みユーザー

退会済みユーザー

2017/09/08 10:45

C2以降を使っていいならできそうです。結構いい線まで来てます。もしできたら投稿します。Excel関数のちょっときもい使い方ですが...
guest

回答5

0

私も頭の体操しようとしたら、大ハマりで。質問してやっと何とか。
折角なので、こちらで結果だけ纏めておきます。
もっと、簡潔にできそうな気もしますが、取り敢えず。

一応確認のために、別セルでの式にしていますので、不要なセルは非表示とするか、
一つの数式に纏めることはできるようにしていますので、どうしても一つのセルという場合には、
ご自身で纏めて下さい。※纏めた場合は、ctrl+shift+enterは忘れずに。
尚、対象の文字列に数値が含まれていない場合は#REFとなりますので、ISERROR()等で対応下さい。

概要

※対象データはA1セル
①文字列の最後尾にある数字の位置を取得(=B1)
※文字列分の配列をROW()関数で生成し、MID()により1文字ずつ、"0"~"9"に含まれるかの判定結果と文字位置との積を取りTrueとなる文字位置の配列の最大値を求める。

②①の位置までの文字列を生成(=C1)

③②文字列の最後尾にある数字以外の位置を取得(=D1)
※対象文字列と判定が逆転している以外は①に同じ。

④文字列より③と①で挟まれる文字列を取得(=E1)
数式

B1=MAX((MID(ASC(A1),ROW(INDIRECT("1:"&LEN(A1))),1)={"1","2","3","4","5","6","7","8","9","0"})*ROW(INDIRECT("1:"&LEN(A1)))) C1=MID(A1,1,B1) D1=MAX((MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"0"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"9"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"8"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"7"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"6"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"5"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"4"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"3"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"2"}) *(MID(ASC(C1),ROW(INDIRECT("1:"&LEN(C1))),1)<>{"1"}) *ROW(INDIRECT("1:"&LEN(C1)))) E1=MID(A1,D1+1,B1-D1)

※先頭のA1~D1はセルの位置を表したものです。B1およびD1は、ctrl+shift+enterによる配列数式です。

投稿2017/09/11 15:15

sazi

総合スコア25076

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

退会済みユーザー

退会済みユーザー

2017/09/11 22:18

絶対こっちの方がきもい (褒め言葉)ですよね。
sazi

2017/09/12 00:02

いやー、中途半端な達成感はありますけど、別なきもさを感じます。 excelが数式で正規表現に対応してくれれば、苦労しなくて済むと改めて思いました。 でも、収穫もあり、hiromanさんとslashさんに感謝です。
PineMatsu

2017/09/12 07:59

超絶技巧ですね。
sazi

2017/09/12 09:12

ROW()と配列数式の組み合わせは、結構強力ですよね。
guest

0

力業ですが...
いらない部分は、B列を値として貼り付けした後にDELすれば元通りになりますね。


(saziさんのコメントを受け2017-09-10に修正)

あきらめたらそこで試合は終わりやで

アドレス説明
A2文字列生データ
B2数式右から抜き取った最寄りの数値
C2=E2-D2+1抜き取る文字長
D2=MAX(F$1:T$1)-MATCH("!",V2:AJ2,0)+2抜き取り開始位置
E2=MAX(F$1:T$1)-MATCH("!",F2:T2,0)抜き取り終了位置
F2=IF(LEN($A2)>=F$1,IF(ISERROR(VALUE(MID($A2,F$1,1))),IF(ISNUMBER(G2)=TRUE,"!","-"),VALUE(MID($A2,F$1,1))),"-")数値のエッジ検出1
~T2F2のオートフィル数値のエッジ検出1
U2ブランク計算式同士が衝突しないため
V2=IF(LEN($A2)>=V$1,IF(ISERROR(VALUE(MID($A2,V$1,1))),IF(ISNUMBER(U2)=TRUE,"!","-"),VALUE(MID($A2,V$1,1))),"-")数値のエッジ検出2
~AJ2V2のオートフィル数値のエッジ検出2

※実証コードのため、「数値がないとき」にエラーが出るかもしれませんがその辺は割愛させてください。

投稿2017/09/08 10:59

編集2017/09/10 01:54
退会済みユーザー

退会済みユーザー

総合スコア0

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

matobaa

2017/09/08 11:33

うぉぉきもい!! (褒め言葉)
退会済みユーザー

退会済みユーザー

2017/09/08 12:13

あざっす、最高の誉め言葉です :)
sazi

2017/09/09 11:49

質問者さんのイメージでやるとおなじ結果にならないような・・・ 使い方が悪い? それから、B2とかE2で3文字が固定になってるけど、数字の塊って言ってるので、 さらにきもいことしないと駄目なのでは?
退会済みユーザー

退会済みユーザー

2017/09/09 12:32

saziさん >数字の塊って言ってるので、さらにきもいことしないと駄目なのでは? 質問者さん >A列右から最初の数字(半角・全角を含む1文字以上)の塊のみを抜き出したい 頭の体操にさらにきもいことを検討します。
退会済みユーザー

退会済みユーザー

2017/09/10 01:56

修正完了。きもさUP。エクセルのワークシート関数の懐の深さを感じます。
PineMatsu

2017/09/12 07:59

超絶技巧ですね。
guest

0

今更ながら、いい数式ができたので、書いてみます。

Excel

1{=IFERROR((TEXTJOIN("",TRUE,IFERROR(VALUE(MID($A2,ROW(INDIRECT("1:"&LEN($A2))),1)),""))),"")}

これなら、数値のみ抜き取れ、数式もスッキリです。

投稿2018/12/25 13:50

kai_keitai

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

SUBSTITUTE関数をアルファベットの分だけネストすると出来ると思います。
SUBSTITUTE関数を使う前に、半角にするASC関数、大文字にするUPPER関数で、文字列を正規化してから、SUBSTITUTE関数を、アルファベットと記号の分をネストします。

EXCEL

1=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(ASC(A1)),"A",""),"B",""),"C",""),"D",""),"E",""),"F",""),"G",""),"H",""),"I",""),"J",""),"K",""),"L",""),"M",""),"N",""),"O",""),"P",""),"Q",""),"R",""),"S",""),"T",""),"U",""),"V",""),"W",""),"X",""),"Y",""),"Z",""),"(",""),")","") 2

イメージ説明

こんな感じで、数値のみが表示されます。

投稿2017/09/16 14:03

kai_keitai

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

頭の体操と聞いて。

googleスプレッドシート限定ですが、別解置いときます。短くできたと思ったら思ったより長かったです…
もっと短くできるはず。

=index(split(regexreplace(asc(A1),"[^0-9]","/"),"/"),1,counta(split(regexreplace(asc(A1),"[^0-9]","/"),"/")))

追記:さらに短くできました。

=Hlookup(10^10,split(regexreplace(asc(A1),"[^0-9]","/"),"/"),1,true)

投稿2019/01/08 01:55

編集2019/01/22 03:15
DY.

総合スコア72

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

まだベストアンサーが選ばれていません

会員登録して回答してみよう

アカウントをお持ちの方は

15分調べてもわからないことは
teratailで質問しよう!

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問