今回の記事では
改行されたセルの文字列を関数を使って一つの文字に変換することって出来ないの？
なるべく簡単な方法を知りたいけどそんなことって出来る？
セルごとに別れている文字列を一つのセルに改行して表示する関数ってあるの？
改行を簡単に削除出来るのは良いけど元に戻す方法も合わせて知っておきたい
こういった疑問に答えます
✔関数を使って改行を削除する方法のテーマ
①SUBSTITUTE関数＋CHAR関数を組み合わせれば改行を削除出来ます
②改行を簡単に設定したい時はCHAR関数を活用すれば手軽に出来ます
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
「関数を使って改行を削除する方法のテーマ」に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
SUBSTITUTE関数＋CHAR関数を組み合わせれば改行を削除出来ます
SUBSTITUTE関数＋CHAR関数を組み合わせると改行を自動的に削除することが出来ます
なぜなら、置換機能に優れたSUBSTITUTE関数と改行を調べてくれるCHAR関数を組み合わせれば改行だけ削除することが出来るからです
それでは具体的な改行の削除方法について見ていきましょう
今回は、セル内で改行された住所のデータをなるべく簡単に改行なしの住所にする方法です
今回のサンプルはこんな感じで都道府県などの住所の情報がセル内で改行されています
こんな風にセル内で改行されたデータを扱うことは実務でもよくあり
特にCSVなどの外部データを開いたり読み込んだりした時に発生することがあります
そんな時は今回の方法でサクッと改行を削除できるようになっておくとすごく便利ですよ
では、やり方を早速見ていきますがその前に1つだけ確認しておきましょう
それが「セル内で改行する方法」です
Excelの場合ただEnterキーを入力しても他のセルに移動してしまうだけなので、セル内で改行させるには技が必要になります
それが「ALT＋Enterキー」という組み合わせです
セル内改行の方法を簡単に確認しましょう
やり方はこれだけです
セル内改行について確認したところで実際のやり方を見ていきましょう
SUBSTITUTE関数を使って全ての改行を削除する
設定した内容はこんな感じです
複数回改行して住所を表示しているB5のセルの内容から改行が削除されたことで1行の住所としてちゃんと表示されていますね
今回のポイントは2つの関数を組み合わせている点です
式を見てみると「=SUBSTITUTE($B5,CHAR(10),“”)」ですね
SUBSTITUTE関数の基本的な使い方は、「=SUBSTITUTE(文字列,検索文字列,置換文字列,[置換対象])」です
今は「置換対象」の設定は必要無いので無視してもらって大丈夫です
この式を日本語にすると「=SUBSTITUTE(B5のセルの文字列の,改行を,空白に置換してね)」という意味です
「空白に置換」がポイントで改行を空白にするということは、削除することと一緒の意味になります
Excelを使いこなしてくるとこの空白を使った文字列などの削除はよく使うのでぜひ覚えておいて下さい
空白に置き換える時は「”(ダブルクォーテーション)」を2つ入力するのが決まりです
次に今回の重要な部分になるCHAR関数について知っておきましょう
CHAR関数は「文字コードを文字として返してくれる」関数です
文字コードというのは簡単に言うと「01=あ」だよ「02=い」だよ
と言ったように文字コードというルールに沿ってプログラム上「01」だったら「あ」って表示してね
といったようにパソコンの裏側で動いている文字を表現するためのルールのことです
パソコンはこのルールに従うことで文字や英数を表示しているんですね
この文字コードをExcelでも「OK、CHAR関数が10だから改行コードを文字として扱うね」といったように目で見て分かる形にしてくれる関数です
この「改行という文字」に対して「空白に置換」ということをやってくれているのが今回の式という訳です
なので、改行が削除されて残った住所は改行なしの状態で表示されたという訳です
説明文にすると長くなりますが、分かってしまえば意外と単純ですよね
SUBSTITUTE関数を使って狙った改行を削除する
次はSUBSTITUTE関数で任意の順番の改行を削除する方法を見ていきましょう
設定方法はこんな感じです
「=SUBSTITUTE($B5,CHAR(10),””,2)」基本はさっきと一緒ですが違いは最後の引数にあります
「引数(ひきすう)」とは設定内容のことで「2」を設定していますね
これは、「置換対象」の引数なので2番目の文字列を検索して置換してねという意味になります
今回の検索対象は「改行」で「改行の2番めは美波町」の後の改行ということになります
ちょっと混乱しやすいので図解してみましょう
こんな風に改行は目に見えませんがいくつも設定されています
この②が今回のターゲットだったので②の改行を削除すると「美波町木岐」の部分がつながって他は改行されているという結果が表示されます
こんな風に置換対象を設定することで任意の順番の文字列を消すことが出来るというわけです
SUBSTITUTE関数については「文字列置換ならSUBSTITUTEとREPLACE関数」とう記事でも解説しているので参考にしてみて下さい
置換機能を使って改行を削除する方法
最後に置換機能を使った改行の削除方法を解説します
置換機能を使うと改行を削除するのはもっと素早く行うことが出来ます
簡単ですが特殊な操作が必要なので手順を追って解説します
今回は住所の改行を削除したいので先に削除したい範囲を選択しておきましょう
「ホームタブ」の「編集グループにある検索と選択」ボタンをクリック
「置換」をクリック
「検索と置換ダイアログ」の「検索する文字列のTEXTボックス」に「Ctrlキー+Jキー」を押します
「すべて置換」ボタンをクリック
置換が出来ない場合は検索と置換のTEXTボックスの内容をもう一度削除してみて下さい
見えない文字が入力されている場合上手くいかないのでDeleteキーやBackSpaceキー
を何度か押して再度検索する文字列に「Ctrlキー+Jキー」を入力して下さい
置換作業が終わるとメッセージが表示されるので「OK」ボタンをクリック
検索と置換ダイアログも閉じて下さい
最後に「折り返して全体を表示」ボタンを押して折返しを解除してみましょう
ちゃんと長い文字の住所になっていればOKです
今回検索文字列に「Ctrlキー+Jキー」の入力をしましたが、この作業は文字コードである改行を検索するための方法です
この組み合わせにる理由は深い話になるので気にする必要はありません
ちょっと失敗しやすいやり方ですが、この方法なら一回で改行が削除できるので試してみて下さい
置換機能を使う場合は、元の文字列を直接置換する時に向いています
関数を使う場合は、元の文字列をとっておきたい時などに向いているので目的によって使い分けましょう
繰り返しになりますが、SUBSTITUTE関数＋CHAR関数を組み合わせると改行を自動的に削除することが出来ます
改行を簡単に設定したい時はCHAR関数を活用すれば手軽に出来ます
セルに分けられた文字列を改行して一つのセルに表示させるにはCHAR関数を活用すれば簡単に出来ます
なぜなら、CHAR関数で改行コードと文字列をくっつけることで素早く改行した文字列を作ることが出来るからです
それでは実際にやってみましょう
改行を手作業で削除するよりもずっと簡単なので気軽に出来ますよ
こんな風にセルに別れた住所も一つのセルで改行して表示しています
ではやり方を見ていきましょう
今回の式は「=B5&CHAR(10)&C5&CHAR(10)&D5&CHAR(10)&E5&CHAR(10)&F5」
長いので難しそうに感じますが実はすごく簡単です
「=B5&CHAR(10)&C5」のように「目的のセル+改行+目的のセル」としているだけです
つまり、「徳島県(改行)海部郡～」としてるだけなのですごく簡単ですね
ただ、変換下だけでは改行されないことがあるのでその時は「折り返して全体を表示する」機能をONにしてあげれば大丈夫です
最後に置換機能を使って文字列を改行する方法を見ていきましょう
こちらもちょっと特殊ですがまず住所を区切るための記号で住所を分けます
今回は「/」を付けて住所を分けています
この「/」に対して置換機能を使って改行してみます
置換機能の使い方は基本的に一緒です
置換機能の検索数文字列に「/」を入力
置換後の文字列に「Ctrlキー+Jキー」を入力
「すべて置換」ボタンをクリック
置換が成功するとこんな風にキレイに改行が出来ます
但し、1回では上手くいかないこともあるので改行の場合は関数の方が向いていると言えます
このように見えない改行を上手く扱う方法を覚えておくと便利なのでやり方を押さえておきましょう
繰り返しになりますが、セルに分けられた文字列を改行して一つのセルに表示させるにはCHAR関数を活用すれば簡単に出来ます
オススメのExcel関数の解説書 5選
関数を本で学ぶ時は次に上げるものがオススメです
本を購入をオススメしている理由と各本の紹介に関して、下記の記事で詳しく解説しているので合わせて読んでみてください
![VLOOKUP関数で０やエラー表示を避ける方法［IF・IFERROR関数との組み合わせ]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4327107_m.jpg?fit=1200%2C900&ssl=1&resize=40%2C40){kind=small}
![IF関数＋COUNTIF関数で特定の文字をピックアップ[条件式を工夫すれば除外も可]](https://i0.wp.com/tsurutoro.com/wp-content/uploads/2022/01/4007523_m.jpg?fit=1200%2C675&ssl=1&resize=40%2C40){kind=small}
コメントを残す