【VBA】Excelマクロで文字列を置換(統一)する方法|スペース削除, 改行削除

VBA
スポンサーリンク

VBAを使えば文字データを自動で置換できます。

たった1行なので、誰でも実装可能です。

・Excelに入力された文字を置換(統一)したい

・スペースの大きさを統一したい
・改行を削除したい

こんな悩みを解決する内容です。

アルファベットの大文字小文字を統一することもできるので便利ですよ!

文字の置換:Replace

Replaceの使い方

以下のように、”株式会社”の表示が統一されていないとします。

”株式会社”の表記が統一されていないデータ

B列に”株式会社”として統一した社名を出力しましょう。

Replaceを使います。

Sub test()

For i = 1 To 3
    Target = Cells(i, 1)
    Target = Replace(Target, "㈱", "株式会社")
    Target = Replace(Target, "(株)", "株式会社")
    Cells(i, 2) = Target
Next i

End Sub
”株式会社”の表記を統一したデータ

Replaceの()には、対象の文字列、置換したい文字、置換後の文字を入れます。

Replace(“文字列”, “置換したい文字”, “置換後の文字”)
Replaceのコード解説

さっきのコードでは、変換前の文字をTargetとして、”㈱”と”(株)”の両方のReplaceに通しました。

こうすると、どちらの表記であっても最終的には”株式会社”に統一されます。

データの最終行を自動取得する方法

処理する行数が毎回3行目までとは限らないんじゃない??

ファイルによってデータが入っている行数が異なる場合は、以下のように書くといいです。

Sub test()

endRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To endRow
    Target = Cells(i, 1)
    Target = Replace(Target, "㈱", "株式会社")
    Target = Replace(Target, "(株)", "株式会社")
    Cells(i, 2) = Target
Next i

End Sub
最終行 = Cells(Rows.Count, 1).End(xlUp).Row

こうすると、データ数が100でも1万でも、何行入っていても大丈夫です。

詳しいことは、Excelマクロで最終行(列)を取得する方法をご覧ください。

ファイルを開くときに自動で置換する方法

いちいち起動ボタンを押さなくとも、勝手に統一してほしい。

イベントプロシージャを使えば、ファイルを開くときに自動で文字を置換してくれます。

プログラムを書く画面の左端に、”ThisWorkbook”があります。

これをダブルクリックしましょう。

ファイルを開いたときに実行するプログラム

ウィンドウが現れるので、左上を”Workbook”右上を”Open”にしてください。

これでファイルを開いたら自動実行するプログラムが作れます。

Private Sub Workbook_Open()

endRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To endRow
    Target = Cells(i, 1)
    Target = Replace(Target, "㈱", "株式会社")
    Target = Replace(Target, "(株)", "株式会社")
    Cells(i, 2) = Target
Next i

End Sub

処理内容はさっきと同じです。

ファイルを開くときに実行されるようになっています。

スペースを削除する方法

下図のように、半角スペースと全角スペースが混在しているとします。

半角スペースと全角スペースが混在しているデータ

半角と全角の両方をReplaceで変換し、削除してみましょう。

Sub test()

endRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To endRow
    Target = Cells(i, 1)
    Target = Replace(Target, " ", "")
    Target = Replace(Target, " ", "")
    Cells(i, 2) = Target
Next i

End Sub

変換後は””、つまり文字なしにしました。

半角スペースを削除するコード
スペース削除後のデータ

全部半角スペースに変換できないの??

それは難しい。

“田中太郎”を”田中 太郎”に変換するのは厳しいです。

どこで苗字と名前を区切るかを判別しないといけないからですね。

全角を半角に統一したいなら、Replace(Target, “ ”, ” “)でOKです。

改行を削除する方法

セルの中に改行があると、見栄えが悪いですよね。

セル内に改行があるデータ

不要な改行はReplaceで削除しましょう。

Sub test()

endRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To endRow
    Target = Cells(i, 1)
    Target = Replace(Target, vbLf, "")
    Cells(i, 2) = Target
Next i

End Sub
Replace(“文字列”, vbLf, “”)

VBAでは改行をvbLfとして認識しています。

なのでReplaceでvbLfを””、つまり文字なしにすればOK。

改行を削除したデータ

大文字小文字の変換:UCase, LCase

UCaseで大文字に変換

UCaseでアルファベットを大文字に変換できます。

Sub test()

Target = "sample"
MsgBox Target

Target = UCase(Target)
MsgBox Target

End Sub
UCaseで大文字に変換する図

実行すれば、最初の文字”sample”が大文字の”SAMPLE”に変換されているとわかります。

LCaseで小文字に変換

小文字に変換するには、LCaseを使います。

Sub test()

Target = "SAMPLE"
MsgBox Target

Target = LCase(Target)
MsgBox Target

End Sub

UCaseと逆のことをしているだけですね。

StrConvで好きな形式に変換

StrConvを使えば、大文字にも小文字にも変換可能です。

他にも、ひらがなやカタカナに変えることもできますが、ほとんど使うことはありませんね。。。

StrConv(“文字列”, 変換条件)
 vbUpperCase:大文字
 vbLowerCase:小文字
 vbProperCase:先頭だけ大文字
 vbHiragana   :ひらがな
 vbKatakana   :カタカナ

例えば、”sample”を大文字に変換したければ、StrConv(“sample”, vbUpperCase)と書きましょう。

まとめ

今回は文字の置換方法について解説しました。

コードが短くて単純でしたね。

もしよければ、イベントプロシージャで自動的に統一する方法も挑戦してみてください!

おススメ書籍

VBA
スポンサーリンク
HTOMblog

コメント

タイトルとURLをコピーしました