エクセルでキーワードを挿入した広告文を作る関数

運用者は日々CSVを弄りエクセルと格闘していると思います。
様々な支援ツールはあるものの、エクセルでCSVファイルを開いての作業は数多くあるはずです。
特に入社したばかりの新卒はまずはオペレーションからOJTが始まっているのではないでしょうか？

そこで今回はエクセルの関数を活用して複雑な広告文の変更の仕方を紹介します。

キーワードを広告文に挿入する

例として上の画像のようなCSVファイルがあったとします。
「リスティング」「PPC」「web広告」「ネット集客」というキーワードをそれぞれ広告文に挿入したい場合を想定してみましょう。
広告文は「名古屋の○○代理店なら」にします。（○○に各キーワードが挿入されます）

文字列とセルを繋ぐ

さて、この場合は簡単です。
キーワードの入っているセルを「◯◯」の代わりに挿入すれば良いので、

=”名古屋の”&G7&”代理店なら”

といった具合です。

文字列は「”」で囲う必要があり、「&」で文字列とセルを繋ぐことが出来ます。
あとはコピペすれば完了となります。

数が多くてコピペが辛い場合

広告数が多い場合いちいちコピペしていては日が暮れてしまいます。
日が暮れるくらいなら良いのですが朝まで掛かるとデスマですね、なんとかして避けなければなりません。
そんな時には「IF関数」を使ってみましょう。

=IF(G4=””,””,”名古屋の”&G4&”代理店なら”)

IF関数は「もし条件が正しければA、間違っていればB」という条件によって結果を分岐させることが出来る関数です。

今回の場合では、

もしセル「G4」に「””（何も入力されていない）」場合は「””（何も入力しない）」
何か入力されている場合には「”名古屋の”&G4&”代理店なら”」を適用

という式になります。
「”」で囲むと関数内では文字列と認識しますので、「””」で何も入力していないという認識になります。

あとはセルを下まで引っ張って、コンポーネントを「広告」だけにフィルタして「広告ID」を削除すれば終わりです。
これで広告数が千でも一万でも数分で完了してしまいます。

キーワードが複雑な場合

上述の例ではかなりシンプルな構成の場合でした。
実際のところ、ここまでシンプルなアカウントは無いと思いますので、複雑なアカウントの場合を考えてみます。

広告グループに複数のキーワードが登録されている場合

csv04

上の画像のように複数のキーワードが広告グループに登録されている場合は、

=IF(G4=””,””,”名古屋の”&G4&”代理店なら”)

を使っても望み通りの結果を得られません。

この様に「広告」以外のコンポーネントにまで不要なタイトルが入ってしまいます。

条件を変える

ではどうすればいいのでしょうか？
まずは「IF関数」の条件を変えてみましょう。

=IF(C7=”広告”,”名古屋の”&G4&”代理店なら”,””)

これでコンポーネントが「広告」の場合のみ文字が入力されるようになります。
しかし、これだけではまだ問題が残ります。
キーワードは「リスティング代理店」ですので、このままでは「名古屋のリスティング代理店代理店なら」という日本語的に意味の通じない広告文が世に出回ってしまいますし、そもそも文字数オーバーです。

挿入する文字だけを抜き出そう

掛合わせのキーワードまで入ってしまっては文字が長くなるのであればメインのキーワード１語だけを使えばこの問題は取り敢えず解決します。
それでは広告文に使う文字列だけを抜き出してみましょう。

今回は「LEFT関数」「FIND関数」を利用します。

「LEFT関数」セル内の文字列の
左から◯文字を抜き出す関数
「FIND関数」セル内の文字列の
指定した文字までの文字数をカウントする関数

「LEFT関数」と「FIND関数」を利用して、セル内の左から指定した文字までを抜き出します。

キーワードは２語以上で構成されている場合「” “（半角スペース）」で区切られているため半角スペースまでの文字数をカウントして、それ以前の文字列を抜き出せば良いわけです。

=IF(C7=”広告”,”名古屋の”&LEFT(G6,FIND(” “,G6)-1)&”代理店なら”,””)

これで下まで引っ張ればOKです。

逆順があるなどキーワードをそのまま利用できない場合

では次はキーワードをそのまま使う事が出来ない場合です。
広告グループに逆順のキーワードが登録されていたり、ミスタイプなどをキーワードとして登録している場合にはキーワードから抜きだすと大変な事になる可能性もあります。

広告グループを利用しよう

この様な場合は広告グループを利用して挿入したい文字を抜き出します。
広告グループにメインとなる（挿入すべき）ワードが含まれている事が大前提となります。

要領は「LEFT関数」「FIND関数」を利用する場合と同様です。
ただ、広告グループの場合は頭に「【新規追加】リスティング_フレーズ一致」のように管理しやすいように特定の文字を振っていたりする場合があります。
このまま広告文には出来ないので頭の文字（今回の例では【新規追加】）を省きます。

=IF(C9=”広告”,”名古屋の”&SUBSTITUTE(LEFT(B9,FIND(“_”,B9)-1),”【新規追加】”,””)&”代理店なら”,””)

例の場合は一律で【新規追加】となっているため、単純に「SUBSTITUTE関数」を使って「”【新規追加】”」を「””（空欄）」に置き換えるだけで完了です。

「SUBSTITUTE関数」
指定した文字列を置換する関数

余計な文字列が複数種類ある場合

例では【新規追加】のみでしたが、実際には【４月追加】【５月追加】や、【注力】【BIG】など複数の接頭辞を用いているかと思います。
その場合ですと置換する方法では間に合いません。

上の画像のようにならないためには別の関数を利用して文字を抜き出す必要があります。
この場合、「】」以降、「_」以前という法則性があることが見て取れます。

=IF(C9=”広告”,”名古屋の”&MID(B9,FIND(“】”,B9)+1,FIND(“_”,B9)-FIND(“】”,B9)-1)&”代理店なら”,””)

先ほどは「LEFT関数」を使いましたが、今回は「MID関数」を利用します。

「MID関数」
指定した文字数から指定した文字数を抜き出す関数

「FIND関数」を使って「】」と「_」までの文字数をそれぞれカウントし、「MID関数」で抜き出しています。
広告グループの命名規則が一定のため、関数で簡単に抜き出すことが出来るのです。

同じ文字に挟まれていて上手く抜き出せない場合

広告グループ名内の抜き出したい文字が同じ文字（例の場合は「_」）同士で囲まれている場合は上の画像のような結果になってしまいます。
「】」のすぐ後に「_」があるためです。

この様な場合には、最初の「_」から次の「_」までの間を抜き出さないといけません。
しかし「FIND関数」で単純に「_」までの文字数を出しても同じ「_」があるため０文字でカウントされてしまい上手く行きません。
今回も「MID関数」と「FIND関数」を使いますが、この状況を回避するには『「ふたつめの「_」までの文字数」ー「ひとつめの「_」までの文字数」』とする必要があります。

「【注力】_リスティング_フレーズ一致」を例に抜き出してみる

「【注力】_リスティング_フレーズ一致」から「リスティング」を抜き出します。

では、まず「ひとつめの「_」までの文字数」を出しましょう。

=FIND(“_”,B9)

「５」になりましたか？

次に「ふたつめの「_」までの文字数」です。
ふたつめの「_」までの文字数をカウントするためには「FIND関数」の開始位置をひとつめの「_」以降にしなければいけません。
「FIND関数」の構成は「=FIND(検索文字列,対象,[開始位置])」となっています。
開始位置は任意なので指定しなくても問題ないのですが、今回はふたつめを数えるために指定します。

=FIND(“_”,B9,FIND(“_”,B9))

さて、これでは変わらず「５」になってしまいます。
これは開始位置に「_」があるため、真っ先に「_」をカウントしてしまっています。
これを回避するためには下記のようにしなければなりません。

=FIND(“_”,B9,FIND(“_”,B9)+1)

「=FIND(“_”,B9)」の結果である「５」にプラス１をする事で開始位置を「６」文字目にしましょう。
これでひとつめの「_」の次（一文字後）からカウントをスタートするためふたつめの「_」までの文字数をカウント出来るようになります。
「１２」になったでしょうか？

次は「ふたつめの「_」までの文字数」ー「ひとつめの「_」までの文字数」です。

=FIND(“_”,B9,FIND(“_”,B9)+1)-FIND(“_”,B9)

これで「７」になります。
しかし、抜き出す「リスティング」という文字は６文字です。

【注力】_リスティング_
【注力】_

上の文字列から下の文字列を差し引くとふたつめの「_」が残りますね。
そのため７文字という結果になるわけです。

=FIND(“_”,B9,FIND(“_”,B9)+1)-FIND(“_”,B9)-1
または
=FIND(“_”,B9,FIND(“_”,B9)+1)-(FIND(“_”,B9)+1)

どちらでも結果は同じですが上の方が自然かも。
ふたつめの「_」の１文字を引いてあげれば正しい文字数が出ます。

それでは「MID関数」と合わせて文字列を抜き出してみます。

=MID(B9,FIND(“_”,B9)+1,FIND(“_”,B9,FIND(“_”,B9)+1)-FIND(“_”,B9)-1)

これで結果は「リスティング」となりました。
さらに他の関数と組み合わせてしまいます。

=IF(C9=”広告”,”名古屋の”&MID(B9,FIND(“_”,B9)+1,FIND(“_”,B9,FIND(“_”,B9)+1)-FIND(“_”,B9)-1)&”代理店なら”,””)

後はセルを下に引っ張るだけで完了です。

複数の文字を挿入する場合

キーワードが２語、３語の掛合わせで構成されており、それぞれのワードを広告文に挿入したい時はありますよね？
これを手作業でひとつひとつ書き換えるとなると大変な作業になってしまいます。
こういったケースも関数を使って一気に片付けたいものです。

では「【注力】_リスティング_運用代行_名古屋_フレーズ一致」を例に、「名古屋のリスティング運用代行なら」という広告文を作成していきましょう。

まずはキーワードを分解しよう

と、その前に広告グループにキーワードが丸々含まれているのであれば広告グループを利用します。
コンポーネントが「広告」になっている行を見るとわかると思いますが、この行ではキーワードは空欄です。
広告グループから挿入したい文字列を抜き出せるのであれば広告の入稿に必要のない「キーワード」や「広告グループ」といったコンポーネントはフィルタリングしてしまう事で作業しやすく出来ます。

では本題のキーワードの分解です。

１つめの「_」
=FIND(“_”,B2)
結果：５文字目

２つめの「_」=
FIND(“_”,B2,FIND(“_”,B2)+1)
結果：１２文字目

３つめの「_」
=FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)
結果：１７文字目

４つめの「_」
=FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)
結果：２１文字目

今までどおりの「FIND関数」の使い方で問題ありません。
それぞれの「_」までの文字数がこれで出ます。

次はそれぞれ「LEFT関数」「MID関数」を使って文字列として抜き出してみましょう。

【注力】_
=LEFT(B2,FIND(“_”,B2))
リスティング_
=MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2))
運用代行
_=MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1))
名古屋
_=MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1))

必要になるのは「リスティング」「運用代行」「名古屋」ですが、上記の関数ではそれぞれ不要な「_」が入ってしまっているため「-1」を加えます。

【注力】
=LEFT(B2,FIND(“_”,B2)-1)
リスティング
=MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)
運用代行
=MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)
名古屋
=MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)

これでそれぞれを単語として抜き出せました。

広告文に挿入する

では次に広告文に挿入していきましょう。
「&」で繋いでいく方法でも良いですが今回は「SUBSTITUTE関数」を利用してみます。

まずは「SUBSTITUTE関数」で置換出来るように大元の広告文を改変しておきます。

{3}の{1}{2}なら

{1}には抜き出したワードの１つ目を、{2}には抜き出したワードの２つ目をという具合に置換する関数を書きます。
{1}{2}{3}を入れ替えることで文字の順序を自由に入れ替えられます。

=SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,”リスティング”)

通常、「SUBSTITUTE関数」はこの形で置換えますが、今回は「”リスティング”」の部分が各広告グループによって異なります。
そのため「”リスティング”」の代わりに上で「リスティング」を抜き出すために使った関数を入れていきます。

=SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1))

いかがでしょうか？
「{3}のリスティング{2}なら」となっていれば問題ありません。

「SUBSTITUTE関数」で上手く置換出来る事は分かりましたので{2}{3}も置換していきましょう。
「SUBSTITUTE関数」で置換する文字を増やすには「SUBSTITUTE関数」を「SUBSTITUTE関数」で囲うという方法を使います。

=SUBSTITUTE(SUBSTITUTE(A1,”置換前の文字”,”置換後の文字”),”置換前の文字2″,”置換後の文字2″)

この様に「SUBSTITUTE関数」ごと「SUBSTITUTE関数」で囲ってしまえばOKです。
では例題に実際の数式を入れていきましょう。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1))

複雑怪奇でゲシュタルト崩壊を起こしそうになってきました。
しかし、これで「名古屋のリスティング運用代行なら」となりますので、あとは下に引っ張るだけとなります。

とは言え都合よく分割できないよという場合

分かります、世の中とは得てしてそんなものです。
頭に必ず余分な文字列が入っているとは限りませんし、全てのキーワードが３単語というわけでもありません。
１単語のキーワードもあれば２単語の場合もあります。

ではそんな場合はどうすればいいでしょうか？

「IF関数」で適用する置換パターンを分岐させる

単純な方法ですが、「IF関数」を利用してみましょう。

今回の場合では３単語のキーワードでした。
この場合、「_」の数を数えると４つとなります。
ということは２単語では「_」の数は３つですね。

検索する文字列が無い状態の時に「FIND関数」で該当の文字列を検索するとエラーを返します。
普段は憎っくきエラーですが、上手く使えばいいやつです。
今回はエラーを返した時に別の関数を適用するような式を書きます。

「IFERROR関数」
もしエラーを返した場合どうするかを指定する関数

「IFERROR関数」は「=IFERROR(値,エラーの時の値)」という式ですので、まずはエラーの場合の関数を用意しましょう。
通常の関数は

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1))

でしたね。
エラーの場合は{1}{2}{3}に対応する関数が1つずつズレています。

{1}
=LEFT(B2,FIND(“_”,B2)-1)
{2}
=MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)
{3}
=MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)

これがそれぞれ対応します。
「SUBSTITUTE関数」でそれぞれ対応する関数を当てはめていきます。

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,LEFT(B2,FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1))

この2つの関数を「IFERROR関数」で繋ぎましょう。

=IFERROR(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,LEFT(B2,FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)))

いかがでしょうか。

「ISERROR関数」を利用する場合

「IFERROR関数」以外にも「ISERROR関数」を利用する方法もあります。

「ISERROR関数」
IF関数と組み合わせてエラーを返した場合の条件をしていする関数

=IF(ISERROR(MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,LEFT(B2,FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)))

これでも「IFERROR関数」を使った場合と同じ結果が得られます。

まだまだ複雑な条件なんだよ！という場合

ここに２語や１語のキーワードの場合が加わると更に複雑になります。
「SUBSTITUTE関数」で置換する文字の数も変わってきます。

例題のケースを見てみましょう。
４つ目の「_」が無く、エラーを返す場合は

頭に何も付いていない３単語の場合
頭に何か付いている２単語の場合

の２パターンに分かれます。
今回のケースで言えば頭にキーワード以外の何かが付いている場合は必ず「【」が含まれています。
つまり広告グループ名に「【」が含まれている場合はA、そうじゃない場合はBという式を用意する必要がありますね。

【注力】_リスティング_運用代行_名古屋_フレーズ一致
【BIG】_PPC_名古屋_フレーズ一致
PPC_運用代行_東京_フレーズ一致

この３パターンの広告グループで広告文を作ってみましょう。

特定の文字が含まれるかを判断する

特定の文字が含まれているかを調べる場合には「COUNTIF関数」を使います。

「COUNTIF関数」
特定の条件を満たすセルをカウントする関数

今回はセルの中に「【」が含まれているかを見るため

=COUNTIF(B2,”【”)

としてみます。
しかしこれでは帰ってくる結果は「０」。
「COUNTIF関数」は条件を満たすものをカウントするため、「０」ということは条件を満たしていないという事です。
しかし、参照している文字列は「【注力】_リスティング_運用代行_名古屋_フレーズ一致」です。
「【」は含まれていますよね、しかし何故反応しないのか。
それは上記の式は「【」を含む、ではなく「【」と等しいという式になっているためです。

=COUNTIF(B2,”*”&”【”&”*”)

「”*”」で囲うことによって「含む」という意味合いになります。
これで結果は「１」となります。
「COUNTIF関数」は条件を満たすセルの数をカウントするため、セルの中に同じ文字列がどれだけ含まれていても１つのセルを指定している場合は「１」を返します。

広告文を用意する

さて、「【」が含まれている場合は「頭に何か付いている２単語の場合」です。
「【BIG】_PPC_名古屋_フレーズ一致」のパターンですね。

次に用意するのは２語のキーワードを挿入するための広告文と「SUBSTITUTE関数」です。
２語を挿入して「名古屋のPPC_なら」にしたいので「{2}の{1}なら」という広告文を用意します。

=SUBSTITUTE(SUBSTITUTE(“{2}の{1}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1))

「IF関数」で分岐する

３パターンの分岐ですのでかなり複雑になります。
ちょっとまとめましょう。

４つ目の「_」がエラーで無い場合
４つ目の「_」がエラーの場合かつ「【」が含まれている場合
４つ目の「_」がエラーの場合かつ「【」が含まれない場合

これを「IF関数」で分岐させて行きます。
まずはエラーを分岐させましょう。

=IF(ISERROR(MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1))),エラーの場合,SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)))

まずはこうなります。
次に「【」が含まれているかどうかの「IF関数」を作ってみます。

=IF(COUNTIF(B2,”*”&”【”&”*”)=1,含まれる場合の広告,含まれない場合の広告）

これに上で作ったエラーの場合の広告文と、２単語の場合の広告文を入れてみます。

=IF(COUNTIF(B2,”*”&”【”&”*”)=1,SUBSTITUTE(SUBSTITUTE(“{2}の{1}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,LEFT(B2,FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)))

さて、最後に上の関数を合体させて完成です。

=IF(ISERROR(MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1))),IF(COUNTIF(B2,”*”&”【”&”*”)=1,SUBSTITUTE(SUBSTITUTE(“{2}の{1}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,LEFT(B2,FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1))),SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(“{3}の{1}{2}なら”,”{1}”,MID(B2,FIND(“_”,B2)+1,FIND(“_”,B2,FIND(“_”,B2)+1)-FIND(“_”,B2)-1)),”{2}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-FIND(“_”,B2,FIND(“_”,B2)+1)-1)),”{3}”,MID(B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)+1)-FIND(“_”,B2,FIND(“_”,B2,FIND(“_”,B2)+1)+1)-1)))

あとは下までセルを引っ張ってください。