Chiquilin Site■05.02.23_Excel:文字列の中から数字を抽出

その1:「あああ123」から「123」を抽出
その2:「123あああ」から「123」を抽出
その3:「あああ123あああ」から「123」を抽出
このページももう本当は必要ないんですが、一応残しておきます。因みにですが Office365なら
 =CONCAT(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),""))
で済みます。

その1:「あああ123」から「123」を抽出

前にだけ文字列がある場合です。後ろの数字が半角数字で文字列が全角しかないなら
■A1セル:あああ123
■B1セル
 =RIGHT(A1,LEN(A1)*2-LENB(A1))
こんな式で計算できます。とはいえ条件が多いので この数式では状況によって対応が利かなくなります。文字列が半角英数字だったり 数字が全角になったりするとアウトです。
ここから先は複雑にはなりますが 異なる状況にでも対応できる方法になります。文字列の抽出に配列を使います。はっきり云って説明しきれません。
■A1セル:あああ123
■B1セル
 =MID(A1,MIN(FIND(JIS({1;2;3;4;5;6;7;8;9;0}),JIS(A1&1234567890))),256)
 
 =RIGHT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(JIS(A1),JIS({1;2;3;4;5;6;7;8;9;0}),""))))
 
 =MID(A1,MATCH(0,INDEX(ISERR(MID(A1&0,COLUMN(1:1),1)*1)
 *COLUMN(1:1),0),),256)
 
 =RIGHT(A1,LOOKUP(256,LEFT(RIGHT(A1&0,COLUMN(1:1)))*0
 +COLUMN(1:1))-1)
 
 =LOOKUP(10^17,RIGHT(A1,COLUMN(1:1))*1)
 =-LOOKUP(1,-RIGHT(A1,COLUMN(1:1)))
上2つの式については JIS関数ではなく ABS関数で全部半角にして計算させた方が式が短くなりますが 濁点・半濁点を含むカタカナが混じると 抽出する文字数がずれてしまう可能性があります。どの数式も 基本は一文字ずつ抜き出して それらを配列計算します。最後の式だけは違いますが。LOOKUP関数は エラー値を無視できる特性があるので 式を短くする上では非常に便利ですね。あいまい検索しかできないのが たまにきずですが。
ページの一番上へ

その2:「123あああ」から「123」を抽出

上とやることは基本的には同じです。
■A1セル:123あああ
■B1セル
 =LEFT(A1,LEN(A1)*10-SUM(LEN(SUBSTITUTE(JIS(A1),JIS({1;2;3;4;5;6;7;8;9;0}),))))
 
 =LEFT(A1,COUNT(INDEX(0/MID(A1,COLUMN(1:1),1),0)))
 
 =LOOKUP(10^17,LEFT(A1,COLUMN(1:1))*1)
こうなります。さっきの「その1」と数式自体は ほとんど変わりません。

その3:「あああ123あああ」から「123」を抽出

説明できるかなぁ。ここからはかなり理解するのが難しくなると思います。
■A1セル:あああ123あああ
■B1セル
 =MID(A1,MIN(FIND(JIS({1;2;3;4;5;6;7;8;9;0}),JIS(A1&1234567890))),
LEN(A1)*10-SUM(LEN(SUBSTITUTE(JIS(A1),JIS({1;2;3;4;5;6;7;8;9;0}),""))))
 
 =MID(A1,MATCH(0,INDEX(ISERR(-MID(A1&0,COLUMN(1:1),1))
+0,0),),COUNT(INDEX(-MID(A1,COLUMN(1:1),1),0)))
 
 =-LOOKUP(0,-(0&RIGHT(LEFT(A1,LOOKUP(256,MID(0&A1,
COLUMN(1:1),1)*0+COLUMN(1:1))-1),COLUMN(1:1))))
 
 =-LOOKUP(1,-(0&RIGHT(LEFT(A1,LOOKUP(1,-MID(0&A1,
COLUMN(1:1),1),COLUMN(1:1))-1),COLUMN(1:1))))
 
 =MAX(TEXT(MID(A1,{1,2,3,4,5},{1;2;3;4;5;6;7;8;9;10;11;12;13;14;15}),"標準;;0;!0")*1)
もう訳 分かりません。と書きたいところですが さすがに載せた以上は解説を入れないとまずいかと思ったので 拙いですが説明を……
 
まず 一つ目の式ですが FIND関数で「1」の場合「2」の場合……と「0〜9」までの文字位置を検索しています。この時 検索語がないとエラーを起こしてしまうのを避ける為 検索する文字の後ろに「1234567890」を繋いでいます。あとは検索した各文字位置の中で最も小さな数字の位置を抽出します。つまり 一番左側に数字がくる位置です。
 
SUBSTITUTE関数の部分ですが これは文字列中に何文字 数字が含まれているかを計算しています。「1」を抜いた場合「2」を抜いた場合……の文字列の文字数を全部足して 文字列の文字数×10 から引くという荒業です。
 
二つ目の式はLOOKUP系の関数を使う式です。短くなったのは結構ですが より説明が難しくなります。まず MID関数で一文字ごとに抽出します。次にそれに「-」をつけた時にエラーが起きるかどうかを ISERR関数でチェックします。これによって 文字なら「1」 数字なら「0」が返ります。あとはそれを INDEX関数を使って配列にして MATCH関数で最初にくる「0」の位置を検索します。これで何文字目から数字が始まるかが分かります。
 
後ろのMIDについても同じ要領ですが こちらについては LOOKUPを使ったあいまい検索が可能ですので ISERRを使う必要はありません。……こんな説明でいいのかとは思いますが 私の説明能力の限界です。済みません。
ChiquilinSite トップページへ  Copyright(C) Chiquilin_site. All Rights Reserved.
inserted by FC2 system