この記事を読むのに必要な時間は約 7 分です。
エクセルを使っていて思うこと。
「セルの中にある改行が邪魔！！」
誰でも１度や２度はあるはずです。
１発で取り除ける便利な方法があったらいいのに・・・
そんな便利な方法、もちろんあります！！
改行も置換できる、Excel関数「SUBSTITUTE」の使い方をご紹介します。
目次（リンク）
文字列を置換するExcel関数、SUBSTITUTEの使い方
まずはSUBSTITUTEの引数から。
引数を日本語で書くと、
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])
それぞれの引数の意味は、
- 文字列：置換したい文字が含まれるもの
- 検索文字列：置換前の文字列
- 置換文字列：置換後の文字列
- [置換対象]：検索文字列の内、一部分だけ換えたいなら指定、省略可
ちょっと分かりづらいと思うので、例を使って説明します。
今はもう秋ということで、
「もうあきだあ」という文字列でやってみます。
Ｂ２セルの中の「あ」という文字を「す」という文字に置換します。
その場合の書き方は、
=SUBSTITUTE(B2,”あ”,”す”)
ですね。
「もうあきだあ」から、「もうすきだす」に換わりました。
これで使い方はバッチリですね！！
セル内の改行を置換して取り除く方法
さて、今度はセルの中の改行を置換する方法です。
改行コードなどと呼ばれていて、「\r\n」と表します。
正規表現とも言われるんですが、円マーク￥がバックスラッシュに見えていると思いますので、半角円マークに脳内変換してください・・・。
今回は、改行を半角スペースに置換します。
画像の通りＢ列で改行が含まれていますが、Ｃ列ではSUBSTITUTE関数の置換で改行が消えました。
改行は「\n」だけという場合もあります。
「\r\n」で上手く置換されない場合は、「\n」だけなど片方で試してみてくださいね。
SUBSTITUTE関数が役立つとき
SUBSTITUTE関数はSEやテスターなど、IT関連の仕事をしている方なら覚えておきたいエクセル関数の１つです。
ちょっと使えれば、作業がかなり楽になります。
微妙に日付の型などが違うデータ比較にめちゃ便利
データを取り込むテストなどで、取り込み元と取り込み先の値が一致していることを検証するテストケース、非常に多いと思います。
CSV内の日付は、年月日がスラッシュ「/」区切りなのに、DBに入るとハイフン「-」に換わっちゃうテーブル、よくありますよね。
SUBSTITUTE関数で、DBに入って「-」に置換されてしまった区切りを「-」に置換し直し、CSVデータと比較します。
すべて一致して、問題ない事を確認できました。
もちろん、置換の関数にREPLACEというのもあります。
でも今回見たいな場合、ハイフンが２つあって２回書くのは面倒です。
しかも、「2018/9/9」、「2018/9/10」、「2018/10/9」のように、０埋めされていなくて区切りの位置がバラバラだとREPLACEを使っていたらホント泣きそう・・・
位置を気にしなくていいSUBSTITUTEが便利だと感じる瞬間ですね！
似たようなSQL文の量産に便利
日付項目を持っている色んなテーブルのデータを調査する、
なんてのもよくあることです。
しかも、なぜかテーブルによって項目名がバラバラだったりするんですよね。
そんな時こそSUBSTITUTE関数です！！
テーブル名と日付項目の一覧から、SQL文を書きます。
よくやっちゃうのが文字列結合です。
一番面倒なのは、「&」で繋げていく方法。
テーブル名とWHEREの間にスペースを入れ忘れるとか、うっかりミスするやり方です。
次にめんどいのが、CONCAT関数で結合していく方法です。
「&」で繋ぐよりは楽だけど、やってみてやっぱめんどくさかったです(笑)
一番おすすめなやり方はこれです。
あらかじめベースとなるSQL文を書いておいて、SUBSTITUTE関数で置換する方法。
いつも通りにSQLを書いておいて、置換用に書いた「AAAA」のような表現それぞれを置換していくだけのやり方なので、ミスが少なくて済みます。
繰り返し出てくる文字列とか、１回で出来るのが美味しいです。
まとめ：SUBSTITUTEを使って改行を置換、便利な使い道
Excel関数、SUBSTITUTEで改行を置換したり、他にも便利な使い方をする方法でした。
知っておくと思わぬところで役に立ったりするのがExcel関数です。
SUBSTITUTEはめちゃ便利なので、ぜひ覚えていってくださいね。
