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

Question

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

【下記完成イメージ】Ａ列：元データ、Ｂ列：抜き出す数値の塊
A1=１1１AbＣｄ（2２2AbＣｄ3３3AbＣｄ） B1=333
A2=１1１AbＣｄ（2２2AbＣｄ3３3） B2=333
A3=１1１AbＣｄ B3=111
・
・
・
A1000

Comments

[PineMatsu]

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

[hiroman]

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

[coco_bauer]

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

[PineMatsu]

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

[hiroman]

B１＝333　以外は抜き出しません。

[PineMatsu]

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

[hiroman]

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

[PineMatsu]

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

[hiroman]

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

[hiroman]

PineMatsuさん、coco_bauerさん　ありがとうございました。申し訳ありませんでした。

[退会済みユーザー]

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

Answer 1

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

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

概要

※対象データはA1セル
①文字列の最後尾にある数字の位置を取得(=B1)
※文字列分の配列をROW()関数で生成し、MID()により１文字ずつ、"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による配列数式です。

Comments

[退会済みユーザー]

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

[sazi]

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

[PineMatsu]

超絶技巧ですね。

[sazi]

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

Answer 2

力業ですが...
いらない部分は、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
～T2	F2のオートフィル	数値のエッジ検出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
～AJ2	V2のオートフィル	数値のエッジ検出2

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

Comments

[matobaa]

うぉぉきもい!! (褒め言葉)

[退会済みユーザー]

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

[sazi]

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

[退会済みユーザー]

saziさん
>数字の塊って言ってるので、さらにきもいことしないと駄目なのでは？
質問者さん
>A列右から最初の数字（半角・全角を含む1文字以上）の塊のみを抜き出したい

頭の体操にさらにきもいことを検討します。

[退会済みユーザー]

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

[PineMatsu]

超絶技巧ですね。

Answer 3

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

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

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

Answer 4

SUBSTITUTE関数をアルファベットの分だけネストすると出来ると思います。
SUBSTITUTE関数を使う前に、半角にするASC関数、大文字にするUPPER関数で、文字列を正規化してから、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(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",""),"(",""),")","")

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

Answer 5

頭の体操と聞いて。

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)