マクロで文字列の置換はVBA関数のReplace関数1択
本記事では「文字列の置換」について説明します。VBAでも置換は可能です。
VBA初心者でもワークシート関数に詳しい方であれば比較的簡単なコードで作業を構築できます。しかし問題もあります。
置換についてはどの方法を使えばよいのか分からないという質問が多いです。実際に方法は複数存在します。
まずは問い合わせがあった方法を4点並べてみます。結論から申し上げますと「どれでもOK」です。
- VBA関数のReplace関数
- RangeオブジェクトのReplaceメソッド
- ワークシート関数のReplaceメソッド
- ワークシート関数のSubstituteメソッド
私個人としてはVBA関数のReplace関数1択です。
ポイントは「コードの書きやすさ」と「配列」です。
本記事ではまず4つの方法を深堀りします。そのあとで情報を総括して結論を出します。
最後に4つの中からおすすめの「VBA関数のReplace関数」をピックアップして事例を紹介します。
関連記事
置換と一緒に行う作業はやはり「転記」ですよね。
コピーして値を加工後（置換後）貼り付け先に転記するという行為は多くの皆様が経験されている事でしょう。
関連記事としてコピペや転記の記事を用意させていただきました。本記事と合わせてご覧ください。
コピー&ペーストがわかるVBA×コピペ｜値のみ貼り付け＆簡単高速にコピーできる方法を公開
ブック間の転記についてVBAで転記｜ブック間で文字列をコピペするマクロの作り方を教えます
関連書籍
こちらの書籍ではReplace関数をシンプルに説明しています。
加えて事例形式で便利な機能をたくさん紹介しているのでおすすめ書籍として紹介させていただきました。
ポチップ
おすすめは電子書籍です。私大半の書籍は電子書籍で購入しています。
文字列を置換する4つの方法
まずは一目で情報を整理できる様に表を用意しました。こちらをご覧ください。
| 番号 | 大分類 | 小分類 | 採用可否 | 詳細（使い時など） |
|---|---|---|---|---|
| 1 | VBA関数 | Replace関数 | 採用 | コーディング時の書きやすさと汎用性の高さから採用 |
| 2 | Rangeオブジェクト | Replaceメソッド | 不採用 | 4つの方法の中で唯一配列と一緒に使えないので不採用 |
| 3 | ワークシート関数 | Replaceメソッド | 不採用 | 1番の下位互換なので不採用 |
| 4 | ワークシート関数 | Substituteメソッド | 不採用 | 1番と使い方が同じなので不採用 |
以降1から4番の情報を順番に説明していきます。結論としては1番の方法しか使わないのですが比較の為すべて説明します。
1_VBA関数のReplace関数
まずは構文とパラメータです。Microsoftのドキュメントから引用します。
構文Replace(expression, find, replace, [ start, [ count, [ compare ]]])
Microsoft：Replace 関数より引用
パーツ 説明 expression 必須。 置換処理を行うサブ文字列が格納されている 文字列式。 find 必須。 検索するサブ文字列。 replace 必須。 置換後の文字列。 start 省略可能。 検索して返される 式 の部分文字列の開始位置。 省略した場合は、1 が使用されます。 count 省略可能。 実行するサブ文字列置換の回数。
省略すると、既定値は -1 になります。つまり、可能なすべての置換を行います。compare 省略可能。 サブ文字列を評価するときに使用する比較の種類を示す数値です。
値については、「設定」セクションを参照してください。
引用先にはパラメータ（compare）の詳細が掲載されています。必要に応じてご覧ください。
ポイント：一括で文字列を置換／指定した文字列だけ置換
基本的にはパラメータ3つだけで置換が可能です。非常にわかりやすくコードもシンプルです。
パラメータexpressionの中にあるパラメータfindで指定した文字列をパラメータreplaceに一括で置換します。
比較的簡単に文字列を置換したい時におすすめの方法です。
加えて配列との相性も良いので私はこの方法を採用しています。
さらにパラメータfindと一致している文字がいくつかあるときは個別に置換することも可能です。汎用性も高いです。
個別に置換する方法は以降事例の中で紹介します。
2_RangeオブジェクトのReplaceメソッド
構文とパラメータです。Microsoftのドキュメントから引用します。
構文Replace (What、 Replacement、 LookAt、 SearchOrder、 MatchCase、 MatchByte、 SearchFormat、 ReplaceFormat)
Microsoft：Range.Replace メソッド (Excel)から抜粋
名前 必須 / オプション データ型 説明 What 必須 バリアント型 Microsoft Excel で検索する文字列。 Replacement 必須 バリアント型 (Variant) 置き換える文字列を指定します。 LookAt 省略可能 Variant 次のXlLookAt定数:xlWholeあるいはxlPartのいずれかになります 。 SearchOrder 省略可能 Variant 次のXlSearchOrder定数:xlByRowsあるいはxlByColumnsのいずれかになります 。 MatchCase 省略可能 Variant 大文字と小文字を区別するには、True を指定します。 MatchByte 省略可能 バリアント型 この引数は、Microsoft Excel で 2 バイト言語サポートを選択またはインストールした場合にのみ使用します。
2 バイト文字が 2 バイト文字とだけ一致するようにする場合は、True を指定します。
2 バイト文字が同等の 1 バイト文字とも一致するようにする場合は、False を指定します。SearchFormat 省略可能 バリアント型 (Variant) メソッドの検索書式を指定します。 ReplaceFormat 省略可能 バリアント型 (Variant) このメソッドの置換書式。
引用先にはより詳しい情報が掲載されています。必要に応じてご覧ください。
ポイント：配列が使えない
たくさんパラメータ（引数）を持っているので複雑なことも出来ますが致命的な欠点があります。
Rangeオブジェクトに所属するメソッドなのでパラメータWhatはセルに用意された文字列になります。
よって配列との相性が悪いので私は不採用にしています。配列を使わない方には関係ないので必要に応じてご使用ください。
配列について知りたい方はこちらの記事をご覧ください。
配列初級編【検索にも使える】VBAの2次元配列で作業の高速化を実現させる
こちらの記事には1つの処理に対して配列を使ったコードと配列を使わないコードを用意しています。
コピペで使えるようにしてありますので簡単に配列の処理速度を体感することが出来ます。きっと驚きますよ。
3_ワークシート関数のReplaceメソッド
構文とパラメータです。Microsoftのドキュメントから引用します。
構文Replace (Arg1、 Arg2、 Arg3、 Arg4)
Microsoft：WorksheetFunction.Replace メソッド (Excel)から抜粋
名前 必須 / オプション データ型 説明 Arg1 必須 文字列型 (String) 置き換えを行う文字列を指定します。 Arg2 必須 Double Arg4 と置き換える Arg1 内の文字の位置を指定します。 Arg3 必須 Double Replace メソッドで Arg4 と置き換える Arg1 内の文字の数を指定します。 Arg4 必須 String Arg1 の一部と置き換える文字列を指定します。
引用先のドキュメントには事例も掲載されています。必要に応じてご覧ください。
ポイント：VBA関数のReplace関数の下位互換
一番シンプルで分かりやすいのがこの方法の最大のメリットです。加えて配列との相性も良いです。
- パラメータArg1の中に置換対象の文字が1つ
- 置換対象の文字位置に規則性があり毎回同じ位置
2つの要件が成立している時にはこの方法を採用しても良いのですがVBA関数のReplace関数でも同じことが出来ます。
よって積極的には使いません。基本は不採用です。
ワークシート関数なのでコーディング時は文頭に「WorksheetFunction・・・」と書く必要があります。
字数も多くコードも読みにくくなるので使わない事が多いです。やはり基本は不採用です。
4_ワークシート関数のSubstituteメソッド
構文とパラメータです。Microsoftのドキュメントから引用します。
構文（一部加工しました）Substitute(Arg1、 Arg2、 Arg3、 Arg4)
Microsoft：WorksheetFunction.Substitute メソッド (Excel)から抜粋
名前 必須 / オプション データ型 説明 Arg1 必須 文字列型 (String) 文字列: 置き換える文字を含む文字列を指定します。
目的の文字列が入力されたセル参照を指定することもできます。Arg2 必須 String Old_text – 置き換えるテキスト。 Arg3 必須 String New_text – old_textに置き換えるテキスト。 Arg4 省略可能 バリアント型 (Variant) 置換対象: 文字列に含まれるどの検索文字列を置換文字列に置き換えるかを指定します。
置換対象を指定した場合、文字列中の置換対象番目の検索文字列だけが置き換えられます。
この指定を行わないと、文字列中のすべての文字が新しい文字に置き換えられます。
ポイント：VBA関数のReplace関数と同じ
ワークシート関数のSubstituteメソッドはVBA関数のReplace関数と同じ機能なので不採用です。
VBA関数のReplace関数を選ぶ理由はコーディング時の文字数です。
3番のコードと一緒でSubstituteメソッドもワークシート関数です。文頭に「WorksheetFunction・・・」が必要です。
1番のコードはVBA関数なので「WorksheetFunction・・・」が不要です。
総括：VBA関数のReplace関数を使いましょう
あくまで私の考えですが置換は「VBA関数のReplace関数」1択で良いです。
理由は各方法を説明する中で解説していますのでここでは省略します。
以降事例の中で実際にVBA関数のReplace関数を使ってみます。
備考：ワークシート関数はパラメータの表記が同じ
ワークシート関数を理解せずにVBAで使う時は注意が必要です。
ワークシート関数はパラメータが全てArg表記です。おそらくArgumentの略ですね。引数という意味です。
注意が必要な理由はこれです。コーディング時にパラメータが全てArg表記になってしまうという事です。
例えばパラメータが4つの時はVBEでのコーディング時に自動クイックヒントの表記がArg1,Arg2,Arg3,Arg4と表示されます。
これは上段で紹介した各方法のパラメータの名前と同じ表記で自動クイックヒントが生成されているだけです。
よって想定通りの表記なのですがちょっと不親切な感じを持ちます。
一方パラメータに固有名詞を持っているワークシート関数以外の要素であればどうでしょうか。
Microsoftのドキュメントに書かれている通りの情報が返ってきます。自動クイックヒントは以下画像をご覧ください。
一見情報量が多く感じるこの表記でも「パラメータが分かってなければ一緒でしょ？」という意見もあります。
最終的にはユーザー様の感じ方次第ですが私はこの点からも使用についてはVBA関数の方が良いなと感じました。
事例
Replace関数を使用するために事例を用意しました。
ワークシートの状態（文字列置換前）
セルに書き込むタイプのSubstitute関数の使用例の中にあった文字列を少し加工してサンプルとして使います。
ワークシートのセルB5に「2024 第 1 四半期」という文字列を用意しています。
全角、半角、スペース、数字、漢字という色々な要素が入っているので採用した次第です。
アルファベット（大文字、小文字）は含まれていませんが機能を知るうえでは良いサンプルです。
この文字列をオートフィルすると以下画像の様に文頭の年だけ進んでしまいます。
各年で第1四半期から第4四半期に値が変わってから年が1つ進むという具合にオートフィルは動いてくれない様です。
ワークシートの状態（文字列置換後）
この後紹介するコードを用いることで以下画像の様に5年分の文字列を用意することが出来ます。
Replace関数を使うことで赤枠内の文字列を作り出しています。
コード
コードはボタンでコピーすることが出来ます。必要に応じてご利用下さい。
Sub VBA関数のReplace関数()
Dim r As Long '行数を格納
Dim d_vle As Long 'default_value 1から4の値を格納
Dim f_vle As Long 'final_value 変数d_vleに1を足した値を格納
Dim d_stg As String 'default_string 最終行の値を格納
Dim f_stg As String 'final_string 加工後セルに代入する値を格納
'5年分の文字列を用意するためのループを用意
For r = 6 To 24
'最終行の値を変数に格納する
d_stg = Cells(r - 1, 2)
'変数f_vleの値で処理を分岐する
If f_vle = 4 Then
'数字を数値で取り出す
d_vle = Val(Mid(d_stg, 1, 4))
'数値同時で足し算
f_vle = d_vle + 1
'数字として戻す（年を進めた文字列を変数に格納する）
f_stg = Replace(d_stg, d_vle, f_vle)
'1に戻す（第 1 四半期に戻す）
f_stg = Replace(f_stg, "第 4", "第 1")
Else
'数字を数値で取り出す
d_vle = Val(Mid(d_stg, 9, 1))
'数値同時で足し算
f_vle = d_vle + 1
'数字として戻す（四半期を1つ進めた文字列を変数に格納する）
f_stg = Replace(d_stg, "第 " & d_vle, "第 " & f_vle)
End If
'セルに値を戻す
Cells(r, 2) = f_stg
Next
End Sub初心者向けの記事になることを想定してコメントを多めに書いています。
コメントを減らして行間を詰めればそんなにたくさんのことは書いてないです。
解説
ポイントは4つです。各ポイント毎に説明していきます。
- For～Nextステートメント
- Ifステートメント
- Mid関数
- Replace関数
1_For～Nextステートメント
５年分のデータを用意するためにループを準備します。今回はループの始点を「6」、終点を「24」に設定しました。
最初の文字列はセルB5に書いてあるので残りの19個の文字列を用意する必要がある為です。（1年で4行×5年-1）
For～Nextステートメントが分からない方はこちらの記事をご覧ください。
ローカルウインドウの使い方を含め丁寧に解説しています。For～Nextステートメントが苦手な方におすすめの記事です。
初心者向おすすめVBA-12繰り返し処理をFor～Nextステートメントで学習する
2_Ifステートメント
各年の第4四半期まで文字列を用意することが出来たら次の行は年を1つ進める必要があります。
年を1つ進めるためのスイッチを条件Trueの処理が担当しています。変数f_vleの値が4の時は以下処理を実行します。
- 1数字を数値で取り出す
Mid関数を使って数字（文字）を取り出した後にVal関数で数値にしてから変数d_vleに格納します。
- 2数値同時で足し算
1つ数値を進めたいので足し算をします。1を足す為に数値にした次第です。
- 3数字として戻す
Replace関数を使って文字列を置換します。
- 41に戻す
年を1つ進めた時は必ず第1四半期から始まるのでここでは強制的に値を1に戻します。
処理を画像にしてみました。IfステートメントのIfからElseの間に書かれた処理です。
一方変数f_vleの値が4以外の時は以下処理を実行します。
こちらのコードの集団は主に四半期の頭に配置される数値をコントロールするために用意されたコードになります。
- 1数字を数値で取り出す
Mid関数を使って数字（文字）を取り出した後にVal関数で数値にしてから変数d_vleに格納します。
- 2数値同時で足し算
1つ数値を進めたいので足し算をします 1を足す為に数値にした次第です。
- 3数字として戻す
Replace関数を使って文字列を置換します。
条件Trueの時と同様に条件Falseのケースも画像にしました。IfステートメントのElseからEnd Ifの間に書かれた処理です。
Ifステートメントを通過した後にセルに値を戻す（代入する）様にしています。
処理は必ずどちらかの分岐を通ります。結果5年分の文字列がセルに入力されることになります。
Ifステートメントが分からない方はこちらの記事をご覧ください。
MsgBox（メッセージボックス）と一緒に使い方を勉強できるような記事構成にしています。必要に応じてご覧ください
初心者おすすめVBA-11マクロ内でMsgBoxとIFステートメントを組み合わせる
3_Mid関数
セルに書き込むタイプのMid関数と使い方は同じです。
Mid関数についてはこちらの記事で解説しています。使った事がない方はご一読ください。
Mid関数の使い方VBAで文字列を取り出す複数のかっこの中の値を1クリックで集める
4_Replace関数
Replace関数の構文は先ほど紹介しましたのでここでは上級者向けReplace関数の使い方（応用編）を紹介します。
「指定した文字列だけ置換」という作業について解説します。
今回の事例では年と第1から第4四半期の情報を置換するというコードです。
この中で「2024 年第 2 四半期」を「2024 年第 3 四半期」に置換したい時に以下のようなコードを書いたとします。
Debug.Print Replace(“2024 年第 2 四半期”, 2, 3)
Debug.Printなのでイミディエイトウィンドウで確認してみましょう。
「3034 年第 3 四半期」という文字列が返ってきますね。期待した文字列ではないです。
「第 2 四半期」を「第 3 四半期」にしたいのですが文字列内の2が全て3に置き換えられています。
自分の思いとしては間違っているのですがコードとしては合っています。こんな時はどうしたら良いのでしょうか。
こんな時は「規則性がある周辺文字と一緒に置換」という方法を使います。
Debug.Print Replace(“2024 年第 2 四半期”, “第 2”, “第 3”)
「第」という文字とスペースを一緒にした”第 “という情報を数字を一緒に置き換えています。
この方法を使うことで2024年という文字列はそのままにして四半期の文字だけを変えることができました。
記事中段で紹介したコードもこの方法で置き換えを行っています。
コード内では変数と一緒に文字を使って置き換えを行っています。＆（アンパサンド）を使うところがポイントです。
&の使い方VBAで文字列を比較Like演算子とワイルドカードで完全／部分一致を確認【一覧で表示】
アンパサンドの使い方を紹介している記事を掲載しました。この記事にはワイルドカードに関する説明も掲載されています。
ワイルドカードは文字列を扱う際には必須となる要素です。置換にも活用できる機能ですよ。
まとめ
VBAを使った文字列の置換について勉強してきました。
今回紹介した4つのコードの様にVBAでは1つの作業に対して方法が複数用意されています。
4つの中から私はVBA関数のReplace関数を提示しましたがどれかが正しいという事はないです。
基本的にはどれも正しい選択肢です。最終的にはご自身が理解しやすい方法でコーディングしてください。
使いこんでくると私と違うことに気付いて「こっちのコードの方が良い」となるかもしれません。
その一連の行為がスキルアップにつながります。まずは回数をこなしましょう。
自分でコードを書いて文字を置き換えてみることが何より一番重要です。是非トライしてくださいね。