【Excel2013】MID&FINDの組み合わせ

質問

指定した文字から8桁の文字を抽出したく、添付画像のような数式を組みましたが、うまく抽出してくれません。
※エラーの非表示は後程IFERROR関数を付けますのでご教示不要です

どうしたら抽出してくれるのでしょうか？

回答No.5

kkkkkm · ベストアンサー

> う～ん、ダメですね。
> 画像と同じ結果でした。

Ctrl+Shift+Enterの配列数式にしたのに駄目なのですか？

数式バーの見た目が
{=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)}
になっていますか？

A列もD列も全角で統一されていますか？

お礼

再度のご回答ありがとうございました。

できました！！
「配列数式」というものを知らずそのままコピペしていました。
大変失礼しました。

僅かですが時間の短縮ができます。
ありがとうございました。

回答No.11

bunjii

回答No.6&7のbunjiiです。
回答No.10でエラーの指摘を受けましたがExcel2010の検証ではエラーになりません。（勿論、Excel2013でも同様のはずです）
数式確定時はCtrlとShiftキーを押しながらEnterキーの打鍵して配列数式にしました。（回答No.6ではAltとShiftを間違えていますがNo.7で訂正しました）
他人の回答に対して不適切な批評は避けて頂きたい。

お礼

何度もご回答ありがとうございました。

回答No.10

masnoske

No.9の方がご指摘のように，No.2の回答で問題ないように思います（添付画像は No.2の結果）．
=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)

No.6の回答ではエラーが出ました．
=MID(A2,MIN(FIND($D$2:$D$6,A2&$D$2:$D$6,A2)),8)

配列数式は普通の入力方法では入力できません．
B2セルに
=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)
を入力し（ここで Enterや Tabを押さない），未確定の状態で Shift + Ctrl + Enter を同時に押す．
そうすると，数式表示エリアに以下のように表示されるはずです．
{=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)}

あとは，B2セルを下にコピーすれば OKです．

普通の入力方法で B2セルに {=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)}と入力しても配列数式にはなりません．

お礼

ご回答ありがとうございました。

はい、おっしゃるようにkkkkkmさんの数式で表示できました。
「未確定の状態でCtrl+Shift+Enter」なんですね。

回答No.9

msMike

KO1014さん、
餘計な御丗話は合點承知之助です。

[No.1][No.2][No.3]のkkkkkmさんの囘答は懇切丁寧で、且つ、御見事な式ですよ。
チャンと試しましたか？！
シャーシャーと「う～ん、ダメですね」と仰る樣じゃ、“配列數式”とは何ぞやを勉強されたし！

お礼

再度のご回答ありがとうございました。

「配列数式」というものを知らずそのままコピペしていました。
今は本当に時間が無いので、時間のできた時に調べます。

回答No.8

imogasi

#3です。
補足していた抱いたのだが、疑問の逐一の回答でなく、言い訳でしかない。
結局どうしたらよいかよくわからない。
こういう質問者が多い。
関数では、難しそうな課題だと思う。
それで質問者の参考になるかどうかわからないが、聞いていることは、こうだろう
と想像して、VBAでやってみる。
質問表現に、文句を言った、お詫びの印だ。
ーー
小生（下記）では
A列のデータ文字列の中に、D列の２文字のどれかに該当があれば
その文字位置から８文字を抜出し、B列にセットする。
と考えた。
この点間違っていれば、無視して。
ーー
標準モジュールに下記貼り付け。
Sub test01()
Dim bt(1000)
lrA = Range("A100000").End(xlUp).Row
MsgBox lrA
'---テーブル作成
lrD = Range("D100000").End(xlUp).Row
MsgBox lrD
For j = 2 To lrD
bt(j) = Cells(j, "D")
Next j
'----存在チェック
For i = 2 To lrA
'---セル検索
For j = 2 To lrD
Set f = Cells(i, "A").Find(what:=bt(j), LookAt:=xlPart) '部分一致
If f Is Nothing Then
Else
Cells(i, "B") = Mid(f, InStr(f, bt(j)), 8)
End If
Next j
'---
Next i
End Sub
ーー
例データ　A列
A２:A11　数字は、本当は全角かもしれないが、下記では半角数字。
御社注文番号：BA300101
御社注文番号：BA300102
御社注文番号：BA300103
注文番号：AA100001
注文番号：AA100002
NO:'MA100001
NO:'MA100002
注文番号：ZZ600001
注文番号：ZA600002
注文番号：ZZ600003
ーー
D列は
注文番号
AA
BA
MA
ZA
ZZ
ーーー
実行後　B2:B11

BA300101
BA300102
BA300103
AA100001
AA100002
MA100001
MA100002
ZZ600001
ZA600002
ZZ600003

お礼

再度のご回答ありがとうございました。

今は本当に時間が無いので、VBAに関しては時間のできた時に勉強します。

回答No.7

bunjii

回答No.6の一部訂正です。
数式確定時にalt+ctrl+enterの同時打鍵が必要。
　　　　　　↓
数式確定時にshift+ctrl+enterの同時打鍵が必要。

お礼

ご回答ありがとうございました。

回答No.6

bunjii

>どうしたら抽出してくれるのでしょうか？
提示の数式はFIND関数の戻り値が配列になりますので数式の組み立て方に工夫が必要です。
下記数式で試してください。（数式確定時にalt+ctrl+enterの同時打鍵が必要。但し、動作確認をしていませんので悪しからず。）
=MID(A2,MIN(FIND($D$2:$D$6,A2&$D$2:$D$6,A2)),8)

ダメなときは結果を補足してください。

お礼

ご回答ありがとうございました。

回答No.4

msMike

添附圖から判斷するに、單に
=MID(A2,FIND("：",A2)+1,8)
又は
=MID(A2,FIND(":",ASC(A2))+1,8)
でも良ささうだけど、駄目なら其の理由を明記されたい。
其れならさうと最初から其れに見合った添附圖にして措けば無駄骨を省けたものを！

お礼

ご回答ありがとうございました。

注文番号リスト２桁＋６桁＝８桁が注文番号でして、その注文番号のみを抜き出したいのです。
注文番号のみの可能性もありますし、”：”や”:”は無い場合もあります。

回答No.3

imogasi

補足すべきだ。
画像だけ挙げて、やりたいことを文章でも説明せず。読者や回答者に推測させるなんて、おかしいと思う。
各行で、「注文番号：」に続く、８ケタの文字を抜き出すのではだめか。
「注文番号リスト」ごとに分けて出したいのか？
それでも、注文番号を抜出し後、結果をソートすればよいのでは。
ーー
注文番号らしきものが、「注文番号：」がない行にも出ているようだが、それは抜き出したくないのか？
少なくとも結果のデータを、手書きしてでも、質問に書くべきでは。
回答者も時間をかけるのだから、質問者もよく考えてほしい。

お礼

ご回答ありがとうございました。

言い訳になってしまいますが、仕事が多忙になってしまい昼休みも無い状態でこの質問をさせていただきました。
今も始業開始前に返答しています。

注文番号リスト２桁＋６桁＝８桁が注文番号でして、その注文番号のみを抜き出したいのです。

回答No.2

kkkkkm

No1の訂正です。

15行目の式をコピペしたのでA15になってますがA2で

=MID(A2,MAX(IFERROR(FIND($D$2:$D$6,A2),0)),8)

お礼

ご回答ありがとうございました。

う～ん、ダメですね。
画像と同じ結果でした。

回答No.1

kkkkkm

=MID(A15,MAX(IFERROR(FIND($D$2:$D$6,A15),0)),8)
でCtrl+Shift+Enterの配列数式にして試してみてください。

お礼

ご回答ありがとうございました。