文字列内の指定の文字列を置換する
SUBSTITUTE関数
=SUBSTITUTE(対象文字列, 置換指定文字列, 置換後文字列[,置換対象])
- 対象文字列…置換の対象となる文字列
- 置換指定文字列…対象文字列の中で置換したい文字列
- 置換後文字列…置換後の文字列
- 置換対象…何番目の「置換指定文字列」を置換するかを指定出来る。省略可（省略した場合は、全ての置換してい文字列が置換される）
スプレッドシートで文字列の置換は、このSUBSTITUTE関数とREPLACE関数が用意されていますが、 SUBSTITUTE関数は置換対象を文字列自体で指定します。
置換したい対象の文字が決まっていないが、場所は特定できる場合などには、REPLACE関数を使うことで、文字列の場所を指定して置換することが出来ます。
サンプル
やってみましょう。
A1セルに「ひらちんの部屋」と入力されているシートを用意しました。B1セルにSUBSTITUTE関数を使って、文字列を置換えた文字を表示させます。
まずは基本形から。”ひらちん”を”たろう”に置換えます。
B1セルに次の数式を入力します。
=SUBSTITUTE(A1, “ひらちん”, “たろう”)
Enterで確定します。
対象文字列はA1セルにあるので、セル参照で指定しています。
置換指定文字列は、”ひらちん”を置換えたいので”ひらちん”です。
置換後文字列は、”たろう”に変えたいので、”たろう”です。
置換対象の引数は省略します。
B2セルには、”たろうの部屋”と表示されました。
指定の文字列を削除する
置換後文字列を「””」（何も無いという意味）で指定することで、指定の文字列を削除することが出来ます。
サンプル
「-」で繋がれたアルファベットと数字の文字列です。
後ろの数字だけ使いたい場合など、アルファベットの部分を消したいこととかありますよね。
同じ文字列を消す場合は、SUBSTITUTE関数でサクッと消せます。
対象文字列（A1）の、”ABC-”を「””」に置き換えるで、出来ますね。
B2セルに次の数式を入力します。
=SUBSTITUTE(A1, “ABC-“, “”)
ハイフンまで消したいので、ABCの後に「-」入れるのを忘れないように）
Enterで確定すると
数字部分だけ取り出せましたね
B2セルにも数式をコピーします。
同じように数字だけ取り出すことが出来ました。
このように、削除したい文字列が決まっている場合は、SUBSTITUTE関数が便利です。
削除したい文字列が決まっていない場合は、 REPLACE関数 が使えるかもしれないのでそちらも参照して下さい。
2番めに現れる置換文字列だけ置換する
最後に、省略可能な引数「置換対象」についても触れておきます。
繰り返し同じ文字列が出てくる場合など、何番目に出てくるものを置換対象にするかを指定することが出来ます。
サンプル
処理している私達としては、発狂してしまいそうな文字列ですが、何らかのコードが更に全体を()でくくってあるみたいなものってありますよね？
※全体カッコで括るな！とか、おんなじ記号同じ文字列に使うんじゃねぇ！とかの話は置いといたとして
この場合、外側の「()」だけを取り除いて、「ABC-1234(a)」みたいにしたいので、
①「(」を取り除く
②「)」を取り除く
と2段階に分けるとしましょう。
置換対象の引数を、指定しないで①をやろうとすると
=SUBSTITUTE(A1, “(“, “”)
となります。
Enterで確定すると
ぎゃ！
当たり前ですが、aの前の「(」まで消えてしまいました。
というのを回避するために、出現する1番目だけ置換対象にするっていうのが、この引数の役目です。
置換対象の引数に’１’を指定して次のように書き換えます。
=SUBSTITUTE(A1, “(“, “”, 1)
Enterで確定します。
出来ました(^o^)
同じように、C1セルに「)」のカッコを取り除く数式を入れましょう。
今回は、2番目に出てくるものを置換対象にするので、置換対象は’２’になるので注意してくださいね。
=SUBSTITUTE(B1, ”)”, “”, 2)
※置換対象もB1に変更するので注意して下さい。
Enterで確定します。
上手く出来ましたね。
それでは、B2セルC2セルにも数式をコピーしましょう。
完璧ですね(^o^)
２行も使って、なんか汚くてう嫌だな～っていう向上心の高い方は、２つの数式を組み合わせてしまいましょう！
B1セルに入力するのは次の数式です。
=SUBSTITUTE(SUBSTITUTE(A1, “(“, “”, 1), “)”, “”, 2)
「(」のカッコを取り除いたものを、「)」のカッコを取り除く、対象文字列に指定しています。
Enterで確定して、B2セルにもコピーします。
シンプルに出来ましたね(^o^)
まとめ
文字列の置き換えは、スプレッドシートの機能である「検索と置換」も使えます。
ただ、この場合元の文字列を変更してしまうので、元のデータは残しときたい場合に不向きです。
置換対象の引数のように、２つ目だけとか１つ目だけのような指定も出来ませんし、データ扱う上では元のデータはなるべく変更しないことが鉄則かと思いますので、関数もしっかり勉強しましょう！
コメント