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

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

ただいまの
回答率

90.35%

  • Excel

    1636questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。

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

受付中

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 1,358

hiroman

score 2

前提・実現したいこと

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

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

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

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

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

質問への追記・修正、ベストアンサー選択の依頼

  • hiroman

    2017/09/08 17:53

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

    キャンセル

  • hiroman

    2017/09/08 17:54

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

    キャンセル

  • 退会済みユーザー

    退会済みユーザー

    2017/09/08 19:45

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

    キャンセル

回答 3

+3

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

一応確認のために、別セルでの式にしていますので、不要なセルは非表示とするか、
一つの数式に纏めることはできるようにしていますので、どうしても一つのセルという場合には、
ご自身で纏めて下さい。※纏めた場合は、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/12 07:18

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

    キャンセル

  • 2017/09/12 09:02

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

    キャンセル

  • 2017/09/12 16:59

    超絶技巧ですね。

    キャンセル

  • 2017/09/12 18:12

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

    キャンセル

+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

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

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/08 20:33

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

    キャンセル

  • 2017/09/08 21:13

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

    キャンセル

  • 2017/09/09 20:49

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

    キャンセル

  • 2017/09/09 21:32

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

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

    キャンセル

  • 2017/09/10 10:56

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

    キャンセル

  • 2017/09/12 16:59

    超絶技巧ですね。

    キャンセル

0

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",""),"(",""),")","")

イメージ説明

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

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

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

  • ただいまの回答率 90.35%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

同じタグがついた質問を見る

  • Excel

    1636questions

    Excelは、マイクロソフト社が開発しているデータ集計や分析を行う表計算ソフトの一つです。文書作成や表計算、資料作成などの多彩な機能を備えており、統合パッケージであるMicrosoft Officeに含まれています。