Excel VBAでReplace関数の使い方をお探しですね。

広告

Excel VBAのReplace関数で文字列を置換・削除する方法を分かりやすく解説

Excelでデータ整形をしていると、「株式会社の表記がバラバラ」「謎のスペースが入ってる」「セル内改行がジャマ」みたいなこと、よくありますよね。

手作業で置換してもいいんですが、毎月同じ作業をするなら、VBAのReplace関数を使うと一瞬で終わります。

この記事では、Replace関数の基本から、スペース・改行・見えない空白の削除まで、実務でよく使うパターンを初心者向けに解説します。

Replace関数の基本構文と使い方

Excel VBAのReplace関数は、文字列の中から指定した文字を探して、別の文字に置き換える関数です。

たとえば「(株)」を「株式会社」に統一したり、「旧商品名」を「新商品名」に変えたりできます。

セルの値を直接書き換えることもできるし、いったん変数に入れて加工してから戻すこともできるので、文字列整形の基本ツールといえます。

基本の書き方はこんな感じです。

最初に対象の文字列、次に探したい文字、最後に置き換え後の文字を指定します。

4番目以降の引数もありますが、最初は必須の3つだけ覚えればOKです。

“`vb
Replace(対象文字列, 検索文字列, 置換後文字列)
“`

たとえば、A1セルの「東京営業所」を「東京支店」に変えたいときは、こう書きます。

Replace関数は元の文字列を勝手に書き換えるわけじゃなくて、置換後の文字列を「返す」関数なので、結果をセルか変数に入れる必要があります。

“`vb
Sub 文字列を置換する()
Range(“A1”).Value = Replace(Range(“A1”).Value, “営業所”, “支店”)
End Sub
“`

この考え方は、文字の削除にもそのまま使えます。

削除っていうのは、置換後の文字に空っぽの文字「””」を指定するだけです。

たとえば「株式会社」を消したいなら、検索文字列に「株式会社」、置換後に「””」を指定すればOK。

つまり、Replace関数では「置き換える」も「取り除く」も同じ仕組みで処理できるので、データのお掃除にめちゃくちゃ便利なんです。

半角スペース・全角スペース・見えない空白を削除する

Excelのデータでよく困るのが、文字列の中に混じった謎のスペースです。

「氏名の間のスペースを消したい」「商品コードの途中に入った空白を取りたい」「CSVから取り込んだデータの余計な空白をなくしたい」みたいなこと、実務だとしょっちゅうあります。

Replace関数を使えば、半角スペースや全角スペースを空文字に置き換えて、サクッと削除できます。

半角スペースだけ削除するなら、検索文字列に半角の「” “」を指定します。

全角スペースは見た目が似てますが別の文字なので、半角とは別に指定する必要があります。

両方消したいときは、Replace関数を入れ子にして、半角→全角の順に取り除くのが分かりやすいです。

“`vb
Sub 半角全角スペースを削除する()
Dim s As String

s = Range(“A1″).Value
s = Replace(s, ” “, “”) ‘半角スペースを削除
s = Replace(s, “　”, “”) ‘全角スペースを削除

Range(“A1″).Value = s
End Sub
“`

「先頭と末尾のスペースだけ消したい」ときは、Trim関数も候補になります。

ただし、Trim関数は文字列の前後の空白を整える用途がメインで、途中にあるスペースは削除できません。

「山田　太郎」を「山田太郎」にしたいなら、Replace関数を使う方が確実です。

前後だけ整えるのか、途中の空白も全部消すのかで使い分けましょう。

厄介なのが、見た目は空白なのに普通のスペース削除では消えない文字です。

代表例が、WebページやHTML由来のデータに含まれる「ノーブレークスペース」。

これは普通の半角スペースとは文字コードが違うので、Replace(s, ” “, “”)では削除できないことがあります。

Windows環境のVBAでは、ChrW(160)を指定すると削除できる場合があります。

“`vb
Sub 見えない空白を削除する()
Dim s As String

s = Range(“A1″).Value
s = Replace(s, ” “, “”)
s = Replace(s, “　”, “”)
s = Replace(s, ChrW(160), “”)

Range(“A1”).Value = s
End Sub
“`

「スペース削除したのに比較結果が一致しない」「空白に見える文字が残ってる」ってときは、半角・全角以外の文字が混じってる可能性大です。

原因を調べたいときは、AscW関数で文字コードを確認する方法もあります。

データの出どころがWeb、PDF、基幹システム、CSVなどの場合は、普通の空白以外が含まれることを前提に処理を考えると、トラブルを減らせます。

セル内改行を削除・整理する方法

Excelのセル内改行は、住所や備考、問い合わせ内容などでよく使われます。

でも、集計やCSV出力、システム連携するときには、セル内改行が原因でデータが崩れることがあります。

そんなときも、Replace関数で改行コードを空文字に置き換えれば、改行を削除できます。

ExcelでAlt+Enterで入力される改行は、多くの場合vbLfまたはChr(10)として扱えます。

次のコードは、A1セル内の改行を削除する例です。

検索文字列にvbLf、置換後に空文字「””」を指定しています。

これで、複数行に分かれてた文字列が1行につながります。

“`vb
Sub セル内改行を削除する()
Range(“A1”).Value = Replace(Range(“A1”).Value, vbLf, “”)
End Sub
“`

ただし、改行コードにはいくつか種類があります。

WindowsのテキストファイルだとvbCrLf、Unix系だとvbLf、古いMac形式だとvbCrが使われることがあります。

Excelのセル内改行だけ相手にするならvbLfで十分なことが多いですが、外部ファイルから取り込んだ文字列を処理する場合は、複数の改行コードを削除する書き方にしておくと安全です。

“`vb
Sub 複数種類の改行コードを削除する()
Dim s As String

s = Range(“A1”).Value
s = Replace(s, vbCrLf, “”)
s = Replace(s, vbLf, “”)
s = Replace(s, vbCr, “”)

Range(“A1”).Value = s
End Sub
“`

全部の改行を消すんじゃなくて、「連続した空行だけ減らしたい」「最後に残った余計な改行だけ削除したい」って場面もあります。

たとえば備考欄で、文章中の改行は残したいけど、2行以上の空行や末尾の改行は不要ってケースです。

この場合は、Replace関数をループで使って、連続した改行を1つにまとめる処理が有効です。

“`vb
Sub 無駄な改行を整理する()
Dim s As String

s = Range(“A1”).Value

‘連続する改行を1つにまとめる
Do While InStr(s, vbLf & vbLf) > 0
s = Replace(s, vbLf & vbLf, vbLf)
Loop

‘末尾の改行を削除
Do While Right(s, 1) = vbLf
s = Left(s, Len(s) – 1)
Loop

Range(“A1”).Value = s
End Sub
“`

このコードでは、まず改行が2つ続いてる箇所を1つの改行に置換して、連続する空行をなくしています。

次に、文字列の末尾が改行で終わってる間だけ、最後の1文字を削除しています。

単純に全部の改行を削除するより、文章の読みやすさを保ちながら不要な改行だけ整えられるので、備考欄やメモ欄の整形に向いてます。

Replace関数・Replaceメソッド・Clean関数の使い分け

VBAで置換する方法には、Replace関数のほかにRangeオブジェクトのReplaceメソッドや、WorksheetFunction.Cleanを使う方法があります。

名前が似てるので混同しやすいですが、得意な場面が違います。

Replace関数は文字列そのものを加工する関数で、変数や1セルの値を細かく処理したいときに向いてます。

一方、ReplaceメソッドはExcelの「検索と置換」に近い機能で、指定したセル範囲に対して一括置換したいときに便利です。

たとえば、A列全体の「旧部署」を「新部署」に置き換えるだけなら、Replaceメソッドを使うと短いコードで処理できます。

大量のセルを対象にする場合、1セルずつFor文で回してReplace関数を実行するより、範囲に対してReplaceメソッドを使う方がスッキリすることがあります。

“`vb
Sub 範囲を一括置換する()
Range(“A:A”).Replace What:=”旧部署”, Replacement:=”新部署”, _
LookAt:=xlPart, MatchCase:=False
End Sub
“`

Clean関数は、印刷できない制御文字を削除するためのワークシート関数です。

VBAではWorksheetFunction.Cleanの形で使えます。

改行やタブなどをまとめて取り除きたい場合に便利ですが、普通の半角スペースや全角スペース、ノーブレークスペースまで万能に削除できるわけじゃありません。

何を消したいのかハッキリしてる場合はReplace関数で指定して、制御文字をまとめて削除したい場合はClean関数を補助的に使う、って考えると実務で扱いやすくなります。

“`vb
Sub Clean関数で制御文字を削除する()
Range(“A1”).Value = WorksheetFunction.Clean(Range(“A1”).Value)
End Sub
“`

実務で安全に置換処理するには、いきなり元データを上書きしないことも大事です。

特に空白や改行の削除は、見た目では変化が分かりにくくて、必要なスペースまで消しちゃう可能性があります。

最初は別列に結果を出力する、対象範囲を限定する、バックアップシートを作ってから実行するなど、戻せる状態で試すと安心です。

最後に、目的別の使い分けをまとめます。

文字列やセル1つを細かく加工するならReplace関数、広いセル範囲をExcelの置換機能みたいに処理するならReplaceメソッド、改行などの制御文字をまとめて取り除くならClean関数が候補です。

空白削除では半角スペース、全角スペース、ノーブレークスペースを区別して、改行削除ではvbLf、vbCrLf、vbCrの違いを意識すると、原因不明の不一致やデータ崩れを防ぎやすくなります。

Replace関数をちゃんと使えるようになると、Excel VBAでのデータ整形の幅がグッと広がって、手作業の修正時間を大きく削減できますよ。

広告