大体で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で文字列の置換ができます。

VBA関数Replaceの置換イメージ

VBA関数のReplaceはある文字列を任意にの文字列へ置換することができます。

文字列『12345』の『345』を『678』に置換するイメージです。

Replaceで文字列の置換)replace1

結果は、『12678』になります。

置換のイメージそのままなのでわかりやすいです。

文字列を置換してみる

VBA関数Replaceを使ったVBAコードを紹介していきます。

文字列『"12345"』の『"345"』を『"678"』へ置換してみます。

Sub TEST1()
    
  MsgBox Replace("12345", "345", "678") '文字列を置換
  
End Sub

実行結果をみてみます。

置換前と置換後

Replaceで置換する前と置換後の値)replace2

置換前の文字列『"12345"』から

置換後の文字列『"12678"』へ置換できました。

セルの値を置換

もちろんセルの値も置換することができます。

セルに『"12345"』を入力しました。

文字列『"123"』を『"678"』へ置換してみます。

Sub TEST2()
  
  'セルの文字列を置換
  Cells(1, 1) = Replace(Cells(1, 1), "345", "678")
  
End Sub

実行してみます。

セルの文字列を置換

Replaceでセルの値を置換した結果)replace3

置換前の文字列『"12345"』から

置換後の文字列『"123678"』へ置換することができました。

セルの数式を置換

セルに入力された数式も置換することができます。

Sub TEST3()
  
  '数式の置換
  Cells(1, 1).Formula = Replace(Cells(1, 1).Formula, "Sheet2", "Sheet3")
  
End Sub

セルの中の数式を置換してみます。

セルの中の数式を置換

セルの中の数式を置換した結果)replace4

セルの中の数式『Sheet2』を『Sheet3』へ置換することができました。

セルの全角空白と半角空白をすべて置換

セルのスペースをすべて削除することもできます。

セルのスペースをすべて削除したいときには、全角スペースと半角スペースをすべて空欄""に置換するとできます。

Sub TEST4()
  
  '半角スペースを空欄に置換
  Cells(1, 1) = Replace(Cells(1, 1), " ", "")
  '全角スペースを空欄に置換
  Cells(1, 1) = Replace(Cells(1, 1), " ", "")
  
End Sub

半角スペースを空欄""に置換します。

そのあとに全角スペースを空欄""に置換しています。

これでスペースをすべて削除することができます。

VBAコードを実行してみます。

半角スペースと全角スペースを置換して削除

半角スペースと全角スペースが削除される)replace5

半角スペースと全角スペースが削除されました。

セルの改行を置換

セルの改行を空欄""に置換して削除することもできます。

Sub TEST5()
  
  '改行を空欄に置換
  Cells(1, 1) = Replace(Cells(1, 1), vbLf, "")
  
End Sub

改行の削除は『vbLf』を空欄『""』に置換することでできます。

VBAコードを実行します。

セルの改行を置換して削除

改行が削除される)replace6

改行が削除されました。

ワークシート関数Substituteで文字列の置換

ワークシート関数のSubstituteでも文字列を置換することができます。

ワークシート関数Substituteの置換イメージ

VBA関数のReplaceと違う点は、置換する番号を選べるということです。

イメージは次のようになります。

文字列『123-123-123』の2番目の『123』だけを『456』に置換するイメージです。

ワークシート関数のSubstituteの置換イメージ)substitute1

置換すると『123-456-123』となります。

ここがVBA関数Replaceとの違いです。

置換する番号を選べます。

VBA関数で置換すると『456-456-456』というように全部変わっちゃいますよね。

一部の文字列だけを置換したいといった場合にワークシート関数Substituteは使えます。

VBAコード

ではワークシート関数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番目の文字列だけを置換

substituteで置換する番号を指定して置換)substitute2

『123-456-123』に置換できました。

1番目と3番目の『123』はそのままです。

置換する対象の文字列が複数あって一部だけを置換したい場合に使えます。

VBA関数Midで文字列の置換

Midを使って文字列を置換することもできます。

VBA関数Midで文字列を置換するイメージ

Midを使って文字列を置換する際は文字列を指定位置に挿入するイメージになります。

左から2番目以降の文字列に『きく』を挿入するイメージです。

こんな感じですね。

Midで置換後の文字列)mid1

結果は『あきくえお』になりました。

4文字分に2文字を挿入したのでのこりの2文字『えお』はそのままとなります。

挿入する文字列が大きい場合は元の文字列を超えた分は無視されます。

『あいうえお』の2番目以降に『きくけこさ』を挿入

挿入文字数が大きい場合で位置のみ指定してMidで置換)mid2

『あきくけこ』となります。

位置と文字数を指定して置換もできます。

『あいうえお』の左の2番目から3文字の範囲に『きく』を挿入する場合です。

挿入文字数が小さい場合で位置と文字数を指定してMidで置換)mid3

『あきくえお』となります。

挿入する文字列が大きい場合のイメージです。

『あいうえお』の左の2番目から3文字の範囲に『きくけこさ』を挿入する場合です。

挿入文字数が大きい場合で位置と文字数を指定してMidで置換)mid4

『あきくけお』となります。

Midは置換元に置換後の文字列を挿入するイメージです。

元々の文字列の大きさは変わりません。

Midを使うメリットは、実行速度が速いということです。

大量の置換をする必要がある場合におすすめです。

VBAコード

ではVBAコードです。

位置のみ指定して置換(挿入文字列が小さい場合)

文字列『あいうえお』の左から2文字目以降の範囲に『きく』を挿入して置換するVBAコードです。

Sub TEST7()
  
  Dim A
  A = "あいうえお"
  
  '2文字目以降に"きく"を挿入
  Mid(A, 2) = "きく"
  
  MsgBox A

End Sub

で実行してみます。

Midを使って文字列を置換)mid5

『あきくえお』となりました。

位置のみ指定して置換(挿入文字列が大きい場合)

文字列『あいうえお』の左から2文字目以降の範囲に『きくけこさ』を挿入して置換するVBAコードです。

Sub TEST8()
  
  Dim A
  A = "あいうえお"
  
  '2文字目以降に"きくけこさ"を挿入
  Mid(A, 2) = "きくけこさ"
  
  MsgBox A

End Sub

では実行してみます。

Midを使って文字列を置換)mid6

『あきくけこ』となりました。

位置と文字数を指定して置換(挿入文字列が小さい場合)

文字列『あいうえお』の左の2番目から3文字分の範囲に『きく』を挿入して置換するVBAコードです。

Sub TEST9()
  
  Dim A
  A = "あいうえお"
  
  '2文字目から3文字に"きく"を挿入
  Mid(A, 2, 3) = "きく"
  
  MsgBox A

End Sub

では実行してみます。

Midを使って文字列を置換)mid7

『あきくえお』となりました。

位置と文字数を指定して置換(挿入文字列が大きい場合)

文字列『あいうえお』の左の2番目から3文字分の範囲に『きくけこさ』を挿入して置換するVBAコードです。

Sub TEST10()
  
  A = "あいうえお"
  
  '2文字目から3文字に"きくけこさ"を挿入
  Mid(A, 2, 3) = "きくけこさ"
  
  MsgBox A
  
End Sub

では実行してみます。

Midを使って文字列を置換)mid8

『あきくけお』となりました。

空欄""への置換はできない

ちなみにMidでは空欄""への置換はできません。

『あいうえお』の2文字目以降にを空欄""を挿入して置換してみようとしたVBAコードです。

これはできません。

Sub TEST11()
  
  Dim A
  A = "あいうえお"
  
  '2文字目以降に空欄を挿入したい⇒できません
  Mid(A, 2) = ""
  
  MsgBox A
  
End Sub

やってみます。

Midに空欄を挿入した結果)mid9

結果は変化なしで『あいうえお』のままです。

Midで空欄""を挿入するとなにも挿入しないということになるので、残念ながら変化なしです。

文字列の範囲を指定して削除するしたい場合は、次に説明するワークシート関数Replaceを使えばできます。

ワークシート関数Replaceで文字列の置換

ワークシート関数Replaceは、文字列の位置と文字数を指定して値を置換することができます。

ワークシート関数Replaceの置換イメージ

ワークシート関数Replaceの置換のイメージです。

Midと少し似ていますが、少し違います。

文字列の置換のイメージ

『あいうえお』の左の2番目から2文字分を『きくけ』に置換のイメージです。

ワークシート関数Replaceで文字列を置換した後)w-replace1

『あきくけえお』となりました。

Midとはちょっと違いますよね。

Midは挿入のイメージでしたがワークシート関数のReplaceは置換になります。

元の文字列の大きさも変わります。

文字列を削除するイメージ

ワークシート関数Replaceを使って、文字列を削除することもできます。

文字列『あいうえお』の左の2番目から2文字分を空欄""に置換するイメージです。

ワークシート関数Replaceで文字列を削除のイメージ)w-replace2

ワークシート関数Replaceは結構使えます。

VBA関数のReplaceとは違って、ワークシート関数のReplaceは、文字列の範囲を指定してそれを任意の文字列へ置換です。

VBA関数のReplacehaはある文字列を任意の文字列に置換ですよね。

VBAコード

ではVBAコードでワークシート関数Replaceを使ってみます。

文字列の置換のVBAコード

『あいうえお』の2番目から3文字分を『きくけこ』に置換するVBAコードです。

Sub TEST12()
  
  '2文字目から3文字を"きくけこ"に置換
  MsgBox WorksheetFunction.Replace("あいうえお", 2, 3, "きくけこ")
  
End Sub

では実行してみます。

ワークシート関数Replaceで文字列を置換)w-replace3

『あきくけこお』に置換されました。

空欄""へ置換のVBAコード

Midと違って空欄""へ置換することもできます。

『あいうえお』の2文字目から3文字分を空欄""へ置換するVBAコードです。

Sub TEST13()
  
  '2文字目から3文字を空欄に置換
  MsgBox WorksheetFunction.Replace("あいうえお", 2, 3, "")
    
End Sub

2文字目から3文字分を空欄""へ置換してみます。

ワークシート関数Replaceで文字列を空欄へ置換)w-replace4

『あお』となりました。

つまり2文字目から3文字分を削除することができます。

まとめ

Excel VBAで文字列を置換する方法は4つありました。

  • VBA関数Replace
  • ワークシート関数Substitute
  • VBA関数Mid
  • ワークシート関数Replace

置換する文字列を指定して、任意の文字列に置換したい場合は、VBA関数のReplaceでできます。

置換する文字列と番号を指定して、任意の文字列に置換したい場合は、ワークシート関数のSubstituteが便利です。

指定した範囲の文字列に、任意の文字列を挿入して置換したい場合は、VBA関数のMidが高速で使えます。

指定した範囲の文字列を、任意の文字列に置換したい場合は、ワークシート関数のReplaceがおすすめです。

参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す