A列の文字の中にB列のワードが含まれていた場合そのワードをD列に表示したい

別カテゴリで投稿していたので投稿し直します。
簡単に説明すると、A1に入れた文字列から、BにあるワードをDに、CにあるのはFに、Aに貼り付けたら自動で抜き出すようにしたいです。



B列にという単語
○○
××
△△

C列には
5×1
5×2
10×1

という文字を並べます。
A1の入力箇所に「○○ 5×1 オリジナルブレンド」という文を入れた場合
D1のセルに「○○」、F1に「5×1」とそれぞれ抜き出した結果が出るようにしたいです。
シートの作成はgoogleスプレッドシートで行っています（Excelでも大丈夫です）

A1に入る文字は必ず同じ順番や同じ文字数で入るとは限らないです。
○○○○ 10×10となる場合や、5×1 ○○となっている場合もあります。
A列に貼り付ける文字が100件以上を超えるので、自動で判別できたら助かります。

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

• 早速のご回答ありがとうございます。
  表に入れてみたのですが、
  A1に入れた文字からは、B1とC1に入れたもののみ抜き出す状態になりました。
  A1に入れてB1〜B7の中にあるワードから該当するものを抜き出す場合どうしたら良いでしょうか。
  =IF(COUNTIF($A$1,"*"&B1:B7&"*"),B1:B7,"")
  と入れてみましたがやはりB1とC1からしか抜き出せず、D1が空欄になりました。

  No.1の回答に寄せられた補足コメントです。 補足日時：2019/11/11 13:23

• ご回答ありがとうございます。
  ＞A1の入力箇所に「○○ 5×2 おりじなる△△ブレンド」という文を入れた場合は次のどれになりますか？
  ③D1が「○○△△」F1が「5×2」
  
  ＞A1の入力箇所に「○○○○ 10×10」という文を入れた場合は次のどれになりますか？
  ⑥ D1が「○○○○」F1が「10×1」
  
  とさせたいです。

  No.2の回答に寄せられた補足コメントです。 補足日時：2019/11/11 16:10

• 2件ご回答ありがとうございます。
  先程回答していただいた内容は問題なく表示できました。
  今回仰っている通りの、Aにダブりがあると表示ができないというところまで確認できました。
  ちょうどそれを聞こうと思っていましたので、察して頂き助かります。
  
  エクセルは使用出来る環境ですが、
  使用OSがMacで、「挿入」や他の項目を探しても「標準モジュール」が見つかりませんでした。
  普段WIN使用なのでMacに詳しくないもので…（Macは仕事で使ってます）申し訳ないです。
  
  関数のみで対応することは難しいでしょうか

  No.5の回答に寄せられた補足コメントです。 補足日時：2019/11/11 16:58

No.7 回答者： tom04 回答日時： 2019/11/12 08:50

No.４～6です。

何度もごめんなさい。
ユーザー定義関数にしてしまうと、データ変更のたびに再計算され
砂時計がくるくる回って「応答なし」の状態になってしまいます。

すなわちユーザー定義関数はおススメしません。
単純にマクロを１回走らせるだけの方が良いと思います。

どうも失礼しました。m(_ _)m

No.6 回答者： tom04 回答日時： 2019/11/11 22:43

No.4・5です。

＞A1の入力箇所に「○○○○ 10×10」という文を入れた場合・・・

相当厄介ですね。
また、関数での方法をご希望だというコトですが当方の力ではお手上げです。

とりあえず、前回同様ユーザー定義関数になりますが、
＞　○○○○ 10×10　のような場合でも対応できるようにしてみました。
尚、Mac版のExcelは使ったことがないのでWindows版のExcelになりますが・・・

前回のユーザー定義関数は消去し、↓に変更してください。

Function myStr(c As Range, myRng As Range)
　Dim k As Long
　Dim r As Range, buf As String
　　For Each r In myRng
　　　For k = 1 To Len(c)
　　　　If Mid(c, k, Len(r)) = r Then
　　　　　buf = buf & r
　　　　　k = k + Len(r) - 1
　　　　End If
　　　Next k
　　Next r
　　　myStr = buf
End Function

※　Mac版のVBE画面の出し方は↓のサイトが参考にならないでしょうか？
http://gadgets-geek.blog.jp/archives/1044111301. …

https://hamachan.info/mac/excel-kaihatsu/

（後者のサイトの最後の方の画面で、メニューの「Visual Basic Editor」をクリックすればVBE画面が表示されるような気がします）

Mac版のExcelが手元にないので確かめるコトができませんし、
ユーザー定義関数が使えるかどうかも不明です。m(_ _)m

No.5 回答者： tom04 回答日時： 2019/11/11 16:33

No.4です。

投稿後に補足を拝見しました。
前回の数式はA1セル内にダブって存在するとお望みの表示になりません、
あくまで一つだけが存在する！という前提の式でした。
（作業用のセルを設ければ可能かもしれませんが、
手っ取り早く、この質問限定（ユーザー定義関数）にしてみてはどうでしょうか？
（Excelでの方法になります）

Alt＋F11キー　→　メニュー　→　挿入　→　標準モジュール　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペーストしてください。

Function myStr(c As Range, myRng As Range) '//この行から//
　Dim r As Range, buf As String
　　For Each r In myRng
　　　If InStr(c, r) > 0 Then
　　　　buf = buf & r
　　　End If
　　Next r
　　　myStr = buf
End Function '//この行まで//

そして、Excel画面に戻り（VBE画面を閉じて）
D1セルに
=myStr(A1,B1:B7)

F1セルに
=myStr(A1,C1:C7)

という数式を入れてみてください。

※　通常のワークシート関数と同じように使えますので
範囲は好みで変更してください。m(_ _)m

No.4 回答者： tom04 回答日時： 2019/11/11 16:17

こんにちは！

横からお邪魔します。
＞B1〜B7の中にあるワードから該当するものを・・・

一例です。
尚、Ｂ列・Ｃ列とも1～7行目は空白セルがないようにしておいてください。
（7行目まで埋まらない場合は「*」などまず使用することはないと思われる文字でつぶしておきます）

D1セルに
=IF(OR(ISNUMBER(FIND(B1:B7,A1))),INDEX(B1:B7,SUMPRODUCT(ISNUMBER(FIND(B1:B7,A1))*ROW(B1:B7))),"")

F1セルに
=IF(OR(ISNUMBER(FIND(C1:C7,A1))),INDEX(C1:C7,SUMPRODUCT(ISNUMBER(FIND(C1:C7,A1))*ROW(C1:C7))),"")

※　どちらも配列数式になりますので、Ctrl+Shift＋Enterで確定！（←必須★）してください。m(_ _)m

No.3 回答者： fujillin 回答日時： 2019/11/11 14:48

こんにちは

セルの位置関係がクリアではないので、以下の様に仮定しています。
　・元となる文字列はA1セルにある
　・B列の文字列群は、仮にB1:B3と仮定

その上でエクセルの関数式として回答します。
D1セルに
=IFERROR(INDEX(B:B,AGGREGATE(15,6,INDEX(ROW(B$1:B$3)/(FIND(B$1:B$3,A1)>0),,),1)),"")
を設定することで、ご質問のような結果になると思います。

F1セルの式も同様にすることで、算出可能でしょう。


※ 関数式の限界として、複数の文字列がヒットしてしまった場合でも最初の値しか返しません。
※ Googleスプレッドシートとのことですが、「（Excelでも大丈夫です）」とありましたので、上記はエクセルの関数での回答です。
スプレッドシートに、上式に対応している関数があるのかどうかは知りません。
最悪の場合は、ユーザ関数にすれば対応可能ではなかろうかと想像します。

No.2 回答者： GooUserラック 回答日時： 2019/11/11 13:49

念のため確認です。

A1の入力箇所に「○○ 5×2 おりじなる△△ブレンド」という文を入れた場合は次のどれになりますか？
① D1が「○○」F1が空欄
② D1が「○○」F1が「5×2」
③ D1が「○○△△」F1が「5×2」
A1の入力箇所に「○○○○ 10×10」という文を入れた場合は次のどれになりますか？
④ D1が「○○」F1が空欄
⑤ D1が「○○」F1が「10×1」
⑥ D1が「○○○○」F1が「10×1」

No.1 回答者： x-1919 回答日時： 2019/11/11 12:58

細かい部分まで希望どおりになっているかわかりませんが。

D1 の式
=IF(COUNTIF($A$1,"*" & B1 & "*"),B1,"")
E1 の式
=IF(COUNTIF($A$1,"*" & C1 & "*"),C1,"")

もし A1 に対して B および C 列の 2行目以降も比較するなら上記の式を下に向けてコピーしていけば良いかと。