[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『文字列の途中までを抜き出す式』（中途B）
お願いします。
A列にある文字列を、下図のように
「先頭」から「最初に出現する数字の連続部分の終わり」までを
B列に抜き出す式を組みたいのですが、もーさっぱり手が出ません。
お知恵をお貸し下さい。
A列の文字列は英数混在で、下図は25桁で統一していますが実際は桁数も色々です。最大で25桁です。
途中計算用の列を大量に使わなければならない様なら、マクロでも仕方ないとは考えてますが、
できればマクロは避けたいです。普通の関数だけでもできるでしょうか?
__| ____________A____________ ______B______
1|
2| 9G2oHWUk9aHMhXtolsHg2yE1u 9
3| oHY4gQ4L33D6qwXC7iVPFbmcZ oHY4
4| rRyymx5uOR9DiaflGeTU4D33R rRyymx5
5| VkG8S11N15CHEkx5p4f92Y4bH VkG8
6| 4aVYyAwd4YeNU6Vciu4HYMmX1 4
7| uuX1cNexYXoedSQrEKX1igf4a uuX1
8| QCXlP6VFKdx5GP2UuCTdZcqnd QCXlP6
9| 5nkgbsZRwWeHKmb5C3U14dDs6 5
10| px5tVl31zxTtauY3FtyI7HhAT px5
11| r1chNhs5t3HS14l8KL8BVQvC6 r1
12| y3hNfIi6FP4qcoN29DKwK3SrD y3
13| PX94Ent2RfxAP9m2142TuLzS9 PX94
14| BkEnrVAoVw91OK3TBTtjFCcmm BkEnrVAoVw91
15| o8QxxA7cP8A7ogFpplJLA2cl2 o8
16| 7s1P4u5wB1aC9m89ODBG4UTWC 7
17| Y2dcwFs6n8ZAVaz2n3Hlwz99x Y2
18| 6E7x99ciQL622S5h9VglbZjis 6
19| LF5d94LilD3LCra6vluUODStX LF5
20| fJjRt7RM6dgY5LSg3e1YoLu1c fJjRt7
21| SImHlEiLiG2WZGAxD7eEb19sE SImHlEiLiG2
22| 1818RnEbLa43G2767x2M8oREy 1818
23| Rs3a51VVg9DtYN3k2OW6QHlL7 Rs3
24| OA3UiJdd2omK1kenIfri9dW5b OA3
25| 3lPp36hPE83W6IMxz6T6dFIa4 3
26| mva3nxKyH9I9uQbv3wxNGiMrR mva3
27| OYHqFtA3CE68MZX28o7G5s2t3 OYHqFtA3
28| W1MW7vZ4wzs6xjyUWGi2s5X9B W1
29| MVed71BY9QmQB4YVo71Y4M6A6 MVed71
30| 8ijwK8RMD8xGAffmzwsoOdH4j 8
31| VcGmCQKf79hg9uqger9Q8wuLD VcGmCQKf79
＜ 使用 Excel：Excel2010、使用 OS：Windows7 ＞
=left(a1,min(find({0,1,2,3,4,5,6,7,8,9},A1&1234567890)))
（GobGob） 2016/10/28(金) 00:37
あー、一桁やないんですね。。 出直しますわ（笑） （GobGob） 2016/10/28(金) 00:46
こんばんわ。
以下の式で出来ると思います。
B2 =LEFT(A2,AGGREGATE(15,6,(1/(ISERROR(FIND(MID(A2,ROW(A$1:A$25),1)*1,A2))*(ROW(A$1:A$25)>=AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1))))*ROW(A$1:A$25),1)-1)
下にオートフィル
（sy） 2016/10/28(金) 01:07
おはようございます。
syさんから回答ありましたね（笑）。
別案です。
B2 =IFERROR(LEFT(A2,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1)+LOOKUP(1,-MID(A2,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1),ROW(A$1:A$25)),ROW(A$1:A$25))-1),"") （GobGob） 2016/10/28(金) 08:20
こんなのも。
=IFERROR(LEFT(A2,MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0)+MATCH(1,INDEX(0/MID(SUBSTITUTE(A2,"E","A"),MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0),ROW($1:$50)),0),1)-1),"") （ねむねむ） 2016/10/28(金) 11:46
あ、SUBSTITUTE関数は大文字小文字区別したっけ。
=IFERROR(LEFT(A2,MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0)+MATCH(1,INDEX(0/MID(SUBSTITUTE(ASC(A2),"E","A"),MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0),ROW($1:$50)),0),1)-1),"") としてくれ。 （ねむねむ） 2016/10/28(金) 11:53
"E"への対処を必要としないバージョン。
=IFERROR(LEFT(A2,MATCH(0,0/MID(A2,ROW($1:$50),1),0)+MATCH(1,ISERROR(MID(A2,ROW(INDIRECT(MATCH(0,0/MID(A2,ROW($1:$50),1),0)&":50")),1)*1)*1,0)-2),"")
ShiftキーとCtrlキーを押しながらEnterキーで式を確定する（確定後、式が{}でかこまれればOK）
（ねむねむ） 2016/10/28(金) 12:11
しかしエラーを無視して集計なんて反則じみた関数があったとは・・・２度びっくりです
syさん式
=LEFT(
A2
,AGGREGATE(
15
,6
,(
1/(
ISERROR(
FIND(MID(A2,ROW(A$1:A$25),1)*1,A2) // 数字以外ならTRUE
)
*( // 掛ける
ROW(A$1:A$25)>=AGGREGATE(15,6,FIND({0,1,2,3,4,5,6,7,8,9},A2),1)
) // 最初に出現する数字の位置以上であるかどうか
) // 分の１(で、#DIV/0!か1のどちらかになる)
)*ROW(A$1:A$25) // 掛ける「それが何文字目か」
,1
)-1 // をSMALLで最初の位置を算出し、
) // それのひとつ前
GobGobさん式
=IFERROR(
LEFT(
A2
,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1) // 最初に出現する数字の位置
+LOOKUP( // たす
1
,-MID(
A2
,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1) // 以降の文字列を１文字ずつ連結したものに
,ROW(A$1:A$25) // マイナス１掛けた結果からプラス値をLOOKUPして
) // (てかLOOKUPのこの性質にも驚きです)
,ROW(A$1:A$25) // 対応する文字位置を算出し、
)-1 // それのひとつ前
)
,"")
ねむねむさん式
私でも知ってる様な関数だけでも再現できちゃうんですね。またびっくりです。
=IFERROR(
LEFT(
A2
,MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0) // 最初に出現する数字の位置
+MATCH( // たす
1 // GobGobさんのをLOOKUPと同じ
,INDEX(
0/MID(
SUBSTITUTE(UPPER(A2),"E","A") // (指数化の回避かな?)
,MATCH(0,INDEX(0/MID(A2,ROW($1:$50),1),0),0)
,ROW($1:$50)
)
,0
)
,1
)-1
)
,""
)
という風に解釈しました。(もし間違ってたら突っ込んで下さい)
ありがとうございました。
（中途B） 2016/10/28(金) 12:14
すみません。
私 >SUBSTITUTE(ASC(A2),"E","A"),
中途Bさん >SUBSTITUTE(UPPER(A2),"E","A") // (指数化の回避かな?) あっ、その通りです。 大文字・小文字なのだから全角を半角にしても意味がなかった。
（ねむねむ） 2016/10/28(金) 12:17
=IFERROR(
LEFT(
A2,
MATCH(0,0/MID(A2,ROW($1:$50),1),0)
+MATCH(
1
,ISERROR(MID(A2,ROW(INDIRECT(MATCH(0,0/MID(A2,ROW($1:$50),1),0)&":50")),1)*1)*1
,0 // ~~~~~~~~ ~~~~~~あーなるほど
)-2
)
,""
)
知ってる関数だけでも色々方法があるものですね。
私にはまずそんな発想が出ませんでした。
勉強になります。
（中途B） 2016/10/28(金) 12:20
あー。指数かー。忘れてたーｗ
=IFERROR(LEFT(A2,AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1)+LOOKUP(1,-MID(SUBSTITUTE(UPPER(A2),"E","A"),AGGREGATE(15,6,FIND(ROW(A$1:A$10)-1,A2),1),ROW(A$1:A$25)),ROW(A$1:A$25))-1),"")
ちなみに。。。ねむねむさん案はゼロのみ、ゼロ先頭の二桁以上には対応してないっぽいので。。。
=IFERROR(LEFT(A1,MATCH(0,INDEX(0/(MID(A1,ROW($1:$50),1)+1),0),0)+MATCH(1,INDEX(0/(MID(SUBSTITUTE(ASC(A1),"E","A"),MATCH(0,INDEX(0/(MID(A1,ROW($1:$50),1)+1),0),0),ROW($1:$50))+1),0),1)-1),"")
=IFERROR(LEFT(A1,MATCH(0,0/(MID(A1,ROW($1:$50),1)+1),0)+MATCH(1,ISERROR(MID(A1,ROW(INDIRECT(MATCH(0,0/(MID(A1,ROW($1:$50),1)+1),0)&":50")),1)*1)*1,0)-2),"") CTRL+SHIFT+ENTER
（GobGob） 2016/10/28(金) 13:11
>ちなみに。。。ねむねむさん案はゼロのみ、ゼロ先頭の二桁以上には対応してないっぽいので。。。
GobGobさん、ありがとう。
あー今回あちこち穴があるな。 もっと考えないと。 （ねむねむ） 2016/10/28(金) 13:17
＞という風に解釈しました。(もし間違ってたら突っ込んで下さい)
ん～とね。。。 オイラの数式で言うと
＞ // それのひとつ前
ではなくて
取り出す桁数 + 最初の数値の位置 を計算すると 1桁ダブっちゃうので -1してます。 （GobGob） 2016/10/28(金) 13:56
>ちなみに。。。ねむねむさん案はゼロのみ、ゼロ先頭の二桁以上には対応してないっぽいので。。。 いちおう0が頭でもそのあとに1から9があれば大丈夫。 (0のみの連続だとだめだが） （ねむねむ） 2016/10/28(金) 14:34
しつこいかもしれないが。
=IFERROR(LEFT(A2,MATCH(1,(ISERROR(MID(A2,ROW($1:$49),1)*1)<>ISERROR(MID(A2,ROW($2:$50),1)*1))*NOT(ISERROR(MID(A2,ROW($1:$49),1)*1)),0)),"") Shift+Ctrl+Enterで。 （ねむねむ） 2016/10/28(金) 16:28
例題でしか試してないけど、
=LEFT(A2,MATCH(1,INDEX(ISNUMBER(MID(A2,ROW($A$1:$A$25),1)*1)*NOT(ISNUMBER((MID(A2,ROW($A$2:$A$26),1)*1))),0),0))
普通にEnterで確定。
よく見たら、ねむねむさんの最後の回答と同じようなことかもしれない。
↓ 追記（18:12） NOT(ISNUMBER は ISERR でいいですね！
=LEFT(A2,MATCH(1,INDEX(ISNUMBER(MID(A2,ROW($A$1:$A$25),1)*1)*ISERR(MID(A2,ROW($A$2:$A$26),1)*1),0),0)) ~~~~~ 参考まで。 （笑） 2016/10/28(金) 17:22 追記18:12
笑さんのほうはN文字目が数字でN+1文字目が数字でない、なのに対して私のほうはN文字目とN+1文字目が違う＆N文字目が数字、なので私のほうは余計な手間をかけているか。
（ねむねむ） 2016/10/28(金) 17:30
ねむねむさん、笑さん、更なる案をありがとうございます。
PX94Ent2Rf ---1桁～10桁 X94Ent2Rf ---2桁～11桁 0011000100 ---1桁～10桁が「数字か」 1001110111 ---2桁～11桁が「数字以外か」 0001000100 ---の掛け算結果 ←から最初に出てくる「1」の位置を返す
この考え方にはホント「スゲー!!」と声に出してしまいました。
「数字の次が数字じゃないところ」分かってしまえばそのまんまなんですけどね。
こんなシンプルな仕掛けで出来てしまうとは・・・
今日はもうおなかいっぱいです。ホクホクです。
みなさま、大変お世話になりました。
ありがとうございました。
（中途B） 2016/10/28(金) 19:06
ねむねむさんの処理案でオイラも考えてみましたｗ。
=IFERROR(LEFT(A2,MATCH(1,MMULT(ISNUMBER(-MID(A2,ROW($A$1:$A$25)+{0,1},1))*1,{1;2}),0)),"")
=IFERROR(LEFT(A2,MATCH(1,MMULT(ISERR(-MID(A2,ROW($A$1:$A$25)+{0,1},1))*1,{2;1}),0)),"")
（GobGob） 2016/10/31(月) 08:50 → 11:22 加筆
=IFERROR(
├ LEFT(
｜ ├ A2
｜ └ ,MATCH(
｜ ├ 1
｜ ├ ,MMULT(
｜ ｜ ├ ISERR(
｜ ｜ ｜ └ -MID(
｜ ｜ ｜ ├ A2
｜ ｜ ｜ ├ ,ROW(OFFSET($A$1,,,LEN(A2)) // ←試しに弄ってみました
｜ ｜ ｜ ｜ +{0,1}
｜ ｜ ｜ └ ,1 // n文字目とn+1文字目の２次元配列に × -1
｜ ｜ ｜ ) // がエラーかどうか(数字以外かどうか)
｜ ｜ ｜ )
｜ ｜ ｜ *1 // に × 1 で真偽型を1 or 0 に変換
｜ ｜ └ ,{2;1} // した配列と{2;1}との行列積を作成
｜ ｜ )
｜ └ ,0 // そこから最初に出てくる「1」の位置を返す
｜ )
｜ )
└ ,""
)
MMULTなんて初めて知りましたよ。
__| ___A____ ___B____ ____C____ ____D____ ____E____ __________F__________
1| n n文字目 n+1文字目 B列<>数字 C列<>数字 =MMULT(D列:E列,{2;1})
2| 1 P X 1 1 1*2+1*1=3
3| 2 X 9 1 0 1*2+0*1=2
4| 3 9 4 0 0 0*2+0*1=0
5| 4 4 E 0 1 0*2+1*1=1 ←ココ
6| 5 E n 1 1 1*2+1*1=3
7| 6 n t 1 1 1*2+1*1=3
8| 7 t 2 1 0 1*2+0*1=2
9| 8 2 R 0 1 0*2+1*1=1
10| 9 R f 1 1 1*2+1*1=3
11| 10 f x 1 1 1*2+1*1=3
ということですね。
なんだかとても「美しい式」だと感じます。
ありがとうございます。
（中途B） 2016/10/31(月) 19:45
私はむしろ中途Bさんの関数解読力解説力に感心します。
これだけさらりと解読できれば質問もさぞ楽しいでことしょうし
回答に対する丁寧な解釈説明は回答者の方々にとっても
考えた甲斐が感じれていいだろうなと思います。
私も質問するときは教えてもらう礼儀として見習いたいものです。
（鈴） 2016/10/31(月) 23:56
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.