Excel関数 該当する文字列を抜き出す

列A　 　　　　　　　　　　　　　　　　列B　　　　
１　AB001-A11-SV1234　　　　　　　　　　　
２　AB001-A11-SD1
３　AB001-B11-SV1
４　AB001-B11-YYYY
５　AB002-A11-SV12/05E
６　AB002-B11-B1
７　AB002-B11-B1/GHU
８　AB002-a11-SD123



列AはAB002、AB003、AB004と、色んな組合せがづつきます。

その中で、AB001の場合は最後のハイフンの後が「SV」を含むものだけ列Bに返し、
しかしAB002の場合は、最後のハイフンの後が「B1」を含むものだけを列Bに返したいです。
具体的に行でいうと１，３、６，７行目が該当するので、その４つをB列に返したいです。
最終的にはAB001, AB002・・と製品ごとに売れた個数のグラフを作りますので列Bに返す以外のいい方法があればそれでもいいです。
「AB001＊SV＊」「AB002＊B1＊」とやって、IF関数などを使ってやっても「＊」などのワイルドカードが使えない関数がありうまくいきません。

問題は製品はこれだけではなく、AB003, AB004以下続き、抜き出したいパターンが２０～３０個あります。
上記の２種類だけを抜き出すのならできたのですが２，３０個あるのでどうしてもうまくいきません。
COUNTIFを含んだ関数でうまくいきそうだったこともありますが、COUTIF＋COUTIF＋・・・で結局、１０個も入らず長さに限界があるようでできませんでした。

私の頭では限界です。
頭のいいかた、よろしくお願いいたします。

質問者からの補足コメント

• 度々ありがとうございます！
  来週以降、会社に行って試してみます。
  はじめから言っておかなくて、何度も申し訳ありません。
  ほんとうに感謝です！！

  No.6の回答に寄せられた補足コメントです。 補足日時：2017/04/08 10:26

• ご回答ありがとうございます！
  来週会社に行って試してみます。
  他の業務があり、なかなか取り掛かれない日が多いので日数かかるかもしれません。
  また結果ご報告します。
  ありがとうございました。

  No.5の回答に寄せられた補足コメントです。 補足日時：2017/04/08 10:30

No.7 回答者： ママチャリ 回答日時： 2017/04/08 10:25

わたし的には、フィルタオプションをお勧めします。

もし、関数にこだわるのであれば、読み飛ばしてください。

フィルタオプションは、「データ」タブの並べ替えとフィルタから「詳細設定」をクリックします。

さらに、VBAの知識があれば、次のようなマクロでも実現可能です。
ちなみに、このマクロは添付画像のレイアウトを前提としています。

Sub Macro1()
Columns("A:A").AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Range("C1").CurrentRegion, _
CopyToRange:=Range("E1"), Unique:=False
End Sub

この回答へのお礼

ご回答ありがとうございます!
売上のデータをある部分にはりつけると、売上個数、金額、支店別、などの様々なグラフが一気に自動的にでるようにしなければいけません。
全体は完成しているのですが、製品の中でも特定のものだけを抜き出すこの質問に関することだけができないでいました。
なので今回はフィルタオプションは使えそうにないのですが、とても参考になりましたので、今後利用させていただきます。ありがとうございます。

お礼日時：2017/04/08 10:43

No.6 ベストアンサー 回答者： yuji3690 回答日時： 2017/04/07 21:36

全体が11文字しかない場合があるわけですね。

11文字の場合をどうするかによりますが、左5文字&10,11文字目を表示するとすれば、
12文字以上の場合
C1=LEFT(A1,5)&RIGHT(LEFT(A1,12),3)
11文字の場合
C1=LEFT(A1,5)&RIGHT(A1,2)
とすればよいので、
C1=IF(LEN(A1)=11,LEFT(A1,5)&RIGHT(A1,2),LEFT(A1,5)&RIGHT(LEFT(A1,12),3))
でどうでしょうか？

この回答へのお礼

ご回答ありがとうございます。
また私の説明不足でした。
１１文字目が１~９と決まっているだけで、そこで終わる場合もあれば１２文字目から何がくるかわからないです。なので結論からいうとうまく行きませんでした。

↑この補足かお礼を４，５日前に書いたつもりなんですが、みあたりません。
さきほど締め切られてしまい、続きをうかがうことができなくなってしまいました。
この最後の製品は今まで一つも出荷されていないものなので、途中まで教えていただいたところで利用させていただきます。
度々ありがとうございました。

お礼日時：2017/04/15 10:43

No.5 回答者： mike_g 回答日時： 2017/04/07 11:45

添付図参照

D3: =FIND("-",A3)
E3: =FIND("-",A3,D3+1)
F3: =MID(A3,E3+1,2)
G3: =IF(AND(LEFT($A3,$D3-1)=G$1,$F3=G$2),$A3,"")

B3: =G3&H3

この回答へのお礼

ご回答ありがとうございます。
試してみたのですが列Hというのが分からずできませんでした。

お礼日時：2017/04/15 10:44

No.4 回答者： yuji3690 回答日時： 2017/04/07 00:58

そのままの形で抜き出したいのであれば、別の列(Dとします)に

D1=IF(COUNTIF(AA:AA,C1)=0,"",A1)
とすれば、C列（抜き出したいパターン表示）ではなく、A列（製品名そのまま）のデータを表示できますよ。

この回答へのお礼

ありがとうございます！うまくいきました！
ただ今日会社に行ってみて一つ問題がありました。
AB005-A11-1、つまり、最後のハイフンの後が他のものは２桁なのに、このように１桁の製品がありました。これはやはりうまく行かず、文字が返せません。今それをどうするか考えています。
他の方のご回答を検証するのに時間がかかりそうなので、しばらく空くこともあると思いますが、１か月以内には締めたいと思いますので今しばらくおまちください。

お礼日時：2017/04/07 16:13

No.3 回答者： tom04 回答日時： 2017/04/06 23:43

No.2です。

＞実際の製品には、AB003ならSVとSDの２つを抜き出したいという場合もあります。

というコトですので、↓の画像のように対応表を変えてみました。
E列1列だけにし、アンダーバーで繋いでいます。
（画像では10行目までをデータ対象として、その範囲にはまず使うことはないであろう「*」アスタリクスで空白セルをつぶしています）
範囲は実際のデータ数に合わせてください。

そして作業列C2セルに
=IF(A2="","",LEFT(A2,FIND("-",A2)-1)&"_"&MID(A2,FIND("#",SUBSTITUTE(A2,"-","#",2))+1,10))
という数式を入れフィルハンドルでこれ以上データはない！というくらいまで下へコピーしておきます。

最後にB2セルに
=IF(OR(ISNUMBER(FIND(E$1:E$10,C2))),A2,"")

配列数式ですので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　B2セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま)
Ctrl＋Shiftキーを押しながらEnterキーで確定！
フィルハンドルで下へコピーすると画像のような感じになります。m(_ _)m

この回答へのお礼

ありがとうございます！難しいですね。

今日会社に行って見てみると、AB005-A11-1、つまり、最後のハイフンの後が他のものは２桁なのに、このように１桁のもがありました。今それをどうするか考えています。
また、製品名をそのまま載せるわけには行かないので簡単に書きましたが、実はAB001ではなくABCD001で、セルの位置も実際と違っていたりするので、数式を一つ一つ置き換えながら検証しながらしないといけないので、このご回答の方法をまだ試せていません。会社に行ってもこれに取り掛かれない日もあり検証に少し時間がかかりそうです。
１か月以内にはご報告するようにします。

お礼日時：2017/04/07 16:07

No.2 回答者： tom04 回答日時： 2017/04/06 22:27

こんばんは！

＞上記の２種類だけを抜き出すのならできたのですが２，３０個あるのでどうしてもうまくいきません。

というコトですので、一案です。
↓の画像のように対応表を作成し、作業用の列を設けてみてはどうでしょうか？

作業列C2セルに
=MID(A2,11,10)
という数すきを入れ、結果のB2セルに
=IF(ISNUMBER(FIND(VLOOKUP(LEFT(A2,5),E:F,2,0),C2)),A2,"")
という数式を入れます。

最後にB2・C2セルを範囲指定　→　C2セルのフィルハンドルで下へコピーすると
画像のような感じになります。

※　作業列が目障りであれば遠く離れた列にするか
非表示にしてみてください。m(_ _)m

この回答へのお礼

ありがとうございます！美しいです。
理想的な形なのですが、私が説明不足なところがありました。
簡単にAB001はSVで、AB002 ならB１とわかりやすく書いたのですが、実際の製品には、AB003ならSVとSDの２つを抜き出したいという場合もあります。その場合は対応表のAB003のSVとAB003のSDがVLOOKで競合してしまって（？）、SDのところが空欄になってしまいます。
試しに、対応表のE4とF4にAB001のSDと入れてみると、結果のB3のセルが空欄になってしまいます。
はじめからいっておかなくて申し訳ありません！
また気が向きましたら、ご返信いただけるとありがたいです。急ぎません。

お礼日時：2017/04/06 23:21

No.1 回答者： yuji3690 回答日時： 2017/04/06 21:51

該当する部分だけ表示する列を作り、

その列に表示されたものが、抜き出したいパターン一覧に含まれていれば、B列にA列のデータ（或いは抜き出したいパターン）を表示する。
というのでどうでしょう？

抜き出したいパターンというのが他にどんなものがあるのか分かりませんが、
今の例ですと、左から5文字＆左から10～12文字目の8文字（「-」を含む）で判別できるように思います。
C1=LEFT(A1,5)&RIGHT(LEFT(A1,12),3)
とすれば、C1に「AB001-SV」が表示されるはずです。
必要なだけコピーしてください。

別のシートなり他の列なりに抜き出したいパターン一覧を作ります。
仮にAA列に作ったとして、
B1=IF(COUNTIF(AA:AA,C1)=0,"",C1)
これで、AA列の中にC1と一致するものがなければ空白、
あればC1を表示する。となります。
必要なだけコピーしてください。

C1(AB001-SV)は該当するので、B1に「AB001-SV」が表示されます。
あとはB列に表示された「AB001-SV」の数をCOUNTIFで数えれば、
該当した「AB001-SV」の数が分かります。

この回答へのお礼

早速のご返信ありがとうございます。
個数確かに出ます！
私が言葉足らずだったのですが、理想的には、該当するA列の製品名をAB001-SVとう形ではなく、そのままの形で出せたらいいです。
ゆくゆくは抜き出したものを他に利用するかもしれないので。
とはいえ、とりあえず個数がでるので助かりました。

お礼日時：2017/04/06 23:12