[[20190618101211]]　『不特定文字列の中から5桁の数値のみを抽出する』（上上下下）　ページの最後に飛ぶ

『不特定文字列の中から5桁の数値のみを抽出する』（上上下下）

各セルに不特定な文字列が多くあります。
その中から特定の5桁の数値だけ抽出する関数はありますでしょうか？
基本的に日本語ではなく、アルファベット(半角)がほとんどです。

例)

Good morning 01234 →　01234
How are 56789 you doing? →　56789
How 98765 have you been? →　98765
43210 It's going well. →　43210

ご教示いただけましたら幸いです。
よろしくお願いいたします。

＜使用 Excel：Office365、使用 OS：Windows10 ＞

 ５桁未満および５桁より大きい数値が入っていることはあるのだろうか？
 また上記がある場合、同じ文字列内で５桁の数字と５桁でない数字が同時にある（123aaa12345など）ことはあるのだろうか？
（ねむねむ） 2019/06/18(火) 10:28

ねむねむ　様

基本的に5桁です。
また、5桁でない数値は混入している場合があります。

43210 It's going 123 well. →　43210

よろしくお願いいたします。
（上上下下） 2019/06/18(火) 10:34

ありゃ、他の数字もアリですか。他に数字が無いなら、以下の数式で済むかと思ったのですが。

 =MID($A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},$A1&1234567890)),5)

そうなると、私はユーザー定義関数を作るくらいしか思いつかない…。
（???） 2019/06/18(火) 10:39

必ずあるわけではないので、ない場合はこちらの関数で確認させていただきます。

ご教示いただきありがとうございます。
（上上下下） 2019/06/18(火) 10:59

 これって解決したんですかね？
 まだならちょっと確認

 1）元の文字列は最大で何文字くらい？

 2）5桁以外の数字もあるとのことですが、6桁以上もありますか？

 3）数字は例示のように必ず前後に半角スペースが入りますか？
  　数字が文字列の先頭にある場合は、数字の右側に半角スペース
  　文字列の最後にある場合は、数字の左側に半角スペース
  　12345?　のように数字の次に「?」やピリオドが来ることはありませんか？

 とりあえず以上です
（笑） 2019/06/19(水) 15:07

 ほとんど自己満足だが。
=IFERROR(MID("a"&A1&"aaaaa",MATCH(1,
 NOT(ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a")&"aaaaa",ROW($2:$51),5)*1))*
     ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a")&"aaaaa",ROW($1:$50),6)*1)*
     ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a")&"aaaaa",ROW($2:$51),6)*1),0)+1,5),"")
（ねむねむ） 2019/06/19(水) 15:36

 文字列中の半角空白と.（笑さんの書き込みを見て追加）をaに置き換え後、MID関数で5文字抜き出したものが数値のみで
 6文字抜き出した場合には数値のみではなく１文字前から６文字が数値のみでない場合にそこから5文字抜き出している。
（ねむねむ） 2019/06/19(水) 15:45

 12e34も抜き出してしまっていたため修正。
 また６文字抜きだしを1文字前の一文字に修正。

（ねむねむ） 2019/06/19(水) 15:52

 =IFERROR(MID("a"&A1&"aaaaa",MATCH(1,
  NOT(ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($2:$51),5)*1))*
  ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($1:$50),1)*1)*
  ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($2:$51)+5,1)*1),0)+1,5),"")
（ねむねむ） 2019/06/19(水) 15:53

 肝心なことを書き忘れ。
 式はShiftキーとCtrlキーを押しながらEnterキーで確定してくれ。
 （確定後、式が{}で囲まれればOK）
（ねむねむ） 2019/06/19(水) 16:01

ねむねむ　様

ご丁寧にありがとうございます。
もう遅いかもしれませんが…一応。

1）元の文字列は最大で何文字くらい？

→半角で150字程度が上限と思います。

2）5桁以外の数字もあるとのことですが、6桁以上もありますか？

→6桁以上は、ほぼないです。
　反対に6桁の数字を抽出したい場合もあります。

3）数字は例示のように必ず前後に半角スペースが入りますか？

→はい、半角スペースはほぼございます。

ご教示いただいた内容でトライしてみます。
本当にありがとうございます。

（上上下下） 2019/06/20(木) 09:56

「'」「.」「(」「)」「?」の記号はございます。

その場合は式も異なるのでしょうか？
お時間ございましたら、ご教示くださいませ。
（上上下下） 2019/06/20(木) 10:04

 質問を行ったのは私ではなく笑さんですよ。

 >→半角で150字程度が上限と思います。 
 であれば私の式中の
 ROW($2:$51)
 を
 ROW($2:$201)
 に、
 ROW($1:$50)
 を
 ROW($1:$200)
 にしてくれ。
（ねむねむ） 2019/06/20(木) 10:06

 もとの質問者は私ではないが、「ほぼない」というのは確率は低いが実際にはある、
 「ほぼございます」は確率は低いが存在しないこともある、という判断でいいのだろうか？

（ねむねむ） 2019/06/20(木) 10:15

笑　様

ねむねむ　様

大変失礼しました。
回答すべきお相手を間違えておりました。

ご容赦ください。

また、6桁は「確率はかなり低いが実際にはある」といった感じです。
しかしながら、それは目視で可能レベルなので、問題ないと考えております。

（上上下下） 2019/06/20(木) 10:26

 ＞はい、半角スペースはほぼございます。

 この「ほぼ」は無視してよい、「必ずある」と考えていいんですか？

 ＞「'」「.」「(」「)」「?」の記号はございます。

 上のように、半角スペースを挟まず、記号やアルファベットの「E」「e」が
 数字とつながることはないのか？　ということなんですけど。

 文字列にそれらの記号が含まれるかどうか、ということではありません。

 以上、確認だけ
（笑） 2019/06/20(木) 11:12

 半角スペースを挟んでいても
 とりあえず ↓ はマズいんですけど、こんなのはありますか？

 ( 1 )　　※数字の前後に半角スペース

 ( ) 内が、スペースで挟まれた1桁の数字

 以上、追加確認でした
（笑） 2019/06/20(木) 12:17

 以前の式を作業列を使ったものにして、(123)などを抜き出さないように修正。
 B列を作業列として
 ="A"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(UPPER(A1)," ","A"),".","A"),"E","A"),"(","A")&"AAAAA"

（ねむねむ） 2019/06/20(木) 13:40

 C列に
 =IFERROR(MID(B1,MATCH(1,NOT(ISERROR(MID(B1,ROW($2:$201),5)*1))*ISERROR(MID(B1,ROW($1:$200),1)*1)*ISERROR(MID(B1,ROW($2:$201)+5,1)*1),0)+1,5),"")
 と入力してShift+Ctrl+Enterで確定。
（ねむねむ） 2019/06/20(木) 13:42

（笑）様

この「ほぼ」は無視してよい、「必ずある」と考えていいんですか？

→はい。半角スペースは必ずあると考えていただいて問題ございません。

123E4　の様な半角スペースを挟まず、記号やアルファベットの「E」「e」が数字とつながることはないのか？

→はい。ございません。数値のみです。

 > ( 1 )　　※数字の前後に半角スペース

→この様な表示はございません。

色々と、ご指摘頂いたにもかかわらず、回答遅れまして申し訳ございません。
（上上下下） 2019/06/20(木) 15:22

ねむねむ　様

凄いテクニックというか…素晴らしいです。
ありがとうございます。

参考にさせていただきます。
お時間を割いていただき、誠に恐縮です。

（上上下下） 2019/06/20(木) 15:23

 ■6桁以上はないと考えていいのなら

 =IFNA(MID(A1,MATCH(TRUE,INDEX(ISNUMBER(MID(SUBSTITUTE(A1," ","★"),ROW($A$1:$A$200),5)*1),0),0),5),"")

 ■6桁以上も考慮するなら

 =IFNA(MID(A1,MATCH(1,INDEX(ISNUMBER(MID(A1&"★",ROW($A$1:$A$200),5)*1)*(LEFT(MID(" "&A1,ROW($A$1:$A$200),1))=" ")*(RIGHT(MID(A1&" ",ROW($A$1:$A$200),6))=" "),0),0),5),"")

 どちらも普通に Enter だけで確定

 ROW($A$1:$A$200) は最大文字数の指定です。
 A1:A200に何が入力されていようが何の関係もありません。

 実際の文字列でうまくいくか分かりませんので、検証は念入りにしてください。

 参考まで
（笑） 2019/06/20(木) 16:17

 ＞6桁以上はないと考えていいのなら
 ＞=IFNA(MID(A1,MATCH(TRUE,INDEX(ISNUMBER(MID(SUBSTITUTE(A1," ","★"),ROW($A$1:$A$200),5)*1),0),0),5),"")

 訂正
 =IFNA(MID(A1,MATCH(TRUE,INDEX(ISNUMBER(MID(SUBSTITUTE(A1&"★"," ","★"),ROW($A$1:$A$200),5)*1),0),0),5),"")

 すみません
（笑） 2019/06/20(木) 17:16

 ＞6桁以上も考慮するなら

 こっちも、ちょっと変更

 =IFNA(MID(A1,MATCH(1,INDEX(ISNUMBER(MID(A1&"★",ROW($A$1:$A$200),5)*1)*(MID(" "&A1,ROW($A$1:$A$200),1)=" ")*(RIGHT(MID(A1&" ",ROW($A$1:$A$200),6))=" "),0),0),5),"")

 すみません
（笑） 2019/06/20(木) 21:24

（笑）様

素晴らしい関数をありがとうございます。

これでチャレンジしてみます。
6桁もあるので、利用させていただきます。

お忙しい中、お時間を割いていただき、誠にありがとうございます。
（上上下下） 2019/06/21(金) 16:31

ねむねむ　様

以下の数式が、一番しっくりきました。
5桁は抽出できるようになりました。
誠にありがとうございます。

ちなみになのですが、6桁の場合、何を修正すれば6桁抽出が可能でしょうか？
自分なりにここかな…と思うところを修正してみたのですが、上手くいきませんでした。

ご教示くださいませ。

 =IFERROR(MID("a"&A1&"aaaaa",MATCH(1,
  NOT(ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($2:$51),5)*1))*
  ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($1:$50),1)*1)*
  ISERROR(MID("a"&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1," ","a"),".","a"),"e","a")&"aaaaa",ROW($2:$51)+5,1)*1),0)+1,5),"")

（上上下下） 2019/06/24(月) 06:08

 （上上下下） 2019/06/24(月) 06:08　と同じ結果になれば良いのであれば以下でも出来ます。
 5桁の場合
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),7)))=5)*(MID(" "&SUBSTITUTE(UPPER(A1),"E","A")&" ",ROW($1:$100),7)*1),1),"00000;;")
 6桁の場合
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),8)))=6)*(MID(" "&SUBSTITUTE(UPPER(A1),"E","A")&" ",ROW($1:$100),8)*1),1),"000000;;")

 文字列中にeを含む数字(12e03など)が絶対に無いなら以下でも。
 5桁
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),7)))=5)*(MID(" "&A1&" ",ROW($1:$100),7)*1),1),"00000")
 6桁
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),8)))=6)*(MID(" "&A1&" ",ROW($1:$100),8)*1),1),"000000")

（sy） 2019/06/24(月) 07:00

 （上上下下） 2019/06/20(木) 15:22 で、
 >記号やアルファベットの「E」「e」が 数字とつながることはないのか？ 
 >→はい。ございません。数値のみです。 
 と答えられてるので無いと思いましたが、(12.34など)の小数点記号に対応してなかったので、

 5桁
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),7)))=5)*(MID(" "&SUBSTITUTE(SUBSTITUTE(UPPER(A1),"E","a"),".","a")&" ",ROW($1:$100),7)*1),1),"00000;;")
 6桁
 =TEXT(AGGREGATE(14,6,(LEN(TRIM(MID(" "&A1&" ",ROW($1:$100),8)))=6)*(MID(" "&SUBSTITUTE(SUBSTITUTE(UPPER(A1),"E","a"),".","a")&" ",ROW($1:$100),8)*1),1),"000000;;")

 ねむねむさん
（ねむねむ） 2019/06/19(水) 15:53、（上上下下） 2019/06/24(月) 06:08　の式は、
 LOWERを追加しないと大文字のE(12E34など)には対応してませんよ。

（sy） 2019/06/24(月) 07:21

sy　様

ご教示、ありがとうございます。
勉強になります。

これを含め、トライしてみようと思います。
大変ご丁寧にありがとうございます。
（上上下下） 2019/06/24(月) 08:18

コメント返信：

[ 一覧(最新更新順) ]

[[20190618101211]] 『不特定文字列の中から5桁の数値のみを抽出する』（上上下下） ページの最後に飛ぶ

[[20190618101211]]　『不特定文字列の中から5桁の数値のみを抽出する』（上上下下）　ページの最後に飛ぶ