リストからある文字だけでなく前の文字群も抜き出す

質問

おはようございます。
お世話になります。

商品リストから特定の文字が入っているものを抜き出したいのですが、
それだけではなく、その前の文字群も合わせて抜き出したいのです。
しかもその文字群は文字数にも幅があるため、前の文字一字だけを
抜き出すだけでは不十分です。

・商品リストはエクセル(excel2010)です。
・シート内のレイアウトは以下です。

【列】
A列：商品名

(例)
販売物A(12345_1212ab)＜オススメ！＞＜子供用＞
販売物B(6789_1212ab)＜今だけ1円です＞＜大人用＞
販売物C(44444)＜買わないで！＞＜じじばば用＞
販売物D(9999999999_1212ab)＜1個1000円、100個でも1000円！！＞＜初回購入特典＞

となります。

この状況下で、「_1212ab」が入っているセルが抜き出したい対象のセルで、
抜き出したい文言は「(×××_1212ab)」になります。
上記の例で行くと販売物A,B,Dが抜き出したい対象で、抜き出したい文言は
(12345_1212ab)
(6789_1212ab)
(9999999999_1212ab)
になります。()はあってもなくてもよいです。

よろしくお願いいたします。

回答No.1

itu1989 · ベストアンサー

【式で作るイメージ】
（１）検索対象文字列の出現位置を検索する。（今回だと「_1212ab」）
（２）先頭から（１）の出現位置までの文字列を対象に「_」(アンダーバー)が存在するかをチェックし、存在する場合のみ以降の処理を実行する。
（３）先頭から（１）の出現位置までの文字列を対象に「(」の出現位置を検索する。
（４）（３）の出現位置から（１）の出現位置＋検索対象文字列＋１の位置までの文字列を抜き出す
※対象文字列のルールとして抜き出し対照の文字列が()で囲われている事を前提とする
※「(」の出現位置から条件の出現位置＋条件の文字数＋１（最後の＋１は「)」を含めるようにする為

検証はしていませんイメージです。
このイメージで式を組めば取得できると思います。

【完全手動】
対象の文字列が()で囲われている事を前提として
（１）Excel上で検索対象文字列を含むレコードのみになるようにフィルタをかける
（２）対象をコピーして正規表現での置換が可能なテキストエディタへ貼り付ける
（３）「(」を「\t」へ置換
（４）「)」を「\t」へ置換
（５）置換後のテキストをExcelへ貼り付ける（必要なら別シート）

Excelはタブ区切りを別列として認識するので貼り付けると以下のようになる

A列　　　B列　　　　　 C列
販売物A　12345_1212ab　＜オススメ！＞...

お礼

さっそくどうもありがとうございました。

【式で作るイメージ】
勉強になりました。そうなんですよね、
関数ってどういう関数を知っているかじゃなくて
どういう風にイメージするか、で、
それに見合う関数を探すんですよね。
そのイメージ力が私はまだ低いのでこういう
アドバイスは大変勉強になります！
今度やってみます。

【完全手動】
手順が少ないのでできるにせよできないにせよ
早めに結果が出ると思ってこちらでやってみました。
補足に書いたとおりになりましたが、ヒントになったので
「\t」ではなく、半角スペースにエクセル上で変換し
その後、タブ区切りボタンで切り分けたところ、
うまく結果が得られました！

お早いお返事に大変助かりました。
どうもありがとうございました！

補足

（３）「(」を「\t」へ置換
（４）「)」を「\t」へ置換
（５）置換後のテキストをExcelへ貼り付ける（必要なら別シート）

をやりましたが「\t」をタブ区切りと認識しないようで
そのまま貼り付けられてしまいました。

後日「\t」についてもう少し調べてみます。

回答No.2

kagakusuki

横から失礼致します。

　自動で行うのでしたら以下の様にされると良いと思います。

　今仮に、元データがSheet1のA2以下（Sheet1のA1セルには「商品名」等の様に項目名が入力されているものとします）に入力されていて、抜き出した結果をSheet1のC2以下に表示させるものとします。
　又、Sheet2のA列を作業列として使用するものとします。

　まず、Sheet2のA2セルに次の関数を入力して下さい。

=IF(COUNTIF(INDEX(Sheet1!$A:$A,ROW()),"*(*_1212ab)*"),ROW(),"")

　次に、Sheet2のA2セルをコピーして、Sheet2のA3以下に貼り付けて下さい。

　次に、Sheet1のC2セルに次の関数を入力して下さい。

=IF(ROWS($2:2)>COUNT(Sheet2!$A:$A),"",REPLACE(LEFT(INDEX($A:$A,SMALL(Sheet2!$A:$A,ROWS($2:2))),FIND("_1212ab)",INDEX($A:$A,SMALL(Sheet2!$A:$A,ROWS($2:2))),FIND("(",INDEX($A:$A,SMALL(Sheet2!$A:$A,ROWS($2:2))))+1)+LEN("_1212ab)")-1),1,FIND("(",INDEX($A:$A,SMALL(Sheet2!$A:$A,ROWS($2:2))))-1,))

　次に、Sheet1のC2セルをコピーして、Sheet1のC3以下に貼り付けて下さい。

　以上です。

　因みに、手動で行うのでしたら以下の様にされると良いと思います。（但し、一部は半自動）

　使用していない適当な列（出来れば別のシートの列）の適当なセルに次の様な関数を入力

=Sheet1!A1&"("

　　　↓
上記のセルをコピーして、そのセル以下のセル範囲に(元データのデータ数以上の行数になる様に)貼り付ける
　　　↓
最初に関数を入力したセルとコピー先のセル範囲を纏めて範囲選択
　　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　　↓
現れた選択肢の中にある[コピー]をクリック
　　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　　↓
現れた選択肢の中にある「123」と記されたクリップボードのアイコン([値]のみ貼り付け)をクリック
　　　↓
[ホーム]タブの「スタイル」グループの中にある[検索と選択]ボタンをクリック
　　　↓
現れた選択肢の中にある[置換]をクリック
　　　↓
現れた「検索と置換」ダイアログボックスの「検索する文字列」欄に

_1212ab)*

と入力
　　　↓
「検索と置換」ダイアログボックスの「置換後の文字列」欄に

_1212ab

と入力
　　　↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
　　　↓
「検索と置換」ダイアログボックスの「検索する文字列」欄に

*(

と入力
　　　↓
「検索と置換」ダイアログボックスの「置換後の文字列」欄に入力されている文字列を全て削除し、「置換後の文字列」欄を空欄にする
　　　↓
「検索と置換」ダイアログボックスの[すべて置換]ボタンをクリック
　　　↓
「検索と置換」ダイアログボックスの[閉じる]ボタンをクリック
　　　↓
[ホーム]タブの「スタイル」グループの中にある[並べ替えとフィルター]ボタンをクリック
　　　↓
現れた選択肢の中にある[フィルター]をクリック
　　　↓
選択されているセル範囲の右上の隅に現れた「▼」マークのボタンをクリック
　　　↓
現れた選択肢の中にある(空白セル)の所のチェックマークを外す
　　　↓
ダイアログボックス内にある[OK]ボタンをクリック
　　　↓
選択範囲を示す黒い太枠の内側にカーソルを合わせてマウスを右クリック
　　　↓
現れた選択肢の中にある[コピー]をクリック
　　　↓
コピー元のセル範囲よりも下の位置にある適当なセルを右クリック
　　　↓
現れた選択肢の中にある、書類が貼られているクリップボードのアイコン([貼り付け])か「123」と記されたクリップボードのアイコン([値]のみ貼り付け)のどちらかをクリック
　　　↓
コピー元のセル範囲をクリック
　　　↓
[ホーム]タブの「スタイル」グループの中にある[並べ替えとフィルター]ボタンをクリック
　　　↓
現れた選択肢の中にある[フィルター]をクリックして、「フィルター」モードを解除

　これで、最後の貼り付け先の所に抜き出した結果が現れます。

お礼

ご親切にありがとうございました。

自動の手法を試させていただいたところ、
魔法のように結果が得られました。
INDEXとFINDとREPLACEが繰り返し出てきていますね！
キーになる文字を目印にその前後の文字たちを
どうにかして一塊で特定して、REPLACEで取り除いて
いるのでしょうか。(ものすごく当てずっぽう)

一見しただけでは仕組みがわからないむずかしさでしたが
実にスマートに結果を得られました。
すべての理解まで行かなくとも、次回似たような状況下で
応用するくらいにはなりたいと思います！

手動の方も後日トライさせていただきます。

どうもありがとうございました。