この記事を読むのに必要な時間は約 7 分です。

エクセルを使っていて思うこと。
「セルの中にある改行が邪魔!!」

 

誰でも1度や2度はあるはずです。

 

1発で取り除ける便利な方法があったらいいのに・・・
そんな便利な方法、もちろんあります!!

 

改行も置換できる、Excel関数「SUBSTITUTE」の使い方をご紹介します。

 

スポンサーリンク

文字列を置換するExcel関数、SUBSTITUTEの使い方

まずはSUBSTITUTEの引数から。

 

引数を日本語で書くと、
=SUBSTITUTE(文字列, 検索文字列, 置換文字列, [置換対象])

 

それぞれの引数の意味は、

 

  1. 文字列:置換したい文字が含まれるもの
  2. 検索文字列:置換前の文字列
  3. 置換文字列:置換後の文字列
  4. [置換対象]:検索文字列の内、一部分だけ換えたいなら指定、省略可

 

ちょっと分かりづらいと思うので、例を使って説明します。

改行も置換できる、SUBSTITUTEの使い方

 

今はもう秋ということで、
「もうあきだあ」という文字列でやってみます。

 

B2セルの中の「あ」という文字を「す」という文字に置換します。
その場合の書き方は、

 

=SUBSTITUTE(B2,”あ”,”す”)
ですね。

 

「もうあきだあ」から、「もうすきだす」に換わりました。

 

これで使い方はバッチリですね!!

 

セル内の改行を置換して取り除く方法

さて、今度はセルの中の改行を置換する方法です。

 

改行コードなどと呼ばれていて、「\r\n」と表します。
正規表現とも言われるんですが、円マーク¥がバックスラッシュに見えていると思いますので、半角円マークに脳内変換してください・・・。

 

今回は、改行を半角スペースに置換します。

改行も置換できる、SUBSTITUTEの使い方

 

画像の通りB列で改行が含まれていますが、C列ではSUBSTITUTE関数の置換で改行が消えました。

 

改行は「\n」だけという場合もあります。
「\r\n」で上手く置換されない場合は、「\n」だけなど片方で試してみてくださいね。

SUBSTITUTE関数が役立つとき

SUBSTITUTE関数はSEやテスターなど、IT関連の仕事をしている方なら覚えておきたいエクセル関数の1つです。

 

ちょっと使えれば、作業がかなり楽になります。

微妙に日付の型などが違うデータ比較にめちゃ便利

データを取り込むテストなどで、取り込み元と取り込み先の値が一致していることを検証するテストケース、非常に多いと思います。

 

CSV内の日付は、年月日がスラッシュ「/」区切りなのに、DBに入るとハイフン「-」に換わっちゃうテーブル、よくありますよね。

改行も置換できる、SUBSTITUTEの使い方

 

SUBSTITUTE関数で、DBに入って「-」に置換されてしまった区切りを「-」に置換し直し、CSVデータと比較します。

 

すべて一致して、問題ない事を確認できました。

改行も置換できる、SUBSTITUTEの使い方

 

もちろん、置換の関数にREPLACEというのもあります。

 

でも今回見たいな場合、ハイフンが2つあって2回書くのは面倒です。
しかも、「2018/9/9」、「2018/9/10」、「2018/10/9」のように、0埋めされていなくて区切りの位置がバラバラだとREPLACEを使っていたらホント泣きそう・・・

 

位置を気にしなくていいSUBSTITUTEが便利だと感じる瞬間ですね!

似たようなSQL文の量産に便利

日付項目を持っている色んなテーブルのデータを調査する、
なんてのもよくあることです。

 

しかも、なぜかテーブルによって項目名がバラバラだったりするんですよね。

 

そんな時こそSUBSTITUTE関数です!!

 

テーブル名と日付項目の一覧から、SQL文を書きます。

改行も置換できる、SUBSTITUTEの使い方

 

よくやっちゃうのが文字列結合です。

 

一番面倒なのは、「&」で繋げていく方法。
テーブル名とWHEREの間にスペースを入れ忘れるとか、うっかりミスするやり方です。

改行も置換できる、SUBSTITUTEの使い方

 

次にめんどいのが、CONCAT関数で結合していく方法です。

 

「&」で繋ぐよりは楽だけど、やってみてやっぱめんどくさかったです(笑)

改行も置換できる、SUBSTITUTEの使い方

 

一番おすすめなやり方はこれです。

 

あらかじめベースとなるSQL文を書いておいて、SUBSTITUTE関数で置換する方法。

 

いつも通りにSQLを書いておいて、置換用に書いた「AAAA」のような表現それぞれを置換していくだけのやり方なので、ミスが少なくて済みます。

 

繰り返し出てくる文字列とか、1回で出来るのが美味しいです。

改行も置換できる、SUBSTITUTEの使い方

 

スポンサーリンク

まとめ:SUBSTITUTEを使って改行を置換、便利な使い道

Excel関数、SUBSTITUTEで改行を置換したり、他にも便利な使い方をする方法でした。

 

知っておくと思わぬところで役に立ったりするのがExcel関数です。

 

SUBSTITUTEはめちゃ便利なので、ぜひ覚えていってくださいね。