Excelで末尾の文字を置換/追加/削除する方法！VBAと正規表現で一括処理！

Excelで末尾の文字を置換/追加/削除したいときはないでしょうか。

例えば商品名などリストがあり共通の文字を末尾に付け加えたいときなどです。

けど、そんな中で悩むことは、

ですよね。

今回はそんなお悩みを解決する
Excelで末尾の文字を置換/追加/削除する方法について
まとめます！

VBAでExcelの末尾の文字を置換/追加/削除するイメージ

VBAでExcelの末尾の文字を置換/追加/削除するイメージについて説明をします。

まず、末尾に文字を追加したい文字列を複数用意します。

置換パターンを末尾を意味する正規表現と置換後の文字を入力します。

置換したい箇所をマウス等で選択します。

VBAを実行すると、

はい、末尾部分が置換後の文字へ置換/追加できました。

置換処理の範囲の拡張もマウスでカンタンに変えられます。

複数列を選択しVBAを実行すると、

複数列も同じく置換/追加されています！

文字追加以外にも末尾の指定文字を置換/削除したり、

末尾の「ジュース」だけ置換/削除されました。

また、末尾の数字のみを置換/削除対象にしたり、

末尾の数字のみが置換/削除されましたね。

アルファベットと数字の混合も、

難なく置換/削除することができます！

そんな便利な置換をインストールなしにサクッと実現できてしまうというわけです。

それでは早速使ってみましょう。

末尾文字を置換するデータを用意する

まずは、末尾文字を置換するデータを用意しましょう。

シート名は「元シート」とし、A1からA5セルまでデータを入力していきます。

サンプルは以下のようにしました。

末尾文字を置換する置換パターンの正規表現と置換後の文字を入力する

同じシートに末尾文字を置換する置換パターンの正規表現と置換後の文字を入力しましょう。

サンプルではE1セルに置換パターン「^」、E2セルに置換後の文字「・」を入力しています。

Excelで末尾の文字を置換/追加/削除するVBA

Excelで末尾の文字を置換/追加/削除するVBAを準備します。

サンプルコードは以下の通りです。

Sub RegExpで正規表現を使い置換する()

    'RegExpをオブジェクトにセットします｡　https://docs.microsoft.com/ja-jp/visualstudio/ide/using-regular-expressions-in-visual-studio?view=vs-2019
    Set objRegExp = CreateObject("VBScript.RegExp")
    
    '置換範囲を設定します。
    Set regRange = Selection 'マウスやキーボード操作で選択した範囲が置換対象となります。
    'Set regRange = Range("A1:A5") 'Range指定した置換対象となります。
    'Set regRange = Sheets("追加シート").Range("A1:A5") '別シートのRange指定が置換対象となります。
    
    '検索するパターンを設定します。
    objRegExp.Pattern = Sheets("元シート").Range("E1").Value
    
    '置換後の文字を指定します。
    strRegWord = Sheets("元シート").Range("E2").Value
    
    '大文字と小文字の区別をする(既定値：False)かしないか(False)を設定します。
    objRegExp.IgnoreCase = False
    
    '検索文字列全体を検索する(True)か、最初の一致を検索する(既定値：False)かを設定します。
    objRegExp.Global = True
    
    '範囲分置換処理を繰り返えします。
    For Each objParagraph In regRange
    
        '変換後の文字列と変換前の文字列を置き換えます。
          objParagraph.Value = objRegExp.Replace(objParagraph.Value, strRegWord)

    Next
    
    Set objRegExp = Nothing
    Set objParagraph = Nothing

End Sub

VBAの設定をする

Excelで末尾の文字を置換/追加/削除するVBAの設定を行っていきましょう。

設定箇所は以下の通りです。
大文字と小文字の区別をする(既定値：False)かしないか(False)を設定します。

検索文字列全体を検索する(True)か、最初の一致を検索する(既定値：False)かを設定します。

以下はサンプルと同じセル、シート名であれば変更する必要がありませんが、参照先が異なる場合はこちらも設定変更をしてください。

検索するパターンを設定します。

置換後の文字を指定します。

タカヒロ

置換パターンと置換後の文字の指定はVBAに直接入力していただいても問題ありません。

VBAを実装し実行する

VBAを実装し実行してみましょう。

実装方法は「VBAの実装手順」をご覧ください。

実装できたらマクロから「RegExpで正規表現を使い置換する」を見つけ実行します。

はい、末尾文字に置換後の文字が追加されていますね！

複数列を選択して実行する

現設定はマウスによる選択範囲を置換対象としています。
次に複数列を選択して置換処理を実行していましょう。

サンプルでＡ列の値をＢ列、Ｃ列にコピーし、マウスなどでセルを選択します。

VBAを実行すると、

はい、複数列もまとめて置換/追加されていますね。

VBAの説明

Excelで末尾の文字を置換/追加/削除するVBAについて説明をします。

今回置換処理に使う機能はVBScriptで用意されているRegExpオブジェクトとなります。RegExpを呼び出し、VBAのオブジェクトにセットします｡

置換範囲を設定します。マウスやキーボード操作で選択した範囲が置換対象となりますが、Renge指定や別シートのRenge指定もできます。詳細は下記をご参照ください。

範囲分置換処理を繰り返えします。

変換後の文字列と変換前の文字列を置き換えます。

別シートの文字列の末尾文字を置換する

続いて、これまで今見ているアクティブシートの選択範囲を置換処理の対象としてきましたが、別シートにある文字列を置換対象にするよう変更をしてみましょう。

シートを追加します。サンプルでは「追加シート」としています。文字列の範囲はRange(“A1:A5”)とします。

以下のコードをアクティブにします。

実行してみましょう。

はい、別シートの指定範囲が置換されていますね。

末尾の特定文字列を置換/削除する

末尾の特定文字列だけを置換/削除していきます。

Excelの元シートに戻り、

「置換パターン」に「^」、「置換後の文字」は「ジュース」にして先頭に「ジュース」を追加します。

続いて「置換パターン」に「ジュース$」、「置換後の文字」は「」にして末尾の「ジュース」のみを置換対象にし削除していきます。

タカヒロ

1行に同じ文字列が先頭と末尾にある場合、通常の置換処理をすると同じ文字列すべてが置換対象になりますが、
正規表現をつかうと末尾の文字列のみという置換対象を限定的にすることができるということですね。

VBAは元シートを参照するよう設定を戻します。

置換対象範囲を選択し、VBAを実行します。

はい、末尾の文字「ジュース」の部分だけ置換後の文字に変わりましたね。

数字の末尾文字を特定文字に置換/削除する

末尾文字にある1-9までの数字を対象に特定文字へ置換していきます。

Excelの元シートに戻り、

「置換パターン」に「[0-9]{3}$」、「置換後の文字」は変わらず「」を指定します。

タカヒロ

数字の桁数に応じて{*}の数字を変更してください。また桁数が一定でない場合は「[0-9]+$」でも可能です。

VBAは元シートを参照するよう設定を戻します。

置換対象範囲を選択し、VBAを実行します。

はい、末尾の数字の部分だけ置換後の文字に変わり削除されましたね。

アルファベットと数字混合の末尾文字を特定文字に置換する

末尾文字にあるアルファベットa～zまでの小文字と数字を対象に特定文字へ置換していきます。

Excelの元シートに戻り、A列に英数字混合の文字列を追加します。

「置換パターン」に「[a-z]{2}[0-9]{3}$」、「置換後の文字」は変わらず「」を指定します。

タカヒロ

置換対象のアルファベットの文字数に応じて[a-z]{*}の数字を変えてください。

VBAは元シートを参照するよう設定を戻します。

置換対象範囲を選択し、VBAを実行します。

はい、アルファベットの部分だけ置換後の文字に変わり削除されましたね。

VBAの実装手順

実装手順は以下の通りです。

今回はExcel側にこのVBAを実装します。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。
※完了メッセージやステータス管理など必要に応じて実装してもらえばと思います。

さいごに

いかがでしょうか。

今回は、
Excelで末尾の文字を置換/追加/削除する方法について
まとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。

タカヒロ

関数で末尾の文字列を削除したい場合はこちらをご参照ください。

Excelで末尾の特定文字を削除する方法！関数とVBAで複数一括処理も！