ExcelVBAでスペースや改行を削除する方法をご説明します。
データを扱う際にスペースや改行が知らず知らずに含まれていて、文字列比較などが正常に出来ないなどの場合に前処理として、削除したい時がよくあります。
削除するにはVBAのReplace関数とReplaceメソッドを使う方法と、ワークシート関数のClean関数を使う方法の大きく3つあります。
※Clean関数は改行のみ削除となります。
また、前後のスペースを削除する関数として「Trim関数」、先頭のスペースのみ削除する「LTrim関数」と、末尾のスペースのみ削除する「RTrim関数」があります。
基本的な使い方と使い分けも併せてReplace関数、Replaceメソッド、Clean関数 それぞれ順にご説明します。
また、Replace関数とReplaceメソッドの詳細については下記記事をご覧ください。
逆にスペースや改行を追加したい場合は下記記事をご覧下さい。
1.Replace関数の使い方とスペース/改行の削除
セルを1つずつ処理するときや、変数を処理するときに利用します。
ただし、対象のセルが多い場合には処理時間が長くなります。
Replace関数の使い方
Replace関数は標準で用意されている関数です。
特定の文字列の中から指定の文字列に置換します。
Replace関数の構文は次のように記述します。
Replace(expression,find,replace[,start[,count[,compare]]])簡単に書くと次のようになります。
「Replace(文字列,検索文字列,置換文字列[,開始位置[,置換回数],比較方法]]])」
スペース/改行削除のサンプルコード
スペースと改行削除のサンプルコードです。
スペース削除
単一の文字列のスペースを削除します。
「Compare:=vbTextCompare」を指定する事で大文字、小文字のスペースを区別しないで削除します。
Sub Sample1()
Dim MyStr As String
MyStr = "あ い う え お"
MyStr = Replace(MyStr, " ", "", Compare:=vbTextCompare)
MsgBox MyStr
End Subもし、大文字小文字を個別に削除する場合は「Compare」を指定せず次のようにします。
Sub Sample2()
Dim MyStr As String
MyStr = "あ い う え お"
MyStr = Replace(Replace(MyStr, " ", ""), " ", "")
MsgBox MyStr
End Sub改行削除
文字列内の改行を削除する方法です。
「vbCrLf 」を「””」で削除する事で改行を削除できます。
Sub Sample3()
Dim MyStr As String
MyStr = "あ" & vbCrLf & "い" & vbCrLf & "う" & vbCrLf & "え" & vbCrLf & "お"
MyStr = Replace(MyStr, vbCrLf, "")
MsgBox MyStr
End Sub2.Replaceメソッドの使い方とスペース/改行の削除
指定したセル範囲で置換したい文字列に当てはまるセルの文字列を置換する場合に利用します。
Excelの「検索と置換(ショートカットCtrl+H)」の機能をVBAで使用する事と同じです。
引数や定数が多く、置換の文字列が見つからなかった場合にエラーになるため、エラー処理を組み込む等、気軽に使うには少し難しいかもしれません。
Replaceメソッドの使い方
Replaceメソッドは、指定したセル範囲で置換したい文字列に当てはまるセルの文字列を置換するメソッドです。
このReplaseメソッドは一度設定すると、条件が保存されExcelの「検索と置換」でも条件が変更されます。
条件が保存されるこの機能については注意しましょう。
Replaceメソッドの構文は次のように記述します。
Range.Replace(what、replacement、LookAt、searchorder、matchcase、matchByte、searchorder、replaceformat)スペース/改行削除のサンプルコード
スペース削除
「matchcase」をFalseに指定する事で大文字と小文字を区別しません。
Sub Sample4()
Range("A1:A10").Replace What:=" ", Replacement:="", LookAt:=xlPart, MatchCase:=False
End Sub改行削除
「vbCrLf 」を「””」で削除する事で改行を削除できます。
Sub Sample5()
Range("A1:A10").Replace What:=vbCrLf, Replacement:="", LookAt:=xlPart, MatchCase:=False
End Sub3.Clean関数の使い方と改行の削除
セルや変数を1つずつ処理するときに使用します。
もともとClean関数は印刷できない文字列を削除するための関数ですが、その機能を使用して改行を削除します。
また、改行は削除できますがスペースは削除できませんのでご注意ください。
Clean関数の使い方
印刷できない文字を文字列から削除します。
構文は次のように記述します。
Clean(文字列)
ワークシート関数のため、VBAで使用するには次のように記述します。
WorksheetFunction.Clean(文字列)改行削除のサンプルコード
Sub Sample5()
Dim MyStr As String
MyStr = "あ" & vbCrLf & "い" & vbCrLf & "う" & vbCrLf & "え" & vbCrLf & "お"
MyStr = WorksheetFunction.Clean(MyStr)
MsgBox MyStr
End Sub
