使用関数：CLEAN、SUBSTITUTE、CHAR、CODE
一つのセル内に、社名と部署を改行で２行分けしていたり、長い住所を分割していることがあります。改行を削除して２行の文字列を１行にまとめる操作を図解します。改行を消すだけならCLEAN関数一択、間に空白を置いて１行にしたいならSUBSTITUTEとCHAR関数を組み合わせて使います。CODE関数の力を借りる場合もあるかも…。
CLEAN関数で改行を一発消去
改行だけを削除して１行にするなら、CLEAN関数で即解決です。
この関数は印刷できない制御文字・特殊文字を消去してくれます。インポートしたデータを、データベースとしての活用に支障が出ないようCLEAN関数で清掃するといった使い方をします。
下図C列の「所属部署」から改行を取り去って、D列に１行表示させてみましょう。
「数式」タブの「関数ライブラリ」→「文字列操作」から「CLEAN」をクリック。
ダイアログが開きます。引数は「文字列」一つ。削除したい制御文字がある範囲を選択するだけです。この場合は「所属部署」の最初のセルを指定します。
下図はテーブル化した表なので、構造化参照で「[@所属部署]」と入りました。
「構造化参照」で分かりやすい数式を組み立てる
テーブルでなければ、セル番地が入ります。
数式は、構造化参照：=CLEAN([@所属部署]) A1参照：=CLEAN(C2)
数式をフィルコピーして完了です。
CLEAN関数で改行を削除すると、上図のように区切りのない一続きの文字列にまとめられます。これだと、さすがに見づらいので、改行の削除と同時に「部」と「課」の間に空白を表示させておく数式について次章で解説します。
もし、「部」と「課」を切り分けて、別個のフィールドに置きたい時は、以下の記事を参照して下さい。
SUBSTITUTE関数＋CHAR関数で空白を挟んだ１行文字列にする
「空白を挟んだ１行文字列にする」とは、下図のような結果を得たいということです。「部」の後にスペースが入ってますね。
SUBSTITUTEは文字列を置き換えてくれる関数です。「数式」タブの「関数ライブラリ」→「文字列操作」から「SUBSTITUTE」をクリックしてダイアログを開きます。
ダイアログの最初の引数「文字列」は、前章のCLEAN関数と同じく「所属部署」の最初のセルを指定します。そして、次の引数である置換前の「検索文字列」に改行を指定するわけですが、改行を文字で入力できないので、対応するコード番号をCHAR関数で呼び出します。
CHAR関数はコード番号に対応する文字を返します。ダイアログを見てわかるように、
引数は「数値」。ここに「改行」の文字コードを入力するのです。
でも、「改行」の文字コードって何番？？
知ってる人は知ってるかもだけど、たぶん普通は知らない・・・と思います。
知らない人を救ってくれるのが「CODE関数」です。
改行のコード番号をCODE関数で確認
CODE関数はCHAR関数と真逆の働きをします。即ち、文字のコード番号を教えてくれる関数なのです。
どこか端っこのセルをダブルクリックして編集モードにし、
Alt
とEnter
を押して、改行を入力します。
隣のセルを選択して、「数式ライブラリ」→「文字列操作」の「CODE」をクリック。
改行を入力したセルを引数に指定すると、「OK」するまでもなく、ダイアログの中に答えが現れます。「10」ですね。コードが判ったら、2つのセルはクリアしましょう。
CHAR関数のダイアログで引数「数値」に「10」と入れます。
数式は「=CHAR(10)」となります。
この数式の「CHAR(10)」をSUBSTITUTE関数のダイアログ「検索文字列」にペースト。「置換文字列」には「" "」。全角のスペースを指定しました。
最終的な数式は「=SUBSTITUTE([@所属部署],CHAR(10)," ")」
言葉にすると「所属部署フィールドの値にある改行を全角スペースに置き換える」。
空白を挟んだ１行文字列になりました。
