Excelの標準機能の置換では正規表現が使えない
ブックの複数のシートや一部のシート、または、指定したセル範囲の中にあるセルから、一部の文字列を削除したり別の文字列に置換したい場合があります。
誤記や内容変更などによるものが多いでしょうね。
Excelでそれを行う場合は置換機能を利用すると実現できます。Ctrl + Fキーで検索と置換ダイアログを開き、置換タブに切り替えて「検索する文字列」欄に削除したい文字列を書いて、置換後の文字列」欄を空欄にして、「次を検索」ボタンを押して「置換」ボタンを押すと削除および置換が行われます。
通常はこれでいいのですが、正規表現が使えないというのは不便ですし、自動実行しなければならない場合はやはりVBAを使った方がラクになります。
以下にセル範囲から指定した文字列を削除するマクロを紹介します。正規表現の利用はオプションになっており、正規表現が分からなくても利用できます。
事前設定
以下のVBAコードは事前に正規表現の参照設定をしておく必要があります。
VBAの画面でツールメニュー→参照設定のダイアログを開き、「Microsoft VBScript Regular Expressions 5.5」にチェックを付けることで正規表現のRegExpクラスが利用できるようになります。
指定セル範囲から削除対象文字列を別の文字列に置換するマクロ
以下の関数は、指定セル範囲にある全てのセルから削除対象文字列を置換後文字列に置き換えます。
正規表現も利用することが出来ます。正規表現のパターンはRegExpクラスに準拠します。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 |
'// 引数1：セル範囲 '// 引数2：削除対象文字列 '// 引数3：置換後文字列 '// 引数4：正規表現利用フラグ（True：利用する、False（既定）：利用しない） Sub DeleteString(r As Range, delString, replaceString, Optional bRegExpFlg = False) Dim reg As New RegExp '// 正規表現クラスオブジェクト Dim rCell As Range '// 指定セルオブジェクト '// 削除対象文字列が未設定の場合は処理しない If (delString = "") Then Exit Sub End If '// 正規表現での削除時 If (bRegExpFlg = True) Then '// 検索条件＝削除対象文字列パターン reg.Pattern = delString '// 文字列の最後まで検索する reg.Global = True End If '// 指定セル範囲を１セルずつループ処理 For Each rCell In r '// 正規表現での削除時 If (bRegExpFlg = True) Then '// セルの文字列から削除対象文字列パターンを置換後文字列に置換 rCell.Value = reg.Replace(rCell.Value, replaceString) Else '// セルの文字列から削除対象文字列を置換後文字列に置換 rCell.Value = Replace(rCell.Value, delString, replaceString) End If Next End Sub |
処理の概要は、引数1のセル範囲の文字列を、引数2の削除対象文字列を引数3の置換後文字列に置換する、という内容です。
また引数4の正規表現フラグで正規表現を使う場合かそうでないかを処理中のIf文で分岐しています。
処理の内容はコメントを参考にしてください。
利用方法
|
1 2 3 4 5 6 7 |
Sub DeleteStringTest() '// ひらがなを削除 Call DeleteString(Range("A1:A9"), "[あ-ん]", "", True) '// "セル"→"Cell"に置換 Call DeleteString(Range("A1:A9"), "セル", "Cell") End Sub |
ひらがなを削除し、「セル」を「Cell」に置換しています。
正規表現を利用しない場合は6行目のように4番目の引数は省略して構いません。
実行前
実行後
このページの先頭あたりをサンプルとして利用しています。
こんな感じで削除、置換が実行されます。このサンプルはひらがなを削除したためか、中国語みたいになっちゃいました。