エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する

自力で老眼回復

こんにちは~。「デザインもできるシステムエンジニア」のあつもり(@atumori17)です。今日も「時間クリエーター」としてノー残業をめざしますっ!

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

前回お伝えした「セル内文字列の前後の空白を一括で削除する方法」に続いて、今回は「Replace関数で文字列内の空白を削除する」テクニックをご紹介します。

エクセルVBAでセル内文字列の前後の空白をまとめて一括で削除する方法
ワークシートの体裁を整えたり、他のアプリケーションにデータを取り込みたいときなど、文字列の前後の空白邪魔になることがあると思います。前後の空白を削除することはTrim関数を使ってワークシート内でもできるのですが、毎回入力するのは面倒ですよね。そこでVBAを使って一括で削除できるテクニックを紹介します。

Trim関数では文字列の前後の空白を削除することはできても、文字列内にある空白は削除できませんでした。それには理由があって文字列内の空白は意図的に入れている場合があるからということでしたね。

でも文字列内の空白を削除したい場合もありますよね。別のアプリケーションで作ったデータなどをエクセルに取り込んだときに、文字列内に余計な空白が入っていることがあります

よくあるのが文章の体裁を整えるために空白を入れたり、ドローソフトなどでレイアウトの見栄えをよくするために空白を入れたりするケースがあります。

エクセルに取り込んだ場合これらの空白は邪魔になることが多いです。

レクタングル大336×280

ユーザーフォームにチェックボックスとラベルを追加する

さて、文字列内の空白を削除する機能を追加していきますが、過去の記事で紹介したチェックボックスを利用してボタンの表示を変える方法で実装していきたいと思います。チェックボックスの作り方などの詳細は下記の記事を参考にしてみてください。

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

一括変換プログラムにチェックボックスを追加していきます。前回作った「一括変換.xls」を開きます。

UserForm1を開いて新たにチェックボックスを追加します。ツールボックスからチェックボックスをクリックした後に、ユーザーフォーム上の置きたい位置でドラッグします。続いてラベルを配置してCaptionを「全ての空白を削除」にします。

  • チェックボックスをONにする→全ての空白を削除
  • チェックボックスをOFFにする→前後の空白を削除

というようなプログラムの動きにしたと思います。

チェックボックスがONOFFされたときの処理を追加する

チェックボックスをダブルクリックしてVisual Basic Editorを開きます。CheckBox2_Click()プロシージャの下に以下のコードを書きます。

Replace関数で文字列の置き換えを行う

前回作った「前後の空白を削除」のソースコードの修正

今度は前回作った「前後の空白を削除」のソースコードの一部を変更します。といっても大幅に変更するわけではありません。 それではCommandButton3_Click()プロシージャに移ります。

変更前

変更後

Replace(置換対象文字列,検索文字列,置換後の文字列)

Replace関数を使うと文字列の置換を行うことができます。今回の場合は置換するわけではなく削除したいんですよね。その場合、置換後の文字列を何も指定しないで””とすることで、検索文字列を削除したことになります。

上記のソースコードの場合、IF文を使って、チェックボックスのチェックがONならReplace関数で空白を削除、OFFならTrim関数で前後の空白を削除という処理にしています。

空白は全角の場合と半角の場合があるので、Replace関数を続けて実行しています。まず半角の空白を削除したデータを変数strCelldataに入れ、さらに変換したstrCelldataから全角の空白を削除するという処理にしています。

修正したソースコード

修正したソースコードは以下になります。

アプリケーションの動作確認をする

ワークシート上の起動するボタンをクリックします。チェックボックスをクリックします。

ボタンの表示が「前後の空白を削除」から「全ての空白を削除」に変わりましたね。全ての空白を削除ボタンをクリックします。変換対象の列の上部をクリックします。OKボタンをクリックします。

文字列内に含まれる全ての空白が削除されました。

もちろんチェックボックスのチェックを外せば、前後の空白のみ削除ができます。

まとめ

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

  • Replace関数は文字列の置換ができる
  • 置換後の文字列を何も指定しないで””とすることで、検索文字列を削除できる
  • 空白を削除したい場合は全角、半角があることをお忘れなく

時短のおつまみ

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

Ctrlキー + z  元に戻す

コピペと同じくらい頻繁に使われる超人気ショートカットキーですね~。1コ前の作業状態に戻せる便利なコマンド。日常生活でも使えたらどんなにいいだろう~。必殺コマンド「コントロールゼット」をお忘れなくっ!

それではまた~。



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

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

  1. エクセルVBAでセル内の文字列に含まれる改行を一括で削除する方法
  2. エクセルVBAでセル内の文字列を操作して全角文字を半角に一括変換する方法
  3. エクセルVBAでチェックボックスのONOFFでボタン表示を変える方法
  4. エクセルVBAでセル内文字列の前後の空白をまとめて一括で削除する方法
  5. エクセルVBAのReplace関数でセルの文字列内の空白をまとめて一括削除する (本記事です)
  6. エクセルVBAのCountA関数でシート内の空白行をまとめて一括で削除する方法
  7. エクセルVBAで空白行を削除しないでまとめて一括非表示で隠す方法
  8. エクセルVBAで条件付き書式を使わず1行おきに色を付ける方法
  9. エクセルVBAで集計や並び替えを使わず1行おきに空白行を一括で挿入する方法
  10. エクセルVBAで同じデータが切り替わる項目ごとに空白行を挿入する方法
レクタングル大336×280
レクタングル大336×280

シェアする

  • このエントリーをはてなブックマークに追加

フォローする