エクセルで文字データを扱っているときに、「特定の位置にある文字や数字を別の文字や数値に変換したい」と思ったことはないでしょうか。すべてを手作業で置換すると何十、何百というセルの内容を変更することになるので、膨大な手間がかかります。そんなことをしなくても済む解決方法の１つに、エクセルのREPLACE関数を利用するという方法があります。

REPLACE関数とは何か？

REPLACE関数はそもそも、英語の「Replace（意味：代替する、代える）」に由来しています。

エクセルでは「＝REPLACE (文字列, 開始位置, 文字数, 置換文字列)」という形で使い、指定した「文字列」の中で「開始位置」から「文字数」分の文字を「置換文字列」に置換してくれます。

REPLACE関数の書式＝REPLACE (文字列, 開始位置, 文字数, 置換文字列)
対応Version365、2019、2016、2013、2010

REPLACE関数の「文字列」について

「文字列」には、置換の対象となる文字列を指定します。

REPLACE関数の「開始位置」について

「開始位置」には、置換を開始する位置を数値（1より大きい）で指定します。「文字列」の左端から指定した数値分だけ文字を数え、該当する文字が置換のスタートとなります。

もし「開始位置」に「文字列」の総文字数より大きい値を指定した場合は、末尾に「置換文字列」を挿入した結果が表示されます。

REPLACE関数の「文字数」について

「文字数」には、「開始位置」から置換したい文字数を値で指定します。３と指定すると、開始位置から３文字が置換の対象となります。

「文字数」に0を指定した場合は「開始位置」の文字の手前に「置換文字列」を挿入した結果が表示されます。

また、「開始位置」と「文字数」の合計が「文字数」の総文字数を超える場合は、「開始位置」から末尾までの文字列が「置換文字列」に置換された結果が表示されます。

REPLACE関数の「置換文字列」について

「置換文字列」には、置換した結果の文字列を指定します。

＜変換の例＞
＝REPLACE (“ああああ”, 2,2, ”い”)　→「あいいあ」

REPLACE関数の具体的な使い方

①REPLACE関数を使える状況を準備します。

ここでは新学期に備えて学年を１つ繰り上げたい状況を例に取り上げています。学年なので、1年は2年、2年は3年のように学年が1つ増える置換になります。

②REPLACE関数を使いたいセルに「=REPLACE (」と入力します。

③「文字列」を指定します。

④「開始位置」を指定します。

学年部分なので開始位置は１になります。

⑤「文字数」を指定します。

学年は１桁の数字なので、文字数は１になります。

⑤「置換文字列」を指定します。

置換文字列は文字列のセルに１を加えた数字になります。

⑥「Enter」を押し、表示された結果を確かめます。

１年１組が２年１組になっているので、期待どおりの結果です。

⑦他のセルにもREPLACE関数を反映させます。

コピペやオートフィルを利用して、他のセルにREPLACE関数を反映させましょう。

REPLACE関数がよく使われる状況

REPLACE関数は規則性のある文字データで置換を行う際によく使われます。規則性がある文字データの例をあげると、

  • 住所（[都道府県]・[市区町村郡]・[地番等]）
  • 電話番号（市外局番、ハイフン）
  • 日付（年/月/日）

などが挙げられます。それぞれどのようにREPLACE関数を使えば良いのか、簡単な例を用いて説明しておきます。なお、FIND関数との組み合わせている例もあります。FIND関数について知りたい方はこちらを参照してください。

住所から県名を削除する

県名には○○県（2文字）のパターンと〇〇○県（3文字）のパターンがあります。なので、単純にREPLACE(文字列,1,3,””）としても、〇〇○県（3文字）のパターンではうまく機能しません。

そこでFIND関数と組み合わせて、”県”の位置を特定した上で置換するようにします。=REPLACE(文字列,1,FIND(“県”,文字列),””)とすることで、○○県（2文字）のパターンと〇〇○県（3文字）のパターンの両方に対応することができます。

電話番号（ハイフン有り）で市外局番を削除する

電話番号の市外局番には2桁～5桁のものがあるので、県名を削除する例と同様に、FIND関数で”-（ハイフン）”の位置を特定した上で置換します。=REPLACE(文字列,1,FIND(“-“,文字列),””)とすることで、すべての市外局番を削除することができます。

ただし、=REPLACE(文字列,1,FIND(“-“,文字列),””)はハイフンが無い電話番号で市外局番を除くことはできないので注意してください。

日付から年を削除する

エクセルで日付を扱う時に注意点は、セル表示では「2020/11/24」となっていてもエクセルの裏側では「44159（シリアル値）」として管理されている点です。そのため、単純に「2020/11/24」→「11/24」という2020年を削除（置換）したいからといって、REPLACE(セル参照,1,5,””)としてしまうと、結果は空白になります。「44159」というちょうど5桁の数字が削除されるからです。

そこで、日付を文字列（表示形式：yyyy/mm/dd）に変換してから置換するという方法を取ります。日付を文字列に変換するにはTEXT関数を使い、TEXT(セル参照,”yyyy/mm/dd”）と引数を設定します。その上でREPLACE関数を、REPLACE(TEXT(セル参照,”yyyy/mm/dd”）,1,5,””)というふうに使います。こうすることで、年を削除した「mm/dd」という結果を表示することができます。

似た機能を持つSUBSTITUTE関数

REPLACE関数に似た機能を持つ関数にSUBSTITUTE関数があります。SUBSTITUTE関数も置換に関する関数ですが、引数の設定が若干REPLACE関数と異なります。SUBSTITUTE関数について詳しく知りたい方はこちらを参照してください。