大体でIT
大体でIT
Excel VBAで文字列を置換するには、ReplaceやSubstitute関数、Mid、Replace関数があります。「指定した文字列」を置換する方法と、「文字列の位置を指定」して置換する方法があります。文字列の置換についてマスターしていきましょう。
Excel VBAで文字列を置換する方法は4つあります。
- VBA関数Replace
- ワークシート関数Substitute
- VBA関数Mid
- ワークシート関数Replace
置換する文字列を指定して、任意の文字列に置換したい場合は、VBA関数のReplaceでできます。
置換する文字列と番号を指定して、任意の文字列に置換したい場合は、ワークシート関数のSubstituteが便利です。
指定した範囲の文字列に、任意の文字列を挿入して置換したい場合は、VBA関数のMidが高速で使えます。
指定した範囲の文字列を、任意の文字列に置換したい場合は、ワークシート関数のReplaceがおすすめです。
それぞれの特徴を理解して文字列を置換したい場面に応じて使い分けるといいかと思います。
VBA関数のReplaceで文字列の置換ができます。
VBA関数のReplaceはある文字列を任意にの文字列へ置換することができます。
文字列『12345』の『345』を『678』に置換するイメージです。
VBA関数Replaceを使ったVBAコードを紹介していきます。
文字列『"12345"』の『"345"』を『"678"』へ置換してみます。
Sub TEST1()
MsgBox Replace("12345", "345", "678") '文字列を置換
End Sub
置換前と置換後
置換後の文字列『"12678"』へ置換できました。
文字列『"123"』を『"678"』へ置換してみます。
Sub TEST2()
'セルの文字列を置換
Cells(1, 1) = Replace(Cells(1, 1), "345", "678")
End Sub
セルの文字列を置換
置換後の文字列『"123678"』へ置換することができました。
Sub TEST3()
'数式の置換
Cells(1, 1).Formula = Replace(Cells(1, 1).Formula, "Sheet2", "Sheet3")
End Sub
セルの中の数式を置換
セルの中の数式『Sheet2』を『Sheet3』へ置換することができました。
セルのスペースをすべて削除したいときには、全角スペースと半角スペースをすべて空欄""に置換するとできます。
Sub TEST4()
'半角スペースを空欄に置換
Cells(1, 1) = Replace(Cells(1, 1), " ", "")
'全角スペースを空欄に置換
Cells(1, 1) = Replace(Cells(1, 1), " ", "")
End Sub
そのあとに全角スペースを空欄""に置換しています。
半角スペースと全角スペースを置換して削除
セルの改行を空欄""に置換して削除することもできます。
Sub TEST5()
'改行を空欄に置換
Cells(1, 1) = Replace(Cells(1, 1), vbLf, "")
End Sub
改行の削除は『vbLf』を空欄『""』に置換することでできます。
セルの改行を置換して削除
ワークシート関数Substituteで文字列の置換
ワークシート関数のSubstituteでも文字列を置換することができます。
ワークシート関数Substituteの置換イメージ
VBA関数のReplaceと違う点は、置換する番号を選べるということです。
文字列『123-123-123』の2番目の『123』だけを『456』に置換するイメージです。
VBA関数で置換すると『456-456-456』というように全部変わっちゃいますよね。
一部の文字列だけを置換したいといった場合にワークシート関数Substituteは使えます。
ではワークシート関数SubstituteのVBAコードをつくってみます。
文字列『123-123-123』の2番目の『123』だけを『456』に置換するVBAコードです。
Sub TEST6()
'2番目の"123"を"456"に置換
MsgBox WorksheetFunction.Substitute("123-123-123", "123", "456", 2)
End Sub
2番目の文字列だけを置換
置換する対象の文字列が複数あって一部だけを置換したい場合に使えます。
Midを使って文字列を置換する際は文字列を指定位置に挿入するイメージになります。
左から2番目以降の文字列に『きく』を挿入するイメージです。
4文字分に2文字を挿入したのでのこりの2文字『えお』はそのままとなります。
挿入する文字列が大きい場合は元の文字列を超えた分は無視されます。
『あいうえお』の左の2番目から3文字の範囲に『きく』を挿入する場合です。
『あいうえお』の左の2番目から3文字の範囲に『きくけこさ』を挿入する場合です。
Midは置換元に置換後の文字列を挿入するイメージです。
Midを使うメリットは、実行速度が速いということです。
位置のみ指定して置換（挿入文字列が小さい場合）
文字列『あいうえお』の左から2文字目以降の範囲に『きく』を挿入して置換するVBAコードです。
Sub TEST7()
Dim A
A = "あいうえお"
'2文字目以降に"きく"を挿入
Mid(A, 2) = "きく"
MsgBox A
End Sub
位置のみ指定して置換（挿入文字列が大きい場合）
文字列『あいうえお』の左から2文字目以降の範囲に『きくけこさ』を挿入して置換するVBAコードです。
Sub TEST8()
Dim A
A = "あいうえお"
'2文字目以降に"きくけこさ"を挿入
Mid(A, 2) = "きくけこさ"
MsgBox A
End Sub
位置と文字数を指定して置換（挿入文字列が小さい場合）
文字列『あいうえお』の左の2番目から3文字分の範囲に『きく』を挿入して置換するVBAコードです。
Sub TEST9()
Dim A
A = "あいうえお"
'2文字目から3文字に"きく"を挿入
Mid(A, 2, 3) = "きく"
MsgBox A
End Sub
位置と文字数を指定して置換（挿入文字列が大きい場合）
文字列『あいうえお』の左の2番目から3文字分の範囲に『きくけこさ』を挿入して置換するVBAコードです。
Sub TEST10()
A = "あいうえお"
'2文字目から3文字に"きくけこさ"を挿入
Mid(A, 2, 3) = "きくけこさ"
MsgBox A
End Sub
空欄""への置換はできない
『あいうえお』の2文字目以降にを空欄""を挿入して置換してみようとしたVBAコードです。
Sub TEST11()
Dim A
A = "あいうえお"
'2文字目以降に空欄を挿入したい⇒できません
Mid(A, 2) = ""
MsgBox A
End Sub
Midで空欄""を挿入するとなにも挿入しないということになるので、残念ながら変化なしです。
文字列の範囲を指定して削除するしたい場合は、次に説明するワークシート関数Replaceを使えばできます。
ワークシート関数Replaceは、文字列の位置と文字数を指定して値を置換することができます。
ワークシート関数Replaceの置換のイメージです。
文字列の置換のイメージ
『あいうえお』の左の2番目から2文字分を『きくけ』に置換のイメージです。
Midは挿入のイメージでしたがワークシート関数のReplaceは置換になります。
文字列を削除するイメージ
ワークシート関数Replaceを使って、文字列を削除することもできます。
文字列『あいうえお』の左の2番目から2文字分を空欄""に置換するイメージです。
VBA関数のReplaceとは違って、ワークシート関数のReplaceは、文字列の範囲を指定してそれを任意の文字列へ置換です。
VBA関数のReplacehaはある文字列を任意の文字列に置換ですよね。
ではVBAコードでワークシート関数Replaceを使ってみます。
文字列の置換のVBAコード
『あいうえお』の2番目から3文字分を『きくけこ』に置換するVBAコードです。
Sub TEST12()
'2文字目から3文字を"きくけこ"に置換
MsgBox WorksheetFunction.Replace("あいうえお", 2, 3, "きくけこ")
End Sub
空欄""へ置換のVBAコード
『あいうえお』の2文字目から3文字分を空欄""へ置換するVBAコードです。
Sub TEST13()
'2文字目から3文字を空欄に置換
MsgBox WorksheetFunction.Replace("あいうえお", 2, 3, "")
End Sub
つまり2文字目から3文字分を削除することができます。
Excel VBAで文字列を置換する方法は4つありました。
- VBA関数Replace
- ワークシート関数Substitute
- VBA関数Mid
- ワークシート関数Replace
置換する文字列を指定して、任意の文字列に置換したい場合は、VBA関数のReplaceでできます。
置換する文字列と番号を指定して、任意の文字列に置換したい場合は、ワークシート関数のSubstituteが便利です。
指定した範囲の文字列に、任意の文字列を挿入して置換したい場合は、VBA関数のMidが高速で使えます。
指定した範囲の文字列を、任意の文字列に置換したい場合は、ワークシート関数のReplaceがおすすめです。
参考になればと思います。最後までご覧くださいましてありがとうございました。
