Excel 文字列から特定の文字を複数まとめて削除する関数とスクリプト
はじめに
Excelを使用している際、セル内の文字列から特定の文字を複数種類まとめて削除したい場面は多くあります。
一般的にはExcelのSUBSTITUTE関数を利用することで、セル内の特定のテキストを空白に置き換えて削除できます 。
しかし、この関数が一度に対象とできるのは1種類の文字のみです 。
この記事では、この課題を解決するための複数の方法を紹介します。
SUBSTITUTE関数を複数重ねる従来の方法に加え、Microsoft 365の新しい機能であるREDUCEとLAMBDA関数を利用した効率的な数式を紹介します。
さらに、VBAやOfficeスクリプトといった自動化手法についても解説します。
方法1：基本的な方法（SUBSTITUTE関数を重ねる）
ExcelのSUBSTITUTE関数は、指定したセルの文字列内の特定の文字を、別の文字に置き換えることができます。
この機能を利用して、削除したい文字を空白に置き換えることで、文字を削除できます。
ただし、SUBSTITUTE関数は1種類の文字しか一度に扱えません。
複数の文字を削除したい場合は、削除したい文字の数だけSUBSTITUTE関数を重ねて使用する必要があります。
数式の例
以下の数式は、セルB1にある文字列から「かさ」と「B」という2種類の文字を削除する場合の例です。
=SUBSTITUTE(SUBSTITUTE(B1,"かさ",""),"B","")この数式は、まず内側のSUBSTITUTE関数でセルB1の「かさ」を空白に置き換え、次に外側のSUBSTITUTE関数で、その結果に対して「B」を空白に置き換えるという処理を行います。
メリット・デメリット
- メリット: Excelのバージョンを問わず利用できるため、幅広い環境で対応可能です。数式の構造が単純であり、IT初心者にも理解しやすい方法です。
- デメリット: 削除したい文字の種類が増えるほど数式が長くなり、管理が煩雑になります。また、特定の文字の追加や変更のたびに数式を編集する必要があるため、メンテナンス性に劣ります。
方法2：新しい関数を使った方法（Microsoft 365向け）
Microsoft 365のExcelには、より高度なデータ処理を可能にする新しい関数が導入されています。
REDUCE関数とLAMBDA関数を組み合わせることで、SUBSTITUTE関数を複数回重ねることなく、指定した複数の文字を一度に削除できます。
この方法は、数式が簡潔になり、メンテナンス性が向上します。
数式の解説
この方法では、REDUCE関数を使って指定した範囲の削除対象文字を一つずつ処理し、LAMBDA関数でその処理内容を定義します。
具体的な数式は以下の通りです:
=REDUCE(元のテキスト,削除対象テキスト,LAMBDA(r,s,SUBSTITUTE(r,s,"")))
REDUCE関数の第1引数: 削除前の元のテキストを指定します。REDUCE関数の第2引数: 削除対象となる文字が入力されたセル範囲を指定します。LAMBDA関数:REDUCE関数が各削除対象文字を処理する際の具体的な動作を定義します。この例では、SUBSTITUTE関数を使って元のテキストから削除対象文字を空白に置き換える処理を繰り返します。
新しい形態の数式なため難解ですが、構造自体はシンプルで元のテキストと削除対象テキストをセル範囲で指定しているだけです。
メリット・デメリット
- メリット:
- 削除対象の文字が増えても数式が長くなることがなく、シンプルに保てます。
- 削除対象文字の追加や変更が、リストのセルを編集するだけで済み、数式の修正が不要です。
- デメリット:
- この機能はMicrosoft 365など、新しいExcel環境でのみ利用可能です。
LAMBDA関数やREDUCE関数の概念は、従来のExcelユーザーにとっては理解に時間がかかる可能性があります。
方法3：VBAを使った方法
ExcelのVBA（Visual Basic for Applications）を利用することで、複数文字を削除する作業を自動化できます。
これは、関数の制限を超えた複雑な処理や、特定の操作を繰り返す場合に有効です。
また関数よりも多くのデータへの利用に向きます。
VBAマクロとして作成すれば、ボタン一つで実行できるようになり、業務効率が向上します。
VBAコードの例
以下のコードは、指定した範囲のセルから、配列target_charsに含まれるすべての文字を削除するVBAマクロです。
Sub DeleteMultiChars()
'----------------------------------------------------
' ▼設定箇所
' 削除対象の文字を配列に格納
Const target_chars As String = "かさ,B,なA"
' 処理対象のセル範囲を指定
Const target_range_address As String = "B:B"
'----------------------------------------------------
Dim target_array As Variant
target_array = Split(target_chars, ",")
Dim target_range As Range
Set target_range = ActiveSheet.Range(target_range_address)
' セルごとにループ処理
Dim cell As Range
For Each cell In target_range.Cells
' セルが空でなければ処理を実行
If Not IsEmpty(cell.Value) Then
' 削除対象文字ごとにループ処理
Dim i As Long
For i = LBound(target_array) To UBound(target_array)
' SUBSTITUTE関数をVBAで実行
cell.Value = Replace(cell.Value, target_array(i), "")
Next i
End If
Next cell
End Sub簡単な利用方法
このVBAマクロは、以下の手順で利用できます。
- マクロの有効化: Excelファイルを開く際、「マクロを有効にする」を選択します。
- VBAエディターを開く:
Alt+F11キーを押してVBAエディターを開きます。 - 新しいモジュールの挿入:
- 左側のプロジェクトエクスプローラーで、対象のファイル名を右クリックします。
- 「挿入」 > 「標準モジュール」を選択します。
- コードの貼り付け:
- 新しく開いたモジュール画面に、提供されたVBAコードをコピー＆ペーストします。
- 設定の変更（任意）:
- コード上部の
target_chars（削除したい文字）とtarget_range_address（処理対象のセル範囲）の値を、用途に合わせて編集します。
- コード上部の
- マクロの実行:
F5キーを押すか、VBAエディターのツールバーにある再生ボタン（▶）をクリックしてマクロを実行します。
- または、Excelの「開発」タブから「マクロ」を選択し、「
DeleteMultiChars」を実行します。
- マクロ付きファイルとして保存:
- マクロを次回も使用したい場合は、ファイルを「Excelマクロ有効ブック（.xlsm）」として保存します。
メリット・デメリット
- メリット:
- Excelのバージョンに依存せず、ほとんどの環境で利用可能です。
- 複雑な条件分岐や大量のデータを処理する場合に適しており、柔軟性が高いです。
- マクロとして保存すれば、再利用が容易で、作業を自動化できます。
- デメリット:
- プログラミングの知識が必要です。
- セキュリティ設定によってはマクロの実行が制限される場合があります。
- 共有する際は、マクロが有効なファイル形式（
.xlsmなど）で保存する必要があります。
方法4：Officeスクリプトを使った方法
Officeスクリプトは、デスクトップ版Excel（Microsoft 365）だけでなく、Web版Excelでも動作する自動化ツールです。
VBAよりも新しい、汎用性が高いプログラミング言語をベースにしており、VBAが使えない環境でも、特定の文字を削除するなどの作業を自動化できます。
特にPower Automateと連携することで、複数のアプリケーションをまたいだワークフローにExcelの処理を組み込むことが可能になります。
Officeスクリプトについてさらに詳しく知りたい場合は、「【Excel】Officeスクリプトとは？ VBAとの違いを比較し、未来の業務効率化に備える」を参照してください。
Officeスクリプトの例
以下のコードは、アクティブシートの指定した範囲から、配列targetCharsに含まれるすべての文字を削除するOfficeスクリプトです。
unction main(workbook: ExcelScript.Workbook) {
// ----------------------------------------------------
// ▼設定箇所
// 削除対象の文字を配列に格納
const targetChars = ["かさ", "B", "なA"];
// 処理対象のセル範囲を指定
const rangeAddress = "B2:B5";
// ----------------------------------------------------
let sheet = workbook.getActiveWorksheet();
let range = sheet.getRange(rangeAddress);
let values = range.getValues();
for (let i = 0; i < values.length; i++) {
for (let j = 0; j < values[i].length; j++) {
let cellValue = values[i][j] as string;
if (cellValue) {
for (let k = 0; k < targetChars.length; k++) {
cellValue = cellValue.split(targetChars[k]).join("");
}
values[i][j] = cellValue;
}
}
}
range.setValues(values);
}簡単な利用方法
このOfficeスクリプトは、以下の手順で利用できます。
- スクリプトエディターを開く: Excelを開き、「自動化」タブの「Officeスクリプト」グループにある「新しいスクリプト」をクリックします。
- コードの貼り付け: 開いたスクリプトエディターに、提供されたOfficeスクリプトのコードをコピー＆ペーストします。
- スクリプトの保存: スクリプトに分かりやすい名前を付けて保存します。
- 設定の変更（任意）:
- コード上部の
targetChars（削除したい文字）とrangeAddress（処理対象のセル範囲）の値を、用途に合わせて編集します。
- コード上部の
- スクリプトの実行:
- スクリプトエディターのツールバーにある「実行」ボタンをクリックしてスクリプトを実行します。
- ワークブックの保存: 変更を反映したワークブックを保存します。
メリット・デメリット
- メリット:
- デスクトップ版とWeb版の両方で動作し、クラウド環境での業務効率化に貢献します。
- Power Automateと連携することで、他のクラウドサービスとの統合が可能です。
- デメリット:
- 利用にはMicrosoft 365の法人向けまたは教育機関向けライセンスが必要です。
まとめ
Excelの文字列から複数種類の文字を削除する方法として、以下の4つのアプローチを解説しました。
| 方法 | 主な対象ユーザー/環境 | メリット | デメリット |
|---|---|---|---|
方法1：SUBSTITUTE関数の重ね技 | 旧バージョンを含むすべてのExcelユーザー | 互換性に優れ、どのバージョンでも利用可能。 | 数式が長く複雑になり、メンテナンスが煩雑。 |
方法2：REDUCEとLAMBDA関数 | Microsoft 365ユーザー | 数式がシンプルで、削除対象の追加・変更が容易。 | Microsoft 365でのみ利用可能。 |
| 方法3：VBA | 旧バージョンを含むすべてのExcelユーザー | 柔軟性が高く、複雑な処理や大量データに適している。 | セキュリティ設定に注意により制限が入る場合がある |
| 方法4：Officeスクリプト | Microsoft 365ユーザー | Web版Excelでも動作し、Power Automateとの連携が可能。 | Microsoft 365の法人向けまたは教育機関向けライセンスが必要 |
これらの方法から、自身のExcelバージョン、スキルレベル、そして目的（単発的な作業か、定型的な自動化か）に合わせて最適な手法を選択することで、日々の業務を効率化できます。