前回に続いて、間違えやすい Excel関数「SUBSTITUTE」と「REPLACE」について見ていきます。
いずれも文字列を別の文字列に置き換える関数です。
「SUBSTITUTE」関数の書式は、次の通りです：
SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
「REPLACE」関数の書式は、次の通りです：
REPLACE(文字列, 開始位置, 文字数, 置換文字列)
SUBSTITUTE関数は、文字列自体を指定して置き換えるのに対し、REPLACE関数は、文字の位置と文字数で置き換えるという違いがあります。
以下に簡単な例をそれぞれ示します。
(1) SUBSTITUTE関数
下図において、B2セルには「ウィンドウズ 11」と入力されています。
C2セルに「=SUBSTITUTE(B2,"ウィンドウズ","Windows")」と入力すると「Windows 11」と返されます。
「ウィンドウズ」という文字列を「Windows」という文字列に置き換えました。
そして、C4セルに「=SUBSTITUTE(C2,"1","2",2)」と入力すると「Windows 12」と返されます。
C2セルの「Windows 11」文字列のうち第4引数で指定した「2」番めに現れた「1」を「2」に置き換えました。
よく使われる例としては特定の文字列を“削除”する使い方があります。
下図において、B2セルには“前株”で社名が、B3セルには“後株”で社名が入力されています。
C2セルに「=SUBSTITUTE(B2,"株式会社","")」、C3セルに「=SUBSTITUTE(B3,"株式会社","")」と入力するといずれも「株式会社」という文字列が削除されて返されます。
(2) REPLACE関数
上記のように SUBSTITUTE関数は単純な文字列の置き換えには便利ですが、REPLACE関数は検索文字を指定する代わりに開始位置からの文字数分の文字列を置換文字列に置き換えるときに使います。
最初の例を REPLACE関数でやってみると、C5セルに「=REPLACE(C2,10,1,"2")」と入力すると「Windows 12」と返されます。（下図）
C2セルの「Windows 11」文字列の「10」文字めからの「1」文字を「2」に置き換えています。
単純な文字列の置換なら SUBSTITUTE関数のほうが分かりやすいですね。
この例で、REPLACE関数が分かりにくいという印象を与えるのが第2引数「10」と第3引数「1」、つまり開始位置、文字数を数えて与えなくてはいけないところでしょう。
2番めの例を REPLACE関数でやると、D2セルに「=REPLACE(B2,1,4,"")」とし、B2セルの文字列の「1」番めから「4」文字分の文字列を空白に置き換える、つまり削除します。（下図）
先頭から数えるならまだ楽なのですが、D3セルでは「=REPLACE(B3,5,4,"")」としなければなりません。
B3セルの文字列の「5」文字めから「4」文字分文字列を空白に置き換えます。
この開始位置や文字数をいちいち数えなければいけないので、通常は FIND関数など文字列操作関数を使って操作することが多いです。
最初の例について FIND関数を組み合わせて REPLACE関数を書き直すと、C5セルには「=REPLACE(C2,FIND("1",C2,FIND("1",C2)+1),1,"2")」と入力します。（下図）
第2引数「10」の部分を「FIND("1",C2,FIND("1",C2)+1)」で置き換えたわけです。
内側の FIND関数で C2セル「Windows 11」文字列について「1」という文字列を先頭から検索し見つかった位置に「+1」して“その次”から、外側の FIND関数で再度 C2セル「Windows 11」文字列について「1」という文字列を検索しています。
つまり、2番めに見つけられた「1」の位置「10」を求めています。
2番めの例では、D2セルに「=REPLACE(B2,FIND("株式会社",B2),LEN("株式会社"),"")」とし、D3セルに「=REPLACE(B3,FIND("株式会社",B3),LEN("株式会社"),"")」としています。（下図）
この例では「株式会社」という文字列が“前株”であろうと“後株”であろうとその出現位置を FIND関数で求め、「株式会社」の文字数を LEN関数で求めています。
なお、REPLACE関数の第3引数「文字数」は、漢字などでも半角英数文字でもそのまま文字数ですが、これをバイト単位で指定する「REPLACEB」関数も提供されています。
また、Microsoftのサポートページ「SUBSTITUTE 関数」「REPLACE 関数、REPLACEB 関数」も併せてご参照ください。