Excel使いこなしの基礎と応用

改行を消して、続けた1行文字列にするCLEAN関数と空白を挟んだ1行文字列にするSUBSTITUTE+CHAR

使用関数:CLEAN、SUBSTITUTE、CHAR、CODE

一つのセル内に、社名と部署を改行で2行分けしていたり、長い住所を分割していることがあります。改行を削除して2行の文字列を1行にまとめる操作を図解します。改行を消すだけならCLEAN関数一択、間に空白を置いて1行にしたいならSUBSTITUTEとCHAR関数を組み合わせて使います。CODE関数の力を借りる場合もあるかも…。

目次


CLEAN関数で改行を一発消去

改行だけを削除して1行にするなら、CLEAN関数で即解決です。
この関数は印刷できない制御文字・特殊文字を消去してくれます。インポートしたデータを、データベースとしての活用に支障が出ないようCLEAN関数で清掃するといった使い方をします。

スポンサーリンク

下図C列の「所属部署」から改行を取り去って、D列に1行表示させてみましょう。
改行で2行になった部署名
数式」タブの「関数ライブラリ」→「文字列操作」から「CLEAN」をクリック。
「関数ライブラリ」→「文字列操作」からCLEANをクリック
ダイアログが開きます。引数は「文字列」一つ。削除したい制御文字がある範囲を選択するだけです。この場合は「所属部署」の最初のセルを指定します。
下図はテーブル化した表なので、構造化参照で「[@所属部署]」と入りました。
▶データ表にはテーブルを使うべき!その理由と設定方法
▶「構造化参照」で分かりやすい数式を組み立てる
CLEANのダイアログで「文字列」に所属部署の最初のセルを指定
テーブルでなければ、セル番地が入ります。
CLEAN関数の引数にセル番地を指定
数式は、構造化参照:=CLEAN([@所属部署]) A1参照:=CLEAN(C2)
数式をフィルコピーして完了です。
1行の文字列になった

CLEAN関数で改行を削除すると、上図のように区切りのない一続きの文字列にまとめられます。これだと、さすがに見づらいので、改行の削除と同時に「部」と「課」の間に空白を表示させておく数式について次章で解説します。

※もし、「部」と「課」を切り分けて、別個のフィールドに置きたい時は、
▶区切り位置指定ウィザードで雑多な固定長データを分割し、不要な文字を削除

SUBSTITUTE関数+CHAR関数で空白を挟んだ1行文字列にする

「空白を挟んだ1行文字列にする」とは、下図のような結果を得たいということです。「部」の後にスペースが入ってますね。
「部」の後にスペースが入って1行の文字列になった例

SUBSTITUTEは文字列を置き換えてくれる関数です。「数式」タブの「関数ライブラリ」→「文字列操作」から「SUBSTITUTE」をクリックしてダイアログを開きます。
関数ライブラリの文字列操作にあるSUBSTITUTEをクリック
ダイアログの最初の引数「文字列」は、前章のCLEAN関数と同じく「所属部署」の最初のセルを指定します。そして、次の引数である置換前の「検索文字列」に改行を指定するわけですが、改行を文字で入力できないので、対応するコード番号をCHAR関数で呼び出します。
SUBSTITUTE関数の「検索文字列」にCHAR関数を入れ子

CHAR関数はコード番号に対応する文字を返します。ダイアログを見てわかるように、
引数は「数値」。ここに「改行」の文字コードを入力するのです。
CHAR関数の引数には「改行」の文字コードを入力
でも、「改行」の文字コードって何番??
知ってる人は知ってるかもだけど、たぶん普通は知らない・・・と思います。

知らない人を救ってくれるのが「CODE関数」です。

改行のコード番号をCODE関数で確認

CODE関数はCHAR関数と真逆の働きをします。即ち、文字のコード番号を教えてくれる関数なのです。

スポンサーリンク

どこか端っこのセルをダブルクリックして編集モードにし、
AltAltキーEnterEnterキーを押して、改行を入力します。
隣のセルを選択して、「数式ライブラリ」→「文字列操作」の「CODE」をクリック。
「関数ライブラリ」→「文字列操作」からCODEをクリック
改行を入力したセルを引数に指定すると、「OK」するまでもなく、ダイアログの中に答えが現れます。「10」ですね。コードが判ったら、2つのセルはクリアしましょう。
セルに改行を入力し、CODE関数の引数に指定

CHAR関数のダイアログで引数「数値」に「10」と入れます。
CHAR関数のダイアログで引数「数値」に「10」と入力
数式は「=CHAR(10)」となります。

この数式の「CHAR(10)」をSUBSTITUTE関数のダイアログ「検索文字列」にペースト。「置換文字列」には「" "」。全角のスペースを指定しました。
SUBSTITUTE関数のダイアログの「検索文字列」にCHAR関数を入れ、「置換文字列」に全角スペースを指定
最終的な数式は「=SUBSTITUTE([@所属部署],CHAR(10)," ")
言葉にすると「所属部署フィールドの値にある改行全角スペースに置き換える」。

空白を挟んだ1行文字列になりました。
空白を挟んだ1行文字列になった

▶社名から「株式会社」を削除する2通りの方法
▶SUBSTITUTE関数で複数の文字列を一括で置き換える


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。