EXCEL VBA Replace関数　複数の文字列の置換（セルの値・指定文字列位置・置換リスト・数式の置換）

2021年11月13日

EXCEL VBA Replace関数　複数の文字列の置換（セルの値・指定文字列位置・一括変換・数式の置換）

●はじめに

今回説明するのは、文字列を置換する機能のReplace関数の利用方法を説明いたします。Replace関数の特徴として、指定された文字列の一部を別の文字列で置換したり、置換開始位置や置換や該当した置換の回数を指定する事も引数を設定する事で、利用する事ができます。実際に文字列等を変換する時は、その置換目的に合わせて使い分けて下さい。それでは、サンプルプログラムを交えて順番に説明いたします。

●【EXCEL VBA Replace メソッド　複数の文字列の置換（セル範囲・一括変換・置換リスト）については、下記の参照して下さい】

EXCEL VBA Replace メソッド　複数の文字列の置換（セル範囲・一括変換・置換リスト）

●【EXCEL VBA Substitute メソッド　複数の文字列の置換（大量・一括変換・置換リスト）については、下記の参照して下さい】

EXCEL VBA Substitute メソッド　複数の文字列の置換（大量・一括変換・置換リスト）

●【EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字については、下記の参照して下さい】

EXCEL VBA データの検索・置き換え方法（テクニック）名字の旧字・新字

●書式の説明

●　Replace関数を利用するには、下記の通りに設定を行います。

Replace(Expression,Find,Replace,Start,Count,Compare）
(置換元文字列式,検索文字列,置換文字列,文字列検索位置,置換文字列数,文字列比較モード）

引数	定数	説明（内容）
Expression	文字列（必須）	置換元の文字列
Find	文字列（必須）	検索対象の文字列
Replace	文字列（必須）	置換対象の文字列
Start	数値で指定（省略可能）	検索開始位置を指定（数値を指定）
Count	数値で指定（省略可能）	置換する文字列数を指定（数値で指定）省略の場合は、全て置換
Compare	vbBinaryCompare（省略可能）	バイナリモードで比較大文字と小文字を区別
Compare	vbTextCompare（省略可能）	テキストモードで比較大文字と小文字を区別

【使用例①】

Range(“A1”) = Replace(Range(“A1”), “EXCEL”, “エクセル”)

※セル「A1」の【EXCEL】の文字列を【エクセル】に置換します。

【使用例②】

Range(“A1”) = Replace(Range(“A1″), ” “, “”, compare:=vbTextCompare)

※セル「A1」の空白文字の大文字スペース・小文字スペース共に削除します。

【使用例③】

Range(“D3”) = Replace(Range(“D3”).Formula, “SUM(“, “SUBTOTAL(101,”)

※セル「D3」に登録されている関数を置換（SUM関数⇒SUBTOTAL関数）します。
※SUM関数では、合計値を計算・SUBTOTAL関数では、平均値を計算しています。

【注意点】
●Replace関数とReplaceメゾットの違いについて

・Replace関数：変数やセル値の指定文字列の一部を置換します。
　　（変換元の何文字目などの置換する場合には使いやすい）　
・Replaceメゾット：指定範囲のセルの文字列の一部を置換します。
（A列全部やA1：A1000などのセル範囲を指定する場合は使いやすい）

Replace関数を使って複数文字列を置換します。

●プログラム説明　(サンプル①)

下記のサンプルプログラムは、複数文字列を一括に置換します。A列に文字列としてExcel・Word・Outlookが複数登録されています。この文字列に対して、エクセル・ワード・アウトルックへ一括変換します。

【プログラムの流れ】
①A列の文字列に対して、置換対象の文字列へ置換します。
・EXCEL　⇒　エクセル
・Word　⇒　ワード
・OutLook　⇒　アウトルック

'
'******** AKIRA55.COM ******* https://akira55.com/replace_kan/ '
'
    Sub Replace_Kan01() 'Replace関数を使って複数文字列を置換します。
 
    Dim I, lRow As Long
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
    
    For I = 1 To lRow
        Cells(I, "A") = Replace(Cells(I, "A"), "Excel", "エクセル")
        Cells(I, "A") = Replace(Cells(I, "A"), "Word", "ワード")
        Cells(I, "A") = Replace(Cells(I, "A"), "OutLook", "アウトルック")
    
    Next I
    
    End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/replace_kan/ '

Sub Replace_Kan01() 'Replace関数を使って複数文字列を置換します。

Dim I, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

For I = 1 To lRow

Cells(I, "A") = Replace(Cells(I, "A"), "Excel", "エクセル")

Cells(I, "A") = Replace(Cells(I, "A"), "Word", "ワード")

Cells(I, "A") = Replace(Cells(I, "A"), "OutLook", "アウトルック")

Next I

End Sub

●実行前～実行後　※プログラム実行後、A列の文字列が指定した文字列へ置換されました。
（画面クリックして拡大）

Replace関数を利用して、指定した文字列を除いて削除（置換）します。（一括置換）

●プログラム説明　(サンプル②)

下記のサンプルプログラムは、A列に東京都の住所一覧が表示されています。この住所一覧に表示されている【東京都】部分のみ一括削除（置換)します。

【プログラムの流れ】
①A列の文字列として登録されている住所に対して、【東京都】のみ削除（置換）します。
・東京都　⇒　””　　（削除）

'
'******** AKIRA55.COM ******* https://akira55.com/replace_kan/ '
'
Sub Replace_Kan02() '指定した文字列を削除(置換)します。

    Dim I, lRow As Long
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。

    For I = 1 To lRow  'A列の最終行まで繰り返します。
        Range("A" & I) = Replace(Range("A" & I), "東京都", "") 'A列に登録されている東京都⇒削除します。
    Next I

End Sub
'

'******** AKIRA55.COM ******* https://akira55.com/replace_kan/ '

Sub Replace_Kan02() '指定した文字列を削除(置換)します。

Dim I, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

For I = 1 To lRow 'A列の最終行まで繰り返します。

Range("A" & I) = Replace(Range("A" & I), "東京都", "") 'A列に登録されている東京都⇒削除します。

Next I

End Sub

●実行前～実行後　※プログラム実行後、A列に登録されている住所内の【東京都】部分のみ削除されました。
（画面クリックして拡大）

Replace関数を利用して、文字列内の空白文字（全角・半角）を削除（置換）します。（一括置換）

●プログラム説明　（サンプル③）

下記のサンプルプログラムは、B列に登録されている【氏名】とC列に登録されている【住所】に空白文字がバラバラに挿入されています。この空白文字（空白全角・空白半角）を削除（取り除く）するサンプルプログラムです。

【プログラムの流れ】
①B列の文字列として登録されている【氏名】に対して、空白文字のみ削除（置換）します。
②C列の文字列として登録されている【住所】に対して、空白文字のみ削除（置換）します。
・空白文字　⇒　””　　（削除）

'
'''******** AKIRA55.COM ******* https://akira55.com/replace_kan/
'
Sub Replace_Kan03()  '空白文字（全角・半角）を削除します。

    Dim I, lRow As Long
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
    
    For I = 2 To lRow 'A列の最終行まで繰り返します。
        Range("B" & I) = Replace(Range("B" & I), " ", "", compare:=vbTextCompare) 'B列の空白を順番に削除します。
        Range("C" & I) = Replace(Range("C" & I), " ", "", compare:=vbTextCompare) 'C列の空白を順番に削除します。
    Next I
    

End Sub
'

'''******** AKIRA55.COM ******* https://akira55.com/replace_kan/

Sub Replace_Kan03() '空白文字（全角・半角）を削除します。

Dim I, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

For I = 2 To lRow 'A列の最終行まで繰り返します。

Range("B" & I) = Replace(Range("B" & I), " ", "", compare:=vbTextCompare) 'B列の空白を順番に削除します。

Range("C" & I) = Replace(Range("C" & I), " ", "", compare:=vbTextCompare) 'C列の空白を順番に削除します。

Next I

End Sub

●実行前～実行後　※プログラム実行後、B列の「氏名」とC列の「住所」部分に挿入されている空白文字を一括で削除されました。
（画面クリックして拡大）

Replace関数を利用して、文字列内の改行を一括削除します。

●プログラム説明　(サンプル④)

下記のサンプルプログラムは、指定した列の文字列内の改行を一括削除を行うサンプルプログラムです。C列に【メールアドレス】が登録されています。このC列に登録されているメールアドレスは、部分的に改行されています。このC列の改行を全て取り除くサンプルプログラムです。

【プログラムの流れ】
①C列（メールアドレス）の文字列内の改行を一括削除します。
※【　vbLf　】は、改行を表し　・　””　は、削除を意味します。

'
''******** AKIRA55.COM ******* https://akira55.com/replace_kan/
'
Sub Replace_Kan04() '文字列内の改行を一括削除します。

    Dim I, lRow As Long
    
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
 
    For I = 2 To lRow 'A列の最終行まで繰り返します。
    
          Range("C" & I) = Replace(Range("C" & I), vbLf, "")  'C列の改行を全て削除します。
    
    Next I
 
End Sub
'

''******** AKIRA55.COM ******* https://akira55.com/replace_kan/

Sub Replace_Kan04() '文字列内の改行を一括削除します。

Dim I, lRow As Long

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

For I = 2 To lRow 'A列の最終行まで繰り返します。

Range("C" & I) = Replace(Range("C" & I), vbLf, "") 'C列の改行を全て削除します。

Next I

End Sub

●実行前～実行後　※プログラム実行後、指定したC列「メールアドレス」内の改行が全て削除する事が出来ました。
（画面クリックして拡大）

Replace関数を利用して、置換リストを作成し置換リストに該当するコードを文字列データに置換処理を行います。

●プログラム説明　(サンプル⑤)

下記のサンプルプログラムは、同じシートに参照元データと置換リストを作成して、参照元データ（A列）のコードと置換リストのコード（G列）に該当するコードを文字列に変換を行います。今回のサンプルプログラムでは、参照元データ（A列）には、銀行コード・支店コードが数字で登録されています。この数値を元に、置換リスト（G列）と照合して数値が一致する銀行名・支店名を別セル（B列）に返します。

【プログラムの流れ】
①A列とG列の最終行を取得します。
②A列【銀行コード・支店コード・口座番号】の最終行まで繰り返します。
③A列のコード内容をB列に転記します。
④B列のコードとG列のコードが一致するコードと文字列をB列に置換します。
⑤コードと文字列を置換した場合は、銀行コードの置換ループから抜ける。
⑥A列の最終行まで繰り返します。（③へ戻る）

【プログラム実行条件】
①銀行コードと支店コードは、７桁の数字で登録・口座番号も７桁の数字で登録します。
A列のデータは、上記の条件で作成してください。
G列の銀行コード・支店コードも一致できるようにコードを作成して下さい。

'
''******** AKIRA55.COM ******* https://akira55.com/replace_kan/
'
Sub Peplace_kan05() '置換リストを作成して、置換リストに該当するコードを文字列データに置換処理を行います。

    Dim I, M, lRow, mRow As Long
    Dim Gin As String
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
    mRow = Cells(Rows.Count, "G").End(xlUp).Row  'G列の最終行を取得します。
    
    For I = 2 To lRow
        For M = 2 To mRow
            Range("B" & I) = Range("A" & I)
            Range("B" & I) = Replace(Range("A" & I), Range("G" & M), Range("H" & M), 1, 1)
            If Range("A" & I) <> Range("B" & I) Then Exit For
        Next M
    Next I
    
End Sub’

''******** AKIRA55.COM ******* https://akira55.com/replace_kan/

Sub Peplace_kan05() '置換リストを作成して、置換リストに該当するコードを文字列データに置換処理を行います。

Dim I, M, lRow, mRow As Long

Dim Gin As String

lRow = Cells(Rows.Count, "A").End(xlUp).Row 'A列の最終行を取得します。

mRow = Cells(Rows.Count, "G").End(xlUp).Row 'G列の最終行を取得します。

For I = 2 To lRow

For M = 2 To mRow

Range("B" & I) = Range("A" & I)

Range("B" & I) = Replace(Range("A" & I), Range("G" & M), Range("H" & M), 1, 1)

If Range("A" & I) <> Range("B" & I) Then Exit For

Next M

Next I

End Sub’

●実行前～実行後　※プログラム実行後、A列の銀行コード・支店コードを元に、コードを一致する銀行名・支店名をB列に置き換えました。
（画面クリックして拡大）

Replace関数を利用して、セルに設定している関数を別の関数へ置換処理を行います。

●プログラム説明　（サンプル⑥）

下記のサンプルプログラムは、EXCELで作成した表で、誤って違う関数を設定してしまった場合に、正しい関数へ置換する（修正）サンプルプログラムです。下記のEXCELの表では、【営業契約件数一覧】としてG列には、【平均値】・H列には「最大値」・I列には「最小値」として作成するはずでしたが、誤って全てSUM関数（４月～６月までの合計値）を登録されています。これをReplace関数を使い、平均値は、AVERAGE関数・最大値は、MAX関数・最小値は、MIN関数へ置換（関数を訂正）します。

【プログラムの流れ】
①A列の最終行を取得します。
②登録されている４行目のセル位置からA列で取得した最終行まで繰り返します。
③G列のSUM関数をAVERAGE関数へ置換・H列のSUM関数をMAX関数へ置換・I列のSUM関数をMIN関数へ置換
（誤っているSUM関数を正しい関数へ修正します。）
④A列の最終行まで繰り返します。（③へ戻る）

'
''******** AKIRA55.COM ******* https://akira55.com/replace_kan/
'
Sub Replace_kan06() '誤って設定したEXCEL関数を正しい関数へ置換します。

    Dim I, lRow As Long
 
    lRow = Cells(Rows.Count, "A").End(xlUp).Row  'A列の最終行を取得します。
    
    For I = 4 To lRow 'A列の最終行まで繰り返します。
    
        Range("G" & I) = Replace(Range("G" & I).Formula, "SUM", "AVERAGE") 'SUM関数⇒AVERAGE関数へ置換します（平均値）
        Range("H" & I) = Replace(Range("H" & I).Formula, "SUM", "MAX") 'SUM関数⇒MAX関数へ置換します（最大値）
        Range("I" & I) = Replace(Range("I" & I).Formula, "SUM", "MIN") 'SUM関数⇒MIN関数へ置換します（最小値）
    
    Next I
    
End Sub
’