Excelマクロ・VBA 複数の文字列を置換(SUBSTITUTE関数を複数)する方法

2019年8月8日2020年12月7日

文字列をマクロ（VBA）なしで複数文字列を置換する方法を紹介しましたが、

関数が冗長になるため保守性がとても悪いです。

もしVBAを使うことに支障や抵抗がなければユーザ定義関数を使用すると

効率的に実現することが可能です。

サンプルファイル

使用方法

※セキュリティの警告が出る場合は有効化してください。（解除方法↓）

Excelマクロ・VBA 最初のプログラム

サンプルファイルの中に保存されているユーザ定義関数MultipleSubstituteを利用します。

=MultipleSubstitute(Str,RuleList)

引数	省略時の値	説明
Str	省略不可	対象文字列を指定。
RuleList	省略不可	検索文字列と置換文字列のセル範囲を指定。

RuleListは1列目に検索文字列を指定し、2列目には置換文字列を指定します。

下のサンプルでは「猫」が「虎」に置換され、「犬」が「狼」に置換されます。

コード

コードは下記のとおりです。

標準モジュールを追加してペーストすれば任意のブックで使用可能です。

Option Explicit

Function MultipleSubstitute(str As String, ruleList As Range)
    ' 変換ルールの行数繰り返す 
    Dim rowIndex As Integer ' 変換ルールのループカウンター
    For rowIndex = 1 To ruleList.Rows.Count
        Dim strSrc As String ' 検索文字列
        Dim strTarget As String ' 置換文字列

        strSrc = ruleList.Cells(rowIndex, 1) ' 検索文字列を取得
        strTarget = ruleList.Cells(rowIndex, 2) ' 置換文字列を取得

        str = Replace(str, strSrc, strTarget) ' 文字列置換を実行
    Next

    MultipleSubstitute = str ' 置換結果をセルに表示
End Function

コメント回答…空白セルの場合に任意の文字列に置換(2020/12/07)

結論として空のセルの場合、置換できません。

IF関数と組み合わせる必要があります。

=IF(B3="","セルが空白です。",MultipleSubstitute(B3,E3:F4))

あくまでVBAで実施する場合、3つ目の引数を追加する形で改修します。

Option Explicit

Function MultipleSubstitute(str As String, ruleList As Range, brankStr As String)
        
    ' 変換ルールの行数繰り返す
    Dim rowIndex As Integer ' 変換ルールのループカウンター
    For rowIndex = 1 To ruleList.Rows.Count
        Dim strSrc As String ' 検索文字列
        Dim strTarget As String ' 置換文字列
    
        strSrc = ruleList.Cells(rowIndex, 1) ' 検索文字列を取得
        strTarget = ruleList.Cells(rowIndex, 2) ' 置換文字列を取得
        
        If str = "" Then
          str = brankStr ' 空白セルのため固定値
        Else
          str = Replace(str, strSrc, strTarget) ' 文字列置換を実行
        End If
        
    Next
    
    MultipleSubstitute = str ' 置換結果をセルに表示
End Function

=MultipleSubstitute(B3,E3:F4,"セルが空白です。")

関連記事

マクロ・VBAの学習・活用方法の記事一覧

Excel マクロ・VBAの学習・活用方法の記事一覧