【関数】対象データの任意の位置から指定した数の文字列を新しい文字へ置換できる「REPLACE」の使い方

Aさん

住所の「市区町村」以降など、特定データの途中から文字を抽出したいですが、どう作業したら効率的ですかね？

森田

その場合は、関数の「MID」が王道ですが、ケースによっては関数の「REPLACE」の方が抽出しやすい場合がありますよ！
では、REPLACEの使い方について解説していきますね。

はじめに

この記事は関数の概要とMIDの使い方を把握していることが前提です。

元データの文字列から任意の部分のみをブランク（空白）へ置換したい場合は「REPLACE」が有効

実務では、既存のデータと使いたいデータで粒度が合っていないケースがあります。

たとえば、住所を「都道府県」と「市区町村+番地」に分けて管理したいといったイメージです。

この解決策として、既存データの文字列から必要な部分だけ抽出した列を用意することが必要です（例：住所から「市区町村+番地」のみ抽出）。

ここで言う抽出のアプローチは2通りあります。

アプローチ①はMIDなどを活用したデータ抽出です。

もう一方のアプローチ②は、不要な部分をブランク（空白）へ置換するイメージです。

この不要な部分をブランク（空白）へ置換するには、関数の「REPLACE」を使うと良いです。
ちなみに、REPLACEは「リプレイス」と呼びます。

「REPLACE」を使うことで、対象データの任意の位置から指定した数の文字列を新しい文字への置換を自動化できます。

REPLACEの構文

REPLACEの構文は以下の通りです。

=REPLACE(文字列,開始位置,文字数,置換文字列)
文字列中の指定した位置の文字列を置き換えた結果を返します。半角と全角の区別なく、1文字を1として処理します。

引数「開始位置」へ引数「文字列」の文字数を超える数値を指定した場合、REPLACEの戻り値は引数「文字列」のままとなります。
引数「開始位置」は「1」以上の数値を指定する必要があります（「1」未満の場合はエラー値「#VALUE!」が表示）。
引数「文字数」は「0」以上の数値を指定する必要があります（負の数はエラー値「#VALUE!」が表示）。

【参考】REPLACEは「文字列操作関数」

あくまで参考情報となりますが、REPLACEがリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。

実際にREPLACEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。

REPLACEの使用結果イメージ

REPLACEで対象データの任意の位置から指定した数の文字列を置換したイメージは以下の通りです。
今回は「住所」列のデータの先頭3文字をブランクへ置換し、「市区町村+番地」の文字列を抽出しました。

なお、REPLACEは原則一つの関数で一つのセルのみが置換対象です。

ベースの数式をセットしたら、他のセルへペーストしましょう。

REPLACEの数式の挿入手順

上記の結果を得るための手順は以下の通りです。

手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑫は不要（全レコードへ数式が自動的にコピーされる）。

【応用】対象データによって引数「開始位置」・「文字数」を可変にしたい場合は

REPLACEの引数「開始位置」・「文字数」は固定値（定数）を指定することが基本です。

本記事では、住所から「都道府県」部分をブランクへ置換するにあたり、「1」と「3」をそれぞれ指定しました。

しかし、元データの内容や置換したいデータによって固定値だと困る場合があります。

たとえば、住所だと次のようなケースですね。

こうした場合は固定値ではなく、FINDなどの関数をネストし、目印となる文字を基準に引数「開始位置」・「文字数」のいずれか、または両方を可変にすると良いです。

イメージとしては、以下の通りです。

【参考】半角と全角を区別して置換したい場合は「REPLACEB」

REPLACEで行う置換は、1文字は半角（1バイト）と全角（2バイト）は区別されず、同じく「1」として処理します。

もし、半角と全角を区別して置換したい場合は「REPLACEB」（リプレイスビー）を使いましょう。

=REPLACEB(文字列,開始位置,バイト数,置換文字列)
文字列中の指定された文字数の文字を別の文字に置き換えます。半角文字は1バイト、全角文字は2バイトとなります。

この関数の「1」は1バイトを示し、バイト数を基準に置換することが可能です。

1文字が1バイトに変わるだけで、使い方自体はREPLACEと同様です。

サンプルファイルで練習しよう！

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ワークシート関数_REPLACE.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）

ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）

本記事の解説と同じ結果になればOKです！

さいごに

いかがでしたでしょうか？

REPLACEはデータの抽出や分割に役立つ関数の一つです。

定期的にデータ抽出/分割の作業を行う機会があるなら、ぜひ覚えておいた方が良いですね。

なお、REPLACE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。

Excel関数をフルに使って データの整形・集計・可視化の自動化を極める本

created by Rinker

ソシム

¥2,640 (2024/11/21 17:21:39時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

パワークエリも関数もぜんぶ使う！Excelでできるデータの収集・整形・加工を極めるための本

created by Rinker

¥2,399 (2024/11/21 17:10:38時点 Amazon調べ-詳細)

• Kindle
• Amazon
• 楽天市場
• Yahooショッピング

また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！

ご参考になれば幸いですm(_ _)m

森田

REPLACEとセットで覚えておいた方が良い関数は、必要な部分を抽出するLEFT、RIGHT、MIDです。
必要な部分を抽出するのと、不要な部分を削除するので、どちらが楽かで使い分けると良いでしょう。
その他、臨機応変に置換したい場合はFINDもセットで使えるとベターですね。