【Excel】エクセルで特定の文字を複数削除する方法｜SUBSTITUTE関数と置換の使い方

いつも記事を読んでいただきありがとうございます！！！ これからもお役に立てる各情報を発信していきますので、今後ともよろしくお願いします(^^)/

Excelで住所・商品コード・電話番号・管理番号などを扱っていると、不要な記号や文字列をまとめて削除したい場面があります。

たとえば、ハイフン、括弧、スペース、特定の接頭語などが混在していると、見た目だけでなく、並べ替えや検索、集計の精度にも影響します。

このようなときは、手作業でセルを一つずつ修正するのではなく、SUBSTITUTE関数で文字を置き換える方法や、検索と置換を使う方法を選ぶと効率的です。

この記事では、エクセルで特定の文字を複数削除する考え方から、実務で使いやすい数式、置換機能の注意点まで解説します。

 

エクセルで特定の文字を複数削除する方法

それではまず、複数の不要文字を数式で削除する基本操作について解説していきます。

A列 元データ	B列 削除後
AB-123(東京)	AB123東京
CD-456(大阪)	CD456大阪

 

SUBSTITUTE関数を重ねる数式

SUBSTITUTE関数は、セル内にある指定文字列を別の文字列へ置き換える関数です。

削除したい場合は、置換後の文字列として空文字列を指定します。

この数式では、まずA2セルに含まれるハイフンを削除し、その結果から左括弧を削除します。

SUBSTITUTE関数は内側から順番に処理されるため、削除対象を増やすときは関数をさらに重ねます。

右括弧も削除する場合は、次のように追加します。

数式を入力したら、B2セル右下のフィルハンドルを下へドラッグしてオートフィルを実行しましょう。

1行目を見出しにしている場合、数式は2行目から入力するのが基本です。

 

複数の記号を空文字列へ置換する操作

住所やコードには、半角スペース、全角スペース、スラッシュ、ハイフンなど、複数種類の不要文字が含まれることがあります。

この場合も、削除したい文字ごとにSUBSTITUTE関数を追加すれば対応できます。

この式は、ハイフン、半角スペース、全角スペース、スラッシュを順に削除します。

半角スペースと全角スペースは別の文字として扱われるため、両方が混在するデータでは両方を指定する必要があります。

削除対象が多いほど数式は長くなりますが、元データを残したまま結果を別列に表示できる点が大きなメリットです。

操作後に想定外の文字まで消えていないか、先頭行と末尾行だけでなく途中のデータも確認しましょう。

 

文字列を一部だけ削除する指定

削除したい対象が一文字ではなく、共通する文字列である場合もSUBSTITUTE関数を使えます。

たとえば商品名の先頭に付いた「旧品」という文字列を削除する場合は、次の数式を入力します。

これにより、A2セル内にある「旧品」が空文字列へ置き換わります。

特定の文字列が複数回含まれている場合、SUBSTITUTE関数は原則としてすべて置き換える仕様です。

一部だけを削除したいときは、後述する置換対象の回数指定や、LEFT関数、RIGHT関数、REPLACE関数を検討します。

 

SUBSTITUTE関数による複数文字の削除

続いては、SUBSTITUTE関数の構文と、複数の文字を削除するときの組み立て方を確認していきます。

A列 入力値	目的
03-1234-5678	ハイフンを削除
〒100-0001	郵便記号とハイフンを削除

 

SUBSTITUTE関数の基本構文

SUBSTITUTE関数の構文は、SUBSTITUTE 関数内の対象文字列、検索文字列、置換文字列、必要に応じて置換対象の回数という順番です。

数式内では、対象となるセルを最初に指定し、次に消したい文字、最後に空文字列を指定します。

この数式では、A2セル内のハイフンをすべて削除できます。

削除を表す空文字列は、ダブルクォーテーションを連続して入力することが重要です。

置換後の文字列に何も入力しないのではなく、必ず空の文字列として指定しましょう。

ハイフンを別の記号へ変更したい場合は、空文字列の代わりに置換後の記号を入力します。

たとえばハイフンをスラッシュにしたい場合は、=SUBSTITUTE(A2,”-“,”/”) と入力します。

 

削除対象を増やす関数の入れ子

複数の不要文字を削除するには、SUBSTITUTE関数の外側へ新しいSUBSTITUTE関数を追加します。

たとえば、郵便番号から郵便記号とハイフンを削除する数式は次のとおりです。

内側の関数で郵便記号を削除し、その処理結果を外側の関数で受け取ってハイフンを削除します。

関数を閉じる括弧の数は、SUBSTITUTE関数の数と一致させる必要があります。

数式が長くなった場合は、数式バーをクリックして、対応する括弧が選択状態になることを確認すると入力ミスを見つけやすくなります。

セルに結果ではなく数式そのものが表示される場合は、先頭にアポストロフィが付いていないか、セルの表示形式が文字列になっていないかを確認してください。

 

指定回数による文字列の削除

SUBSTITUTE関数の4番目の引数を使うと、同じ文字列が複数回ある場合に、特定の回数だけ置き換えられます。

たとえば、A2セルの2つ目のハイフンだけを削除したい場合は、次のように入力します。

この数式では、2回目に出現したハイフンだけが空文字列に置き換わります。

電話番号の区切りを一部だけ変更する場合や、日付風の文字列の一部を調整する場合に役立つでしょう。

 

検索と置換を使った一括削除

続いては、数式を使わずに検索と置換で文字を削除する方法を確認していきます。

操作対象	検索する文字列	置換後
A2からA10	–	空欄

 

置換ダイアログの表示

検索と置換は、選択した範囲の文字をまとめて修正したいときに便利な機能です。

対象となるセル範囲を選択してから、CtrlキーとHキーを同時に押すと、検索と置換のダイアログボックスが表示されます。

置換タブで検索する文字列に削除したい文字を入力し、置換後の文字列は空欄のままにします。

置換後の文字列を空欄にすることで、検索した文字を削除できる仕組みです。

置換ボタンを押すと一件ずつ確認でき、すべて置換を押すと選択範囲内の対象をまとめて削除できます。

 

Excel画面を模した操作イメージ

次のイメージは、A列の電話番号からハイフンを削除する操作例です。

選択範囲を限定してから置換を実行すれば、表全体ではなく必要な列だけを安全に修正できます。

置換前には、元データを別シートへコピーするか、ファイルを保存しておくと安心です。

 

すべて置換を実行する前の確認

すべて置換は便利ですが、対象範囲を誤ると意図しないセルまで変更される可能性があります。

たとえばハイフンを削除すると、電話番号だけでなく、日付風の文字列や商品名に含まれたハイフンまで消えるかもしれません。

検索対象を絞り込むため、最初に対象列または対象範囲を選択することが大切です。

不安な場合は、最初に置換ボタンで数件だけ確認し、結果に問題がないことを確かめてからすべて置換を実行しましょう。

 

関数と置換の使い分け

続いては、SUBSTITUTE関数と検索と置換をどのように使い分けるかを確認していきます。

状況	適した方法
元データを残したい	SUBSTITUTE関数
一度だけ一括修正したい	検索と置換

 

元のデータを保持したい場合

取引先から受け取ったデータや、後から照合が必要になるデータでは、元の値を残しておくことが重要です。

このような場合は、元データがあるA列を変更せず、B列にSUBSTITUTE関数を入力して削除後の文字列を表示します。

関数による加工は元データを壊さず、修正内容を見比べられることが利点です。

後から削除対象を追加したくなった場合も、数式を修正して再計算するだけで対応できます。

 

データを直接整形したい場合

インポートしたデータを一度だけ整形し、そのまま別のシステムへ取り込む場合は、検索と置換のほうが素早く完了します。

数式列を作る必要がないため、表の構成を変えずに不要文字を削除できます。

ただし、置換後は元の文字列へ簡単に戻せないため、操作直後に問題があればCtrlキーとZキーで元に戻しましょう。

保存して閉じた後でも戻せるように、作業前のバックアップを取る習慣が必要です。

 

数式結果を値へ変換する手順

SUBSTITUTE関数で加工した結果を、最終的に値として確定したい場合があります。

この場合は、結果が入力された列をコピーし、同じ場所または別の場所で値として貼り付けを実行します。

貼り付けのオプションから値を選ぶと、数式ではなく表示結果だけを残せます。

値貼り付け後は数式へ戻せないため、元の数式列を残してから作業すると安全です。

 

削除できない文字とエラーの対処

続いては、数式や置換を実行しても文字が消えない場合の確認ポイントを解説していきます。

症状	主な原因
スペースが残る	全角と半角が異なる
見えない空白が残る	改行や特殊な空白文字

 

全角と半角の違い

画面上では同じように見えても、全角ハイフン、半角ハイフン、長音記号、マイナス記号は別の文字です。

SUBSTITUTE関数で半角ハイフンを指定しても、全角の記号は削除されません。

削除できないときは、実際にセル内で使われている文字をコピーして数式へ貼り付けると確実です。

検索と置換でも同様に、対象セルから直接コピーした文字を検索する文字列へ貼り付ける方法が有効です。

 

余分なスペースとCLEAN関数

Webページや外部システムからコピーしたデータには、通常のスペース以外に改行コードや印刷できない文字が含まれることがあります。

このような場合は、CLEAN関数やTRIM関数をSUBSTITUTE関数と組み合わせると整理しやすくなります。

この例では全角スペースを半角スペースへ置き換え、印刷できない文字を取り除き、連続する余分なスペースを整理します。

ただし、TRIM関数は単語間に必要な半角スペースを一つ残すため、すべての空白を完全に削除したい目的とは異なる場合があります。

 

数式エラーの確認

数式でエラーが表示される場合は、ダブルクォーテーションや括弧の入力漏れを確認します。

特にSUBSTITUTE関数を何重にもすると、最後の閉じ括弧が不足しやすくなります。

数式内で区切り記号が正しく入力されているか、参照セルが存在するかも確認しましょう。

関数の結果が想定と異なるときは、長い数式を一度に作るのではなく、一つずつSUBSTITUTE関数を追加して途中結果を確認する方法が有効です。

 

まとめ エクセルで特定の文字を複数削除する方法

エクセルで特定の文字を複数削除する場合、元データを保持しながら処理したいならSUBSTITUTE関数、直接データを整形したいなら検索と置換が便利です。

SUBSTITUTE関数では、置換後の文字列に空文字列を指定し、関数を重ねることでハイフン、括弧、スペースなどをまとめて削除できます。

削除対象が複数ある場合は、文字ごとにSUBSTITUTE関数を追加することが基本です。

検索と置換では、置換後の文字列を空欄にして実行すると、選択範囲内の不要文字を一括で削除できます。

ただし、置換は元データを直接変更するため、範囲を限定し、作業前にバックアップを残すことが大切です。

全角と半角の違い、目に見えないスペース、特殊文字にも注意しながら、データの用途に合った方法を選びましょう。