エクセルマクロ(EXCEL VBA) その18　文字列の置換(Replace)

森脇孝

2年前

文字列の置換

今回は文字列の置換についてご紹介します。
例えば、公演アンケートを取ったときにこんな結果だったとします。

この表の数値を☆に置き換えてみましょう。
マクロで文字列を置換したいときには、Replaceという関数を使います。

まずは、シートをコピーして名前を付けた上で、
B4の 1 を ☆ に置き換えてみましょう。
B4はCells(4, 2)なので、

ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "置き換え後"

Cells(4, 2) = Replace(Cells(4, 2), "1", "☆")

とします。

ここで、青線で書いた部分が「検索対象の文字列」・・・今回は 1
オレンジ線の部分が「置き換え対象の文字列」・・・今回は 1
そして緑線の部分が「置き換え後の文字列」・・・今回は ☆

右辺が置き換わったので、それを元のセルに入れ直すことで、『置換』できるというわけです。

これを実行すると

1を☆に置換できました！

　
ということで、おなじみのForNextを使って、2行目から最終行まで置き換えを繰り返せばいいですね。
最終行をlastRowとすると、

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
　Cells(i, 2) = Replace(Cells(i, 2), "1", "☆")
Next

これで、このアンケートの中に1があれば、☆に置き換えてくれるはずです。

　
ということで、2は☆☆、3は☆☆☆、4は☆☆☆☆、5は☆☆☆☆☆に置き換えましょう。

For i = 2 To lastRow
　Cells(i, 2) = Replace(Cells(i, 2), "1", "☆")
　Cells(i, 2) = Replace(Cells(i, 2), "2", "☆☆")
　Cells(i, 2) = Replace(Cells(i, 2), "3", "☆☆☆")
　Cells(i, 2) = Replace(Cells(i, 2), "4", "☆☆☆☆")
　Cells(i, 2) = Replace(Cells(i, 2), "5", "☆☆☆☆☆")
Next

なんかもうちょっとカッコよく書けるような気もしますが、わかりやすいの見逃してください。

これを実行すると、

これを使えば、☆を数字に置き換えることもできます。
　

今回の最終的なコード

Sub star_chikan()
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "置き換え後"

lastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastRow
  Cells(i, 2) = Replace(Cells(i, 2), "1", "☆")
  Cells(i, 2) = Replace(Cells(i, 2), "2", "☆☆")
  Cells(i, 2) = Replace(Cells(i, 2), "3", "☆☆☆")
  Cells(i, 2) = Replace(Cells(i, 2), "4", "☆☆☆☆")
  Cells(i, 2) = Replace(Cells(i, 2), "5", "☆☆☆☆☆")
Next

End Sub

宿題-18

氏名の列を参照して、「田」という感じを「★」に置換してください。
さらに、「野」と「松」を消してください。

▼ クリックで解説を表示

解説

例によってシートをコピーしておきます。

ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "田を★に"

本編でやったコードを少し書き換えるだけで良さそうですね。
宿題はA列なので、Cells(i, 2)だったところをCells(i, 1)に置き換えます。
そして、置き換える元を「田」、置き換え先を「★」と、設定すれば良いですね。

For i = 2 To lastRow
    Cells(i, 1) = Replace(Cells(i, 1), "田", "★")
Next

「野」と「松」を消すというのは、どうすれば実現できるでしょうか？

実は、置き換え先を “” とすることで、消すことができます。

For i = 2 To lastRow
    Cells(i, 1) = Replace(Cells(i, 1), "田", "★")
    Cells(i, 1) = Replace(Cells(i, 1), "野", "")
    Cells(i, 1) = Replace(Cells(i, 1), "松", "")
Next

他にも、「・」区切りになっているところを「、」区切りに直したり、全角スペース区切りを半角スペース区切りに直したりすることもできます。

最終的なコードはこちらです。

Sub ta_chikan()
'シートをコピーして名前を付ける
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = "田を★に"

'A列の最終行を取得
lastRow = Cells(Rows.Count, 1).End(xlUp).Row

'2行目から最終行まで繰り返す
For i = 2 To lastRow
    '文字列をそれぞれ置換
    Cells(i, 1) = Replace(Cells(i, 1), "田", "★")
    Cells(i, 1) = Replace(Cells(i, 1), "野", "")
    Cells(i, 1) = Replace(Cells(i, 1), "松", "")
Next

End Sub

答えファイルのダウンロード

エクセルマクロ(EXCEL VBA) その19　文字列の検索(Find)

（文：森脇孝／エントレ）