こんにちは～。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も「時間クリエーター」としてノー残業をめざしますっ！
全10回の連載でエクセルのデータ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。今回はその第5回目です。
前回お伝えした「セル内文字列の前後の空白を一括で削除する方法」に続いて、今回は「Replace関数で文字列内の空白を削除する」テクニックをご紹介します。
Trim関数では文字列の前後の空白を削除することはできても、文字列内にある空白は削除できませんでした。それには理由があって文字列内の空白は意図的に入れている場合があるからということでしたね。
でも文字列内の空白を削除したい場合もありますよね。別のアプリケーションで作ったデータなどをエクセルに取り込んだときに、文字列内に余計な空白が入っていることがあります。
よくあるのが文章の体裁を整えるために空白を入れたり、ドローソフトなどでレイアウトの見栄えをよくするために空白を入れたりするケースがあります。
エクセルに取り込んだ場合これらの空白は邪魔になることが多いです。
目次
ユーザーフォームにチェックボックスとラベルを追加する
さて、文字列内の空白を削除する機能を追加していきますが、過去の記事で紹介したチェックボックスを利用してボタンの表示を変える方法で実装していきたいと思います。チェックボックスの作り方などの詳細は下記の記事を参考にしてみてください。
一括変換プログラムにチェックボックスを追加していきます。前回作った「一括変換.xls」を開きます。
UserForm1を開いて新たにチェックボックスを追加します。ツールボックスからチェックボックスをクリックした後に、ユーザーフォーム上の置きたい位置でドラッグします。続いてラベルを配置してCaptionを「全ての空白を削除」にします。
- チェックボックスをONにする→全ての空白を削除
- チェックボックスをOFFにする→前後の空白を削除
というようなプログラムの動きにしたと思います。
チェックボックスがONOFFされたときの処理を追加する
チェックボックスをダブルクリックしてVisual Basic Editorを開きます。CheckBox2_Click()プロシージャの下に以下のコードを書きます。
|
1 2 3 4 5 6 7 8 9 10 |
Private Sub CheckBox2_Click() 'チェックボックスの値でボタンの文字を変える If CheckBox2.Value = True Then CommandButton3.Caption = "全ての空白を削除" Else CommandButton3.Caption = "前後の空白を削除" End If End Sub |
Replace関数で文字列の置き換えを行う
前回作った「前後の空白を削除」のソースコードの修正
今度は前回作った「前後の空白を削除」のソースコードの一部を変更します。といっても大幅に変更するわけではありません。 それではCommandButton3_Click()プロシージャに移ります。
変更前
|
1 2 |
'セル内文字列の前後の空白を削除する strCelldata = Trim(strCelldata) |
変更後
|
1 2 3 4 5 6 7 8 |
If CheckBox2.Value = True Then 'セル内の全ての空白を削除する strCelldata = Replace(strCelldata, " ", "") strCelldata = Replace(strCelldata, " ", "") Else 'セル内文字列の前後の空白を削除する strCelldata = Trim(strCelldata) End If |
Replace関数を使うと文字列の置換を行うことができます。今回の場合は置換するわけではなく削除したいんですよね。その場合、置換後の文字列を何も指定しないで””とすることで、検索文字列を削除したことになります。
上記のソースコードの場合、IF文を使って、チェックボックスのチェックがONならReplace関数で空白を削除、OFFならTrim関数で前後の空白を削除という処理にしています。
空白は全角の場合と半角の場合があるので、Replace関数を続けて実行しています。まず半角の空白を削除したデータを変数strCelldataに入れ、さらに変換したstrCelldataから全角の空白を削除するという処理にしています。
修正したソースコード
修正したソースコードは以下になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 |
'空白の削除 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 If CheckBox2.Value = True Then 'セル内の全ての空白を削除する strCelldata = Replace(strCelldata, " ", "") strCelldata = Replace(strCelldata, " ", "") Else 'セル内文字列の前後の空白を削除する strCelldata = Trim(strCelldata) End If Cells(y, Col).Value = strCelldata Next y myError: End Sub |
アプリケーションの動作確認をする
ワークシート上の起動するボタンをクリックします。チェックボックスをクリックします。
ボタンの表示が「前後の空白を削除」から「全ての空白を削除」に変わりましたね。全ての空白を削除ボタンをクリックします。変換対象の列の上部をクリックします。OKボタンをクリックします。
文字列内に含まれる全ての空白が削除されました。
もちろんチェックボックスのチェックを外せば、前後の空白のみ削除ができます。
まとめ
いかがでしたか？今回の内容をまとめます。
- Replace関数は文字列の置換ができる
- 置換後の文字列を何も指定しないで””とすることで、検索文字列を削除できる
- 空白を削除したい場合は全角、半角があることをお忘れなく
時短のおつまみ
今日のおつまみはコレです！
コピペと同じくらい頻繁に使われる超人気ショートカットキーですね～。１コ前の作業状態に戻せる便利なコマンド。日常生活でも使えたらどんなにいいだろう～。必殺コマンド「コントロールゼット」をお忘れなくっ！
それではまた～。
[連載記事 全10回] エクセルVBAで文字列変換ツールを作る
全角半角の変換をしたり、空白行や改行を削除したり。データ編集に便利な機能を詰め込んだランチャーアプリを作っていきます。1つの記事で1機能ずつ追加していきますので、少しずつVBAを学びながら進めることができます。
- エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法
- エクセルVBAでセル内の文字列を操作して全角文字を半角に一括変換する方法
- エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法
- エクセルVBAでセル内文字列の前後の空白をまとめて一括で削除する方法
- エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する (本記事です)
- エクセルVBAのCountA関数でシート内の空白行をまとめて一括で削除する方法
- エクセルVBAで空白行を削除しないでまとめて一括非表示で隠す方法
- エクセルVBAで条件付き書式を使わず1行おきに色を付ける方法
- エクセルVBAで集計や並び替えを使わず1行おきに空白行を一括で挿入する方法
- エクセルVBAで同じデータが切り替わる項目ごとに空白行を挿入する方法