Excel VBA

Excelセル内の改行を置換/削除する方法！関数の他VBAで一括処理も！

Excelセル内の改行を置換/削除したいときはないでしょうか。

例えばアンケートで集めた改行付き住所情報を1行にまとめたいときなどです。

けど、そんな中で悩むことは、

・セル内の改行を置換/削除したいが方法がわからない。
・VBAでセル内の改行を置換/削除したいがやり方がわからない。

ですよね。

今回はそんなお悩みを解決する

・Excelセル内の改行を置換/削除する基本操作
・VBAでExcelセル内の改行を置換/削除する方法
・VBAで別シートのセル内の改行を置換/削除する方法

についてまとめます！

VBAでセル内の改行を置換/削除するイメージ

VBAでセル内の改行を置換/削除するイメージについて説明をします。

セル内の改行を置換/削除したい文字列を複数用意します。

はじめに標準機能の置換処理で改行のみを削除していきます。

次に関数で改行の置換処理をおこないます。

最後にVBAで複数行を一度にまとめて置換処理をしていきます。

VBAを実行すると、

はい、改行を削除できました。

別シートの文字列もカンタンな設定でサクッと改行を削除できます。

それでは早速使ってみましょう。

セル内の改行を置換/削除データを用意する

まずは、セル内の改行を置換/削除データを用意しましょう。

A1からA2セルまで改行付きデータを入力していきます。

Excelセル内の改行を置換/削除する基本操作

Excelセル内の改行を置換/削除する基本操作について説明します。

「ホーム」タブ右端の「検索と選択」で「置換」をクリックします。

「検索する文字列」の入力ボックスをクリックします。

Ctrl+aキーを押し、
Ctrl+Jキーを押します。

入力ボックスに「.」が入ったことを確認します。

タカヒロ
タカヒロ
何度か繰り返すと見えない改行コードが含まれた形になりますので、初めに全体選択の［Ctrl］+［a］キーを押すようにお願いします。

「検索する文字列」の入力ボックスへ、削除の場合は空欄、別の文字へ置き換える場合は文字を入力します。

「ずべてを置換」をクリックし、置換処理をおこないます。

はい、できましたね。

Excelセル内の改行を置換/削除する関数

Excelセル内の改行を置換/削除する関数について説明します。

使う関数はREPLACE関数とSUBSTITUTE関数になります。

REPLACE関数で改行を削除する

REPLACE関数に文字列を取り込む段階で改行コードは取り除かれる仕様をそのまま利用しています。
ですので、文字数などの設定は表示に影響させない内容となっており、置換をしているわけではありません。

数式は以下の通りです。
REPLACE(元の文字列,開始位置,文字数,置換文字)

元の文字列にA1セル、開始位置を1文字目を指定、文字数は0を指定、置換文字は空文字””を指定します。

REPLACE(A1,1,0,””)

はい、できましたね。

SUBSTITUTE関数で改行を置換/削除する

REPLACE関数では改行の削除のみでしたが、SUBSTITUTE関数を使うと改行の削除の他、別の文字への置換も可能です。

数式は以下の通りです。
SUBSTITUTE(元の文字列,検索する文字列,置換文字,「置換対象」)

元の文字列にA2セル、検索する文字列に改行を示す「CHAR(10)」を指定、置換後の文字は全角スペース”　”を指定します。
「置換対象」は置換する位置である文字数を指定するものですが、今回は省略します。

SUBSTITUTE(A1, CHAR(10), “　”)

はい、できましたね。

数式を下のセルまでドラッグしましょう。

はい、2行目も置換されましたね。

Excelセル内の改行を置換/削除するVBA

続いて、Excelセル内の改行を置換/削除するVBAについて説明をします。

Replaceメソッドで置換する方法と、正規表現を使い置換する方法の2種類となります。

Excelセル内の改行を置換/削除するVBA-Replaceメソッドで置換

ReplaceメソッドでExcelセル内の改行を置換/削除するVBAを準備します。

サンプルコードは以下の通りです。

Sub Replaceメソッドで置換する()

    '置換範囲を設定します。
    Set regRange = Selection 'マウスやキーボード操作で選択した範囲が置換対象となります。
    'Set regRange = Range("A1:A2") 'Range指定した置換対象となります。
    'Set regRange = Sheets("追加シート").Range("A1:A2") '別シートのRange指定が置換対象となります。
    
    
    '置換後の文字を指定します。
    strRegWord = "　"
    
    '範囲分置換処理を繰り返します。
    For Each objParagraph In regRange
          
        '変換後の文字列と変換前の文字列を置き換えます。
         objParagraph.Value = Replace(objParagraph.Value, vbLf, strRegWord)
    Next
    
    Set regRange = Nothing
    Set objParagraph = Nothing

End Sub

VBAの設定をする

Excelセル内の改行を置換/削除するVBAの設定を行っていきましょう。

設定箇所は以下の通りです。

Set regRange = Selection

置換範囲を設定します。
マウスやキーボード操作で選択した範囲が置換対象となります。

strRegWord = “　”

置換後の文字を指定します。サンプルは全角スペースを指定していますが、変更頂いても問題ありません。

VBAを実装し実行する

VBAを実装し実行してみましょう。

実装方法は「VBAの実装手順」をご覧ください。

実装できたらマクロから「Replaceメソッドで置換する」を見つけます。

置換対象範囲をマウスなどで選択し、マクロを実行します。

はい、選択範囲の改行が置換されていますね！

VBAの説明

Excelセル内の改行を置換/削除するVBAについて説明をします。

置換範囲を設定します。マウスやキーボード操作で選択した範囲が置換対象となりますが、Renge指定や別シートのRenge指定もできます。

Set regRange = Selection

範囲分置換処理を繰り返します。

For Each objParagraph In regRange

Replaceメソッドで変換後の文字列と変換前の文字列を置き換えます。

objParagraph.Value = Replace(objParagraph.Value, vbLf, strRegWord)

タカヒロ
タカヒロ
vbLfはセル内の改行を意味します。

式は以下の通りです。

Replace(元の文字列,検索する文字列,置換文字)

別シートのセル内の改行を置換/削除するVBA

続いて、これまで今見ているアクティブシートの選択範囲を置換処理の対象としてきましたが、別シートにある文字列を置換対象にするよう変更をしてみましょう。

シートを追加します。サンプルでは「追加シート」としています。文字列の範囲はRange(“A1:A5”)とします。

以下のコードをアクティブにします。

Set regRange = Range(“A1:A2”) ‘Range指定した置換対象となります。

実行してみましょう。

はい、別シートの指定範囲が置換されていますね。

Excelセル内の改行を置換/削除するVBA-正規表現で置換

次は正規表現でExcelセル内の改行を置換/削除するVBAを準備します。

サンプルコードは以下の通りです。

Sub RegExpで正規表現を使い置換する()

    'RegExpをオブジェクトにセットします｡　https://docs.microsoft.com/ja-jp/visualstudio/ide/using-regular-expressions-in-visual-studio?view=vs-2019
    Set objRegExp = CreateObject("VBScript.RegExp")
    
    '置換範囲を設定します。
    Set regRange = Selection 'マウスやキーボード操作で選択した範囲が置換対象となります。
    'Set regRange = Range("A1:A2") 'Range指定した置換対象となります。
    'Set regRange = Sheets("追加シート").Range("A1:A2") '別シートのRange指定が置換対象となります。
    
    '検索するパターンを設定します。
    objRegExp.Pattern = "\n"
    
    '置換後の文字を指定します。
    strRegWord = "　"
    
    '大文字と小文字の区別をする(既定値：False)かしないか(False)を設定します。
    objRegExp.IgnoreCase = False
    
    '検索文字列全体を検索する(True)か、最初の一致を検索する(既定値：False)かを設定します。
    objRegExp.Global = True
    
    '範囲分置換処理を繰り返します。
    For Each objParagraph In regRange
    
        '変換後の文字列と変換前の文字列を置き換えます。
          objParagraph.Value = objRegExp.Replace(objParagraph.Value, strRegWord)
          
    Next
    
    Set regRange = Nothing
    Set objRegExp = Nothing
    Set objParagraph = Nothing

End Sub

VBAの設定をする

Excelセル内の改行を置換/削除するVBAの設定を行っていきましょう。

設定箇所は以下の通りです。

Set regRange = Selection

置換範囲を設定します。
マウスやキーボード操作で選択した範囲が置換対象となります。

strRegWord = “　”

置換後の文字を指定します。サンプルは全角スペースを指定していますが、変更頂いても問題ありません。

検索するパターンを設定します。”\n”は正規表現で改行を意味します。

objRegExp.Pattern = “\n”

VBAを実装し実行する

VBAを実装し実行してみましょう。

実装方法は「VBAの実装手順」をご覧ください。

実装できたらマクロから「RegExpで正規表現を使い置換する」を見つけます。

置換対象範囲をマウスなどで選択し、マクロを実行します。

はい、選択範囲の改行が置換されていますね！

VBAの説明

Excelセル内の改行を置換/削除するVBAについて説明をします。

今回置換処理に使う機能はVBScriptで用意されているRegExpオブジェクトとなります。RegExpを呼び出し、VBAのオブジェクトにセットします｡

Set objRegExp = CreateObject(“VBScript.RegExp”)

置換範囲を設定します。マウスやキーボード操作で選択した範囲が置換対象となりますが、Renge指定や別シートのRenge指定もできます。

Set regRange = Selection

範囲分置換処理を繰り返します。

For Each objParagraph In regRange

変換後の文字列と変換前の文字列を置き換えます。

objParagraph.Value = objRegExp.Replace(objParagraph.Value, strRegWord)

別シートの文字列のセル内の改行を置換/削除

同じく、別シートにある文字列を置換対象にするよう変更をしてみましょう。

参照先シート名は「追加シート」とし、文字列の範囲はRange(“A1:A2”)とします。

以下のコードをアクティブにします。

Set regRange = Sheets(“追加シート”).Range(“A1:A2”) ‘別シートのRange指定が置換対象となります。

できましたね。

VBAの実装手順

実装手順は以下の通りです。

Excel側にVBAを実装していきます。

①Excelを新規に開き、「開発」タブをクリックし、「VisualBasic」をクリックします。
もしくはショートカットキー「Alt」＋「F11」でもOKです。

②標準モジュールを追加します。
左ペインのVBAProjectを右クリックし、「挿入」、「標準モジュール」を選択します。

③右ペインのウインドウに上記のVBAを入力します。

こちらで完了です。

VBAを実行する

では早速VBAの実行をしてみましょう。

①「開発」タブの「VBA」をクリックし実行したいマクロを選択し、「実行」をクリックします。

②処理がされたことが確認できれば完了です。

さいごに

いかがでしょうか。

今回は、

・Excelセル内の改行を置換/削除する基本操作
・VBAでExcelセル内の改行を置換/削除する方法
・VBAで別シートのセル内の改行を置換/削除する方法

についてまとめました。

また、他にも便利な方法がありますので、よろしければご参照頂ければと思います。



この記事の関連キーワード

こちらの記事の関連キーワード一覧です。クリックするとキーワードに関連する記事一覧が閲覧できます。








コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

RECOMMENDこちらの記事も人気です。