エクセルVBAでセル内文字列の前後の空白を一括で削除する方法

こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も「時間クリエーター」としてバシバシ時短に取り組んでます！

全10回の連載でエクセルのデータ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。今回はその第4回目です。

前回お伝えした「チェックボックスのONOFFでボタン表示を変える方法」に続いて、今回は「セル内文字列の前後の空白を一括で削除」するテクニックをご紹介します。

エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法

チェックボックスのONOFFでボタンの表示を「全角→半角」「全角→半角」とを切り替えることができれば見た目がすっきりすると思います。それに応じて一括変換の処理も変えるようにソースコードを修正していきます。

asatte.biz

2017-07-17 20:00

文字列の前後にある空白。タイプミスやコピペミス、入力オペレーターのクセなどで入ってしまうことがあるんですよね。この前後の空白ですが、ワークシートの体裁を整えたり、他のアプリケーションにデータを取り込みたいときなど、邪魔になることがあると思います。

文字列の前後にある空白を削除することはTrim関数を使ってワークシート内でもできるのですが、毎回入力するのは面倒ですよね。というわけで前回と同じように、VBAを使って別のエクセルファイルでも使える汎用的なプログラムを作っていきます。

1 セル内の前後の空白を削除するプログラムの作成
- 1.1 前回使用した一括変換プログラムにボタンを追加する
- 1.2 サンプルコードを貼り付ける
2 プログラムコードの解説
- 2.1 Trim関数でセル内の前後の空白を削除する
3 前後の空白を一括削除プログラムの動作確認
4 まとめ
5 時短のおつまみ
6 [連載記事全10回] エクセルVBAで文字列変換ツールを作る

セル内の前後の空白を削除するプログラムの作成

前回使用した一括変換プログラムにボタンを追加する

はじめからユーザーフォームを作って、ワークシートに起動ボタンを配置して・・・という作業は面倒だし効率が悪いので、前回使ったプログラムを再利用します。デスクトップにある「一括変換.xls」を開きます。

UserFrom1を開いて新たに「前後の空白を削除」ボタンを追加します。ボタンの表示名はプロパティウィンドウのCaptionで変更できます。ユーザーフォームやボタンの大きさは見やすいように調整して下さい。

サンプルコードを貼り付ける

「前後の空白を削除」ボタンをダブルクリックしてコードウィンドウを開きます。「CommandButton3_Click()」の下に以下のコードを貼り付けます。このプログラムコードは「セル内の文字列に含まれる改行を一括で削除する方法」で作ったコードをコピーして一部を修正して使っています。

エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法

ワークシートのセル内にある改行を消したいって思ったことありませんか？データを別のアプリケーションに取り込んだり、データベースに登録したりするときに改行が邪魔になることがあります。そんな時に改行を一括で削除できるツールがあったら便利ですよね。

asatte.biz

2017-06-24 08:36

ユーザーフォーム上のボタンを押して、変換したい列を選択して、OKボタンを押すと開始行から終了行までループして変換処理を実行する。というプログラムの流れは変わらないからです。違いはループ内でどういった変換処理を行うかということだけです。

'空白の削除
Private Sub CommandButton3_Click()

Dim tmp
Dim Col As Long
Dim Row_Start As Long
Dim Row_End As Long
Dim strCelldata As String
Dim y As Long

    '処理開始行
    Row_Start = 2
    
    '最終行を取得
    With ActiveSheet.UsedRange
     Row_End = .Rows(.Rows.Count).Row
    End With

    'キャンセルが押された場合は処理を終了する
    On Error GoTo myError
    tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address
    Range(tmp).Select
    
    '選択した列番号
    Col = Selection.Column
                       
    For y = Row_Start To Row_End
        strCelldata = Cells(y, Col).Value         
        'セル内文字列の前後の空白を削除する
        strCelldata = Trim(strCelldata)         
        Cells(y, Col).Value = strCelldata
    Next y
    
myError:
End Sub

'空白の削除

Private Sub CommandButton3_Click()

Dim tmp

Dim Col As Long

Dim Row_Start As Long

Dim Row_End As Long

Dim strCelldata As String

Dim y As Long

'処理開始行

Row_Start = 2

'最終行を取得

With ActiveSheet.UsedRange

Row_End = .Rows(.Rows.Count).Row

End With

'キャンセルが押された場合は処理を終了する

On Error GoTo myError

tmp = Application.InputBox("列を選択して下さい", "列の選択", Type:=8).Address

Range(tmp).Select

'選択した列番号

Col = Selection.Column

For y = Row_Start To Row_End

strCelldata = Cells(y, Col).Value

'セル内文字列の前後の空白を削除する

strCelldata = Trim(strCelldata)

Cells(y, Col).Value = strCelldata

Next y

myError:

End Sub

プログラムコードの解説

重要なポイントに要点を絞って説明していきます。今回変更しているのは27行目から34行目のFor文の中で行う処理です。

Trim関数でセル内の前後の空白を削除する

Trim(文字列)

Trim関数は文字列の前後の空白を削除することができます。空白が1つでも複数でも全て削除します。ここで注意してほしいのが前後の空白だけだということです。文字列内にある空白は削除しません。

なぜ前後の空白だけしか削除してくれないのでしょう？前後の空白はタイプミスやコピペミスで入ってしまうことが多いのに対し、文字内の空白は意図的に入力する場合が多いからです。

例えば名前です。苗字と名前の間の空白は、苗字と名前が区別しやすいようにするために意図的に入れていますよね。前後の空白だけを取り除きたいのに、苗字と名前の間の空白が削除されてしまっては困るというわけですね。

今回追加したプログラムコードは以下になります。

'セル内文字列の前後の空白を削除する
strCelldata = Trim(strCelldata)

1 2	'セル内文字列の前後の空白を削除する strCelldata = Trim(strCelldata)

たったこれだけ。簡単ですね～。

前後の空白を一括削除プログラムの動作確認

ワークシートの起動ボタンを押します。フォームが表示されたら「前後の空白を削除」ボタンを押します。

前後の空白を削除したい列をクリックします。InputBoxのOKボタンを押します。

セル内にある前後の空白が削除されました。

変換前と変化後を見比べてみます。前後の空白だけが削除され、文字列内にある空白は削除されていないのがわかりますね～。

まとめ

いかがでしたか？今回の内容をまとめます。

Trim関数でセル内の前後の空白を削除できる
前後の空白は何文字あろうと削除する
文字列内の空白は削除しない

時短のおつまみ

今日のおつまみはコレです！

Windowsキー + D 　全てのウィンドウを最小化する

開いてる全てのウィンドウを最小化してデスクトップを表示できます。このショートカットは個人的にはショートカットランキングBEST1ですね。コピペの次に使うことの多いショートカットキーです。ぜひ試してみてくださ～い。

それではまた～。

[連載記事全10回] エクセルVBAで文字列変換ツールを作る

全角半角の変換をしたり、空白行や改行を削除したり。データ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。1つの記事で1機能ずつ追加していきますので、少しずつVBAを学びながら進めることができます。