この関数は文字列の中から指定した文字列を検索し、置換する事ができます。開始位置等を指定しなければExcel関数のSUBSTITUTE関数の様な動きをし、開始位置や文字数を指定すると、Excel関数のREPLACE関数と同じように動くようになります。
- 開始位置を指定するかどうかで動きが変わる
- データをまとめて変更したい時に便利
- 分岐や反復と組み合わせるといろいろ出来る
Replace関数
基本構文
Replace(expression,find,replace[, start[, count[, compare]]])
引数となる英語が並んでいるので、意味をまとめたものが下の表になります。
引数 | 説明 | 省略 |
---|---|---|
expression | 置換対象文字列 | 不可 |
find | 検索文字列 | 不可 |
replace | 置換文字列 | 不可 |
start | 開始位置:省略すると1となる | 可 |
count | 実行回数:省略すると‐1で、対象を全て置換する | 可 |
compare | 大文字と小文字を区別するかどうかの設定 | 可 |
意味が分かったところで下記の引数3種を使用した必要最低限の使用方法をみていきます。
Replace(置換対象文字列,検索文字列,置換文字列)
「EXCEL」のEを検索し、eに置換する動作をプログラムに起こすとこんな感じになります。
Sub 置換()
Cells(2, 2) = Replace(Cells(2, 1)
, "E", "e")
End Sub
複数の置換対応
分岐や反復と組み合わせる事で、複数の文字列に対応する事が出来る様になります。今回はFor文とSelect Caseを使用して組んでみました。If文でも問題ありませんので、好みの方を使ってください。
文字列が1の場合は正解、2の場合は不正解と表示される物を作成してみました。
Sub 置換() Dim ir As Long Dim lRow As Long lRow = Cells(Rows.Count, 1).End(xlUp).Row For ir = 2 To lRow Select Case Cells(ir, 1) Case "1" Cells(ir, 2) = Replace(Cells(ir, 1), "1", "正解") Case "2" Cells(ir, 2) = Replace(Cells(ir, 1), "2", "不正解") End Select Next End Sub
今回はセルをずらして変化を見ていますが、Caseの左辺をCells(ir, 2)→Cells(ir, 1)とすることで、原文の位置を置換する事が可能となります。
Start,Countの使用
3種の引数で動いていたところに引数StartとCountを追加してみます。
Replace(置換対象文字列,検索文字列,置換文字列,開始位置,実行回数)
まずは開始位置を「1」これは特に指定しなければ1になりますので、これまでの置換と同様の動きです。次に実行回数を「2」にして実行していきます。
Sub 置換() Dim ir As Long Dim lRow As Long lRow = Cells(Rows.Count, 1).End(xlUp).Row For ir = 2 To lRow Cells(ir, 2) = Replace(Cells(ir, 1), "1", "正解", 1, 2) Next End Sub
「111」の様に並んでいるときの1を「正解」に置換してみましょう。1つ目の「1」から開始し2回実行しますので、111を置換すると「正解正解1」となります。
次は開始位置を「2」にして変化をみていきます。Range(”A2”)の「1」は開始位置が変わったことで、置換するものがなく空白になってしまいます。同じように「111」は最初の1が空白になり、2番目と3番目の1が正解に置換される様になります。
Sub 置換() Dim ir As Long Dim lRow As Long lRow = Cells(Rows.Count, 1).End(xlUp).Row For ir = 2 To lRow Cells(ir, 2) = Replace(Cells(ir, 1), "1", "正解", 2, 2) Next End Sub
まとめ
VBAでのReplaceはまとめて単語を置換するのにとても役立ちます。私自身はStartとCountを使う事はほとんどなく、単語を検索して置換する使い方がメインとなっていますので、まずはそちらから使ってみると良いと思います。
特にデータベースからデータを抜き出すと1が男、2が女の様に数値になっている物が多くありますので、それを「文字列」に置換したりするのに利用していますので、是非使ってみてください。
お読みいただきありがとうございました。