Excel VBA

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

Excelで先頭の文字を置換/追加したいときはないでしょうか。

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

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

・Excelの先頭の文字を置換/追加したいが方法がわからない。
・VBAで先頭の文字を一括で置換/追加したいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する
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)を設定します。

objRegExp.IgnoreCase = False

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

objRegExp.Global = True

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

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

objRegExp.Pattern = Sheets(“元シート”).Range(“E1”).Value

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

strRegWord = Sheets(“元シート”).Range(“E2”).Value

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

VBAを実装し実行する

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

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

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

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

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

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

サンプルでＡ列の値をＢ列、Ｃ列にコピーします。

値がある範囲をマウスなどで選択します。

VBAを実行すると、

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

VBAの説明

Excelで先頭の文字を置換/追加するVBAについて説明をします。

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

Set objRegExp = CreateObject(“VBScript.RegExp”)

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

Set regRange = Selection

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

For Each objParagraph In regRange

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

objParagraph.Value = objRegExp.Replace(objParagraph.Value, strRegWord)

別シートの文字列の先頭文字を置換する

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

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

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

Sheets(“追加シート”).Range(“A1:A5”)

実行してみましょう。

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

特定の先頭文字を特定文字に置換する

「も」がつく先頭文字だけ特定文字へ置換していきます。

Excelの元シートに戻り、

「置換パターン」に「^も」、「置換後の文字」は変わらず「・」を指定します。

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

Set regRange = Selection ‘マウスやキーボード操作で選択した範囲が置換対象となります。

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

はい、先頭文字が「も」の部分だけ置換後の文字に変わりましたね。

アルファベットの先頭文字を特定文字に置換する

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

Excelの元シートに戻り、

「置換パターン」に「^[a-z]」、「置換後の文字」は変わらず「・」を指定します。

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

Set regRange = Selection ‘マウスやキーボード操作で選択した範囲が置換対象となります。

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

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

数字の先頭文字を特定文字に置換する

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

Excelの元シートに戻り、

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


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

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

はい、先頭の数字の部分だけ置換後の文字に変わりましたね。

2桁以上の数字の先頭文字を特定文字に置換する

先頭文字にある1-9までの数字を対象に特定文字へ置換していきましたが、次は2桁以上の数字を対象に置換していきます。

サンプルでは3桁の数字を入力しています。

なお、上記の「置換パターン」に「[0-9]」をした状態で2桁維持用の数字を置換しようとすると、以下のようになります。

↓

タカヒロ
タカヒロ
先頭の1文字のみが置換対象になっているという訳ですね。

Excelの元シートに戻り、

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

タカヒロ
タカヒロ
数字の桁数に応じて{*}の数字を変更してください。

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

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

はい、先頭の3桁の数字の部分だけ置換後の文字に変わりましたね。

VBAの実装手順

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

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

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

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

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

こちらで完了です。

VBAを実行する

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

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

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

さいごに

いかがでしょうか。

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

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



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。


「置換」の関連記事

こちらの記事の内容「置換」に関連する記事一覧です。






コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

CAPTCHA ImageChange Image

RECOMMENDこちらの記事も人気です。