Excelでテキストの一部を任意の文字列に置き換え

質問

無知な質問ですいません。どなたかお知恵を拝借できれば幸いです。

例えばC列の「本日オススメのフルーツは【オススメ1】と【オススメ2】です。」
というテキストの一部分だけ任意の文字列に置き換えたい場合

A列に【オススメ1】の果物名、B列に【オススメ2】の果物名を入れると
自動でテキストの一部分だけを入れ替える事は出来るでしょうか？

色々方法を検索して試したのですが、以下の様なマクロを使うと【オススメ1】
をA列に入れた文字列に置き換え出来ます。

sub macro1()
　dim h as range
　for each h in range("A1:A" & range("A65536").end(xlup).row)
　if h <> "" then h.entirerow.replace what:="【オススメ1】", replacement:=h.value, lookat:=xlpart
　next
end sub

しかし、こちらは一度実行してしまうと値を元に戻せない為、繰り返しのA列の果物を
更新したい場合に向いていません。

あとはテキストを以下の様に分解して再度繋げる方法ですが、この手法は出来れば使いたくありません。
「本日オススメのフルーツは」「【オススメ1】」「と」「【オススメ2】」「です。」

ネットで検索して色々試してみたのですが、イマイチいい方法が見つかりません。

打開策ございましたらご教授の程、よろしくお願いいたします。

回答No.2

kagakusuki · ベストアンサー

>HTMLソースには【"】や【&】といった記号がよく登場するもののですから、お教えいただいた関数では狙い通りの結果が得られないかと思いまして

　別に取り扱う文字列データの中に含まれている特定の文字列を置換するだけなのであって、ワークシート関数そのものをどうこうする訳ではないのですから、【"】や【&】が含まれているからと言って関数で置換出来なくなる訳ではありません。
　御質問の件の場合、D2セルに次の関数を入力してから、D2セルをコピーして、D3以下に貼り付けると良いと思います。

=SUBSTITUTE(SUBSTITUTE($C2,$A$1,$A2),$B$1,$B2)

　尚、例えば【=A1】などの様に【=】で始まっている文字列を入力する際には、文字列を入力するためのセルの書式設定の表示形式を[文字列]に設定してから入力されるか、もしくは

'=A1

などの様に先頭に半角の【'】(アポストロフィー)を付けた形で入力して下さい。

お礼

正に狙い通りの結果が得られました！
ご助力に心より感謝申し上げます。m(_ _ )m

回答No.5

bunjii

回答No.4の一部に誤りがありましたので訂正します。
『注意すべきことは置換前のセルと置換後のセルおよび置換する文字列のセルの表示形式を「文字列」にしておくことです。』
『関数で処理するときに注意すべきことは置換前のセルと置換する文字列のセルの表示形式を「文字列」にしておくことです。』

回答No.4

bunjii

>色々方法を検索して試したのですが、以下の様なマクロを使うと【オススメ1】をA列に入れた文字列に置き換え出来ます。
それでよいのではないですか？

>しかし、こちらは一度実行してしまうと値を元に戻せない為、繰り返しのA列の果物を更新したい場合に向いていません。
元データをテンプレートとして保存し、加工後のデータを使い捨てにすれば解決できるでしょう。

回答No.1への「お礼」は条件の後出しなので好ましくありません。
重要な条件は最初から提示するようにしてください。
>そしてHTMLソースには【"】や【&】といった記号がよく登場するもののですから、お教えいただいた関数では狙い通りの結果が得られないかと思いまして…
「関数では狙い通りの結果が得られない」と思うだけではなく実行してみないと可否がわかりません。
注意すべきことは置換前のセルと置換後のセルおよび置換する文字列のセルの表示形式を「文字列」にしておくことです。

一般的にはWebコンテンツで表示内容を自動的に変更する手法はPerl、Java等のプログラムで組み立てるようです。
Webコンテンツの全文をVBAでも作成可能かと思います。（私は経験ありません）

お礼

＞回答No.1への「お礼」は条件の後出しなので好ましくありません。
重要な条件は最初から提示するようにしてください。

大変申し訳ございませんでした。以後、必ず注意いたします。
アドバイスありがとうございました。

回答No.3

kagakusuki

回答No.2です。

>こちらは一度実行してしまうと値を元に戻せない為、繰り返しのA列の果物を更新したい場合に向いていません。

　それでしたら、元データであるC列の文字列を変更するのではなく、置換後の文字列を(例えばD列等の)A列～C列以外の列に表示させる様にすれば済む話なのではないでしょうか？

Sub QNo9235584_Excelでテキストの一部を任意の文字列に置き換え()

Const FirstRow = 2 '元データである置換対象となる文字列が入力されている最初の行
Const OriginalColumn = "C" '元データである置換対象となる文字列が入力されている列
Const OutputColumn = "D" '置換した結果の文字列を出力する列

Dim i As Long, j As Long, LastRow As Long, buf As Variant, _
SearchCell As Variant, ReplaceColumn As Variant, SearchString() As Variant

SearchCell = Array("A1", " B1") '「『元データである置換対象となる文字列』の中に含まれている検索する文字列」が入力されているセル
ReplaceColumn = Array("A", "B") '置換後の文字列が入力されている列
ReDim SearchString(UBound(SearchCell))
For i = 0 To UBound(SearchString)
SearchString(i) = Range(SearchCell(i)).Value
buf = buf & SearchString(i)
Next i

LastRow = Range(OriginalColumn & Rows.Count).End(xlUp).row
If LastRow < FirstRow Or buf = "" Then
MsgBox "処理すべきデータが見当たりませんません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "データ無し"
Exit Sub
End If

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

For i = FirstRow To LastRow
buf = Range(OriginalColumn & i).Value
If buf <> "" Then
For j = 0 To UBound(SearchString)
If buf <> "" And SearchString(j) <> "" Then _
buf = Replace(buf, SearchString(j), Range(ReplaceColumn(j) & i).Value)
Next j
Range(OutputColumn & i).Value = buf
End If
Next i

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

お礼

マクロに関してもアドバイスをくださり、本当にありがとうございました。
こちらの手法も試してみたいと思います。重ねてお礼申し上げます。

回答No.1

panacon

敢えてVBでマクロを組む必要があれば別ですが、単純にC2に関数を書いてはダメなのでしょうか。言われていることは、下記でできますが、VBの練習でしたらすみません。
="本日オススメのフルーツは" & A2 & "と" & B2 & "です。"

お礼

ご回答ありがとうございます。大変申し訳ございません。私の説明が不足していました。

今回、質問しやすい様にあえてフルーツのテキストを作りましたが、
実際はサイト更新用のHTMLソースをエクセル上で分かりやすく管理したいというのが
狙いでございまして、【桃】や【梨】といった文字列は実際にはリンク用のURLやら画像のパスに置き換わる予定です。

そしてHTMLソースには【"】や【&】といった記号がよく登場するもののですから、お教えいただいた関数では狙い通りの結果が得られないかと思いまして…申し訳ありません。