この記事は 事務でよく使うExcel関数まとめ|目的から探せる一覧 の1本です。他の関数もまとめて見られます。
片方は 086-123-4567、もう片方は 0861234567。人には同じ番号でも、Excelには別物です。
SUBSTITUTE(サブスティチュート) は、文字の中の特定の文字だけを別の文字に置き換える関数です。
使うデータ

SUBSTITUTEの形
- 「どのセルの」「何を」「何に」。消すときは "" (空の文字)に置き換える
ハイフンを消す

=SUBSTITUTE(B2,"-","")。A社は直った。もともとハイフンが無いB社はそのまま通る(エラーにならない)。C社とD社は直らない。C社が直らないのは、ハイフンが全角(-)だからです。SUBSTITUTEは全角と半角を別の文字として扱います。
「見た目が同じ横棒」は何種類もある
-(半角)/-(全角)/ー(長音)/—(ダッシュ)。画面では判別できない。Ctrl+H を開いて半角の - で「すべて検索」し、ヒットしなければ半角ハイフンではない。
複数の種類をまとめて直す

3つより増えるなら、列を分ける
4つ5つと重ねると後から読めない。「半角ハイフンを消す列」「全角を消す列」と1段ずつ分ける。
置換(Ctrl+H)との使い分け

| 置換(Ctrl+H) | SUBSTITUTE | |
|---|---|---|
| もとのデータ | 書き換わる | そのまま残る |
| 新しいデータを貼ったとき | もう一度手で実行 | 自動でやり直される |
| 向いている場面 | その場限り | 毎月くり返す作業 |
もとのデータを直接書き換えない
他のシステムから出力したデータは、証拠として元のまま残す。元の列は触らず、隣に整形用の列を足す。
整形した結果で計算できないとき

VALUE(…) で囲む。スペースが消えない
スペースにも全角( )と半角( )がある。両方消すなら2回重ねる。前後のスペースだけなら TRIM関数 のほうが簡単。
「株式会社」の表記ゆれは、消す前に考える
(株) 株式会社 ㈱ を全部消すと元に戻せない。正式名称は別の列で管理し、照合用に消した列を正式名称として使わない。
次に読むとよい記事
- 前後の余分なスペースだけを取る → TRIM関数の使い方
- 整形した列で別の表から値を持ってくる → XLOOKUP関数の使い方
- 文字の一部を取り出す → LEFT・RIGHT・MID関数の使い方
「毎月、貼り付けたデータの表記を手で直してから作業を始めている」という状態は、整形用の列を作るだけでなくなります。現状のヒアリングと課題整理は無料です。
この記事について
SUPRAXは、林業・製造・運送の現場経験をもとに、中小企業の業務改善・AI活用・GISの支援を行っています。 記事の内容について「うちの場合はどうか」を聞きたい方は、無料相談をご利用ください。
あわせて読みたい
TRIM関数の使い方|合計が合わない原因のスペースを消す
条件を正しく書いたのに合計が0になる。検索しても見つからない。 原因で一番多いのが、画面では見えない余分なスペースです。 これを取り除くのが TRIM(…
XLOOKUP関数の使い方|品番から品名と単価を自動で出す
伝票を作るとき、品番を見ながら品名と単価をマスタから探して手で打っていませんか。 XLOOKUP(エックスルックアップ)を使うと、品番を入れた瞬間に品名…
SUMPRODUCT関数の使い方|単価×数量の合計を1つの式で出す(条件付き・件数・SUMIFSとの使い分けまで、本物のExcel画面で)
月初、先月の売上一覧を開いて「合計いくら?」と聞かれる。表には単価と数量はあるのに、金額の列がない。 いつものやり方:金額の列を足して、=D2E2 を下…