1. ホーム
  2. ブログ
  3. SUBSTITUTE関数の使い方|特定の文字だけを置き換える

SUBSTITUTE関数の使い方|特定の文字だけを置き換える

#

この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。

片方は 086-123-4567、もう片方は 0861234567。人には同じ番号でも、Excelには別物です。

SUBSTITUTE(サブスティチュート) は、文字の中の特定の文字だけを別の文字に置き換える関数です。

使うデータ

電話番号の表記がばらばらな顧客リスト。半角ハイフン、ハイフンなし、全角ハイフン、スペース区切り
C社は全角のハイフン、D社はスペース区切り。他のシステムから出したデータでよく起きる。

SUBSTITUTEの形

=SUBSTITUTE(文字列, 検索文字列, 置換文字列)
  1. 「どのセルの」「何を」「何に」。消すときは "" (空の文字)に置き換える

ハイフンを消す

C2に =SUBSTITUTE(B2,"-","") を入れて下までコピー。A社は直ったが、C社とD社はそのままの画面
=SUBSTITUTE(B2,"-","")。A社は直った。もともとハイフンが無いB社はそのまま通る(エラーにならない)。C社とD社は直らない。

C社が直らないのは、ハイフンが全角(-)だからです。SUBSTITUTEは全角と半角を別の文字として扱います。

!

「見た目が同じ横棒」は何種類もある

-(半角)/-(全角)/ー(長音)/—(ダッシュ)。画面では判別できない。Ctrl+H を開いて半角の - で「すべて検索」し、ヒットしなければ半角ハイフンではない。

複数の種類をまとめて直す

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"-",""),"-","")," ","") で4件とも数字だけの同じ形になった画面
内側から順に、半角ハイフン → 全角ハイフン → スペースを消す。4件とも同じ形になった。これで別の表と突き合わせられる。
!

3つより増えるなら、列を分ける

4つ5つと重ねると後から読めない。「半角ハイフンを消す列」「全角を消す列」と1段ずつ分ける。

置換(Ctrl+H)との使い分け

Excelのホームタブ右端の「検索と選択」ボタンを赤い枠で示した画面
同じことは「ホーム」→「検索と選択」→「置換」(Ctrl+H)でもできる。違いは元のデータが残るかどうか。
置換(Ctrl+H)SUBSTITUTE
もとのデータ書き換わるそのまま残る
新しいデータを貼ったときもう一度手で実行自動でやり直される
向いている場面その場限り毎月くり返す作業
×

もとのデータを直接書き換えない

他のシステムから出力したデータは、証拠として元のまま残す。元の列は触らず、隣に整形用の列を足す。

整形した結果で計算できないとき

「1,200円」から円とカンマを消しただけでは文字のままなので、=VALUE(SUBSTITUTE(…)) で数値にした画面
SUBSTITUTEの結果は必ず文字。数字として使うなら VALUE(…) で囲む。
×

スペースが消えない

スペースにも全角( )と半角( )がある。両方消すなら2回重ねる。前後のスペースだけなら TRIM関数 のほうが簡単。

!

「株式会社」の表記ゆれは、消す前に考える

(株) 株式会社 ㈱ を全部消すと元に戻せない。正式名称は別の列で管理し、照合用に消した列を正式名称として使わない。

次に読むとよい記事


「毎月、貼り付けたデータの表記を手で直してから作業を始めている」という状態は、整形用の列を作るだけでなくなります。現状のヒアリングと課題整理は無料です。

#Excel#SUBSTITUTE#データ整理#表記ゆれ

この記事について

SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。

無料で相談してみる

あわせて読みたい

記事一覧へ戻る