Aさん 住所の「市区町村」以降など、特定データの途中から文字を抽出したいですが、どう作業したら効率的ですかね？
森田 その場合は、関数の「MID」が王道ですが、ケースによっては関数の「REPLACE」の方が抽出しやすい場合がありますよ！
では、REPLACEの使い方について解説していきますね。
はじめに
この記事は関数の概要とMIDの使い方を把握していることが前提です。
関数の概要とMIDの使い方の詳細は以下の記事をご参照ください。
はじめに この記事は数式の概要を把握していることが前提です。 参考記事 数式の概要の詳細は以下の記事をご参照ください。 関数とは 関数とは、数式の構成要素の1つであり、固有の計算/処理の機能がセットされた数式のことです。 …
はじめに この記事は関数の概要を把握していることが前提です。 …
元データの文字列から任意の部分のみをブランク（空白）へ置換したい場合は「REPLACE」が有効
実務では、既存のデータと使いたいデータで粒度が合っていないケースがあります。
たとえば、住所を「都道府県」と「市区町村+番地」に分けて管理したいといったイメージです。
この解決策として、既存データの文字列から必要な部分だけ抽出した列を用意することが必要です（例：住所から「市区町村+番地」のみ抽出）。
ここで言う抽出のアプローチは2通りあります。
- 必要な部分のみを抽出する
- 不要な部分のみを削除する
アプローチ①はMIDなどを活用したデータ抽出です。
もう一方のアプローチ②は、不要な部分をブランク（空白）へ置換するイメージです。
この不要な部分をブランク（空白）へ置換するには、関数の「REPLACE」を使うと良いです。
ちなみに、REPLACEは「リプレイス」と呼びます。
「REPLACE」を使うことで、対象データの任意の位置から指定した数の文字列を新しい文字への置換を自動化できます。
置換を自動化する関数は他に「SUBSTITUTE」があります。
こちらは「文字列」を対象に置換を行います（詳細は以下の記事を参照）。
はじめに この記事は関数の概要を把握していることが前提です。 参考記事 関数の概要については以下の記事をご参照 …
REPLACEの構文
REPLACEの構文は以下の通りです。
=REPLACE(文字列,開始位置,文字数,置換文字列)
文字列中の指定した位置の文字列を置き換えた結果を返します。半角と全角の区別なく、1文字を1として処理します。
| 引数名 | 必須 | データ型 | 説明 |
|---|---|---|---|
| 文字列 | ○ | 文字列 | 置換対象のセルを指定します。 |
| 開始位置 | ○ | 数値 | 「文字列」の何文字目から置換するか指定します。 ※「文字列」の先頭文字は「1」 |
| 文字数 | ○ | 数値 | 「開始位置」以降で置換する文字数を指定します。 |
| 置換文字列 | ○ | 文字列 | 「開始位置」と「文字数」の指定内容を置き換える新しい文字を指定します。 ※置換後の文字 |
引数「開始位置」へ引数「文字列」の文字数を超える数値を指定した場合、REPLACEの戻り値は引数「文字列」のままとなります。
引数「開始位置」は「1」以上の数値を指定する必要があります（「1」未満の場合はエラー値「#VALUE!」が表示）。
引数「文字数」は「0」以上の数値を指定する必要があります（負の数はエラー値「#VALUE!」が表示）。
【参考】REPLACEは「文字列操作関数」
あくまで参考情報となりますが、REPLACEがリボン「数式」タブの関数ライブラリの「文字列操作」に分類されています。
実際にREPLACEを活用する際は、以下で解説しているように直接入力で挿入していきましょう。
REPLACEの使用結果イメージ
REPLACEで対象データの任意の位置から指定した数の文字列を置換したイメージは以下の通りです。
今回は「住所」列のデータの先頭3文字をブランクへ置換し、「市区町村+番地」の文字列を抽出しました。
なお、REPLACEは原則一つの関数で一つのセルのみが置換対象です。
ベースの数式をセットしたら、他のセルへペーストしましょう。
REPLACEの数式の挿入手順
上記の結果を得るための手順は以下の通りです。
- 関数を挿入するセルを選択
※今回はD2セル - 「=rep」と入力
- サジェストから「REPLACE」を選択し、「Tab」キーで確定
- 置換対象のセルを選択
※今回はC2セル - コンマ（,）を入力
- 任意の開始位置を入力
※今回は「1」 - コンマ（,）を入力
- 任意の文字数を入力
※今回は「3」 - コンマ（,）を入力
- 置換後の文字を入力
※今回は「””」 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はD3~D11セルへペースト
手順②の際にIMEを半角英数モードにすること。
テーブルの場合、手順⑫は不要（全レコードへ数式が自動的にコピーされる）。
【応用】対象データによって引数「開始位置」・「文字数」を可変にしたい場合は
REPLACEの引数「開始位置」・「文字数」は固定値（定数）を指定することが基本です。
本記事では、住所から「都道府県」部分をブランクへ置換するにあたり、「1」と「3」をそれぞれ指定しました。
しかし、元データの内容や置換したいデータによって固定値だと困る場合があります。
たとえば、住所だと次のようなケースですね。
- 4文字の都道府県（神奈川県や和歌山県など）もデータに混在している
- 市区町村の部分のみ置換したい
こうした場合は固定値ではなく、FINDなどの関数をネストし、目印となる文字を基準に引数「開始位置」・「文字数」のいずれか、または両方を可変にすると良いです。
イメージとしては、以下の通りです。
FINDの詳細は以下の記事をご参照ください。
はじめに この記事は関数の概要とLEFT・RIGHT・MIDの使い方を把握していることが前提です。
【参考】半角と全角を区別して置換したい場合は「REPLACEB」
REPLACEで行う置換は、1文字は半角（1バイト）と全角（2バイト）は区別されず、同じく「1」として処理します。
もし、半角と全角を区別して置換したい場合は「REPLACEB」（リプレイスビー）を使いましょう。
=REPLACEB(文字列,開始位置,バイト数,置換文字列)
文字列中の指定された文字数の文字を別の文字に置き換えます。半角文字は1バイト、全角文字は2バイトとなります。
この関数の「1」は1バイトを示し、バイト数を基準に置換することが可能です。
1文字が1バイトに変わるだけで、使い方自体はREPLACEと同様です。
サンプルファイルで練習しよう！
可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。
サンプルファイル_ワークシート関数_REPLACE.xlsx
※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
（上記リンクから登録フォームへ遷移します）
ブックを開いたら、次の手順を実施してください。（今までの解説のまとめです）
- 関数を挿入するセルを選択
※今回はD2セル - 「=rep」と入力
- サジェストから「REPLACE」を選択し、「Tab」キーで確定
- 置換対象のセルを選択
※今回はC2セル - コンマ（,）を入力
- 任意の開始位置を入力
※今回は「1」 - コンマ（,）を入力
- 任意の文字数を入力
※今回は「3」 - コンマ（,）を入力
- 置換後の文字を入力
※今回は「””」 - 「Enter」キーで確定
- 1行目の数式をコピーし、以降のセルへペースト
※今回はD3~D11セルへペースト
本記事の解説と同じ結果になればOKです！
さいごに
いかがでしたでしょうか？
REPLACEはデータの抽出や分割に役立つ関数の一つです。
定期的にデータ抽出/分割の作業を行う機会があるなら、ぜひ覚えておいた方が良いですね。
なお、REPLACE以外にもExcelでのデータ整形の各種テクニックを拙著で解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 REPLACEとセットで覚えておいた方が良い関数は、必要な部分を抽出するLEFT、RIGHT、MIDです。
必要な部分を抽出するのと、不要な部分を削除するので、どちらが楽かで使い分けると良いでしょう。
その他、臨機応変に置換したい場合はFINDもセットで使えるとベターですね。
