2023/02/26

【ExcelVBA】文字列の中の指定した文字を置き換えるReplace関数【置換】

ExcelVBA

こんな疑問に答えます。

こんにちは、Fujiya（@fujiya_xyz）です。
今回はReplace関数について解説していきます。

ExcelVBAのReplace関数は、文字列中の指定した文字列を置換するための関数です。これを利用することで、指定した文字列を検索し、他の文字列に置き換えることができます。

ExcelVBA（エクセル）のReplace関数を解説

例えば、Excelシート内のデータを整理する際に、不要な文字列やスペースなどを除去するために使用できます。また、複数のシートからデータを取り込んだ場合、異なる形式のデータを統一したり、データを加工して分析するためのより良い形式に変換することができます。

ExcelVBAのReplace関数を使って、不要な文字列やスペースなどを除去してExcelシート内のデータを整理する

Replace関数を使えば、Excelシート内のデータを整理も簡単です。まず”ワークブック内のシート全て”について、不要な文字列やスペースなどを除去するマクロのコードを紹介します、手順は以下です。

Sub 不要な文字の削除()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        Dim rng As Range
        For Each rng In ws.UsedRange
            rng.Value = Replace(rng.Value, "不要な文字列", "")
            rng.Value = Replace(rng.Value, " ", "")
        Next rng
    Next ws
End Sub

このマクロは、ワークシート内の全てのセルに対してReplace関数を使用し、「不要な文字列」と「スペース」を除去します。「不要な文字列」の部分は、除去したい文字列に置き換えてください。

もう少し詳しくコードの解説とすると以下となります。

1. Dim ws As Worksheet は、ワークシートを表す変数 ws を宣言します。
2. For Each ws In ThisWorkbook.Sheetsというループは、このブック内のすべてのワークシートを処理するための繰り返し処理です。
3. Dim rng As Rangeは、Excelシート内のUsed（UsedRange）な範囲を表しており、これを変数rngと宣言しています。
4. For Each rng In ws.UsedRangeという2回目のループは、ワークシート内に使用されているすべての範囲を処理するためのものです。
5. rng.Value = Replace(rng.Value, "不要な文字列", "") は、範囲内の値から「不要な文字列」を除去しています。Replace関数は、最初の引数 rng.Value の文字列から、2番目の引数「不要な文字列」を検索し、3番目の引数「」に置き換えています。
6. rng.Value = Replace(rng.Value, " ", "")は、範囲内の値から「スペース」を除去を除去しています。こちらは、最初の引数 rng.Value の文字列から、2番目の引数「スペース」を検索し、3番目の引数「」に置き換えています。

このマクロを実行することで、Excelブック内のシート全て「不要な文字列」や「スペース」を除去できます。例えば、文字数が多く、特定の必要の無い文字列やスペースが謎に多くなってしまった文献などを整理するのにはいいのかなと思います。

上記の例では、変数の宣言も使って上級者っぽくしてみましたが、次にご紹介する例は実践で使えるものになります。

Replace関数とsplit関数と組み合わせて2つの文字列で区切る

これは実際に僕もどうしたらいいんだろう？と思って行き着いたやり方になります。Split関数は、文字列を指定された区切り文字で分割し、配列として返す関数なのですが複数の条件指定ができないです。

ちなみにSplit関数については「【文字列を指定した文字で区切って分割するSplit関数」で解説をしてますので、わからない方は先にお読みください。

例えば、下の画像のようにCells(1, 1) に「A会社/B部署/山田　たろう」という文字列があったとします（「」は無し）。この文字列をそれぞれ「A会社」「B部署」「山田」「太郎」と同じ列の下に順番に分けたいとします。

しかし、Split関数で”/”またはスペースのどちらかを区切ることは出来ても、両方の文字を指定して区切ることができないです。この場合には、まずReplace関数を使って”/”を全てスペースに置換します（”/”でもOK）。その後、Split関数を使ってスペースごとに区切る処理をしていくという流れです。

Sub 複数の条件で区切る()
    henkan = Cells(1, 1)
    henkan2 = Replace(henkan, "/", "　")
    Cells(2, 1) = Split(henkan2, "　")(0)
    Cells(3, 1) = Split(henkan2, "　")(1)
    Cells(4, 1) = Split(henkan2, "　")(2)
    Cells(5, 1) = Split(henkan2, "　")(3)
End Sub

実行すると、セルA1にある文字列をスラッシュで区切り、それぞれの部分を別々のセルB1からE1に順番に入力されます。以下で、デモコードについてもう少し詳しく解説します。

1. henkan = Cells(1, 1)は、変数henkanにシートのセルA1の値を代入。
2. henkan2 = Replace(henkan, "/", "　")は、変数henkanのスラッシュをスペースに置き換えた結果を変数henkan2に代入。
3. Cells(2, 1) = Split(henkan2, "　")(0)は、変数henkan2を全角スペースで分割した結果の1目の要素をシートのセルB1に代入。
4. Cells(3, 1) = Split(henkan2, "　")(1)は、変数henkan2を全角スペースで分割した結果の2つ目の要素をシートのセルC1に代入。
5. Cells(4, 1) = Split(henkan2, "　")(2)は、変数henkan2を全角スペースで分割した結果の3つ目の要素をシートのセルD1に代入。
6. Cells(5, 1) = Split(henkan2, "　")(3)は、変数henkan2を全角スペースで分割した結果の4つ目の要素をシートのセルE1に代入。

整理していくとすごく簡単な理屈かなと思います。単体の関数やコードでも複数組み合わせることで様々なことに対応できるのがプログラミングの面白いところですね。

今回の記事は以上になります
最後までお読みいただきありがとうございました。

プログラミング 知識

• facebook
• Tweet
• hatebu
• Share
• Read Later