EXCELでセルの改行箇所で値を分割する数式を作る

EXCELの資料修正作業にて、改行して2つの情報を1つセルにまとめていたものを2つのセルに切り分けたいという相談があったので、作ってみました。

今回はEXCEL VBAではなく、EXCELの数式で用意してオートフィルで提供します。
忘れないように控えておきます。

※なお、今回の検証環境はWindows10 EXCEL2013です。

結論

※対象セル=改行して2つの情報が入っているセルとします。

考え方

とにかく、改行コードを検知できれば後は簡単です。調べてみたら、ちょうど良いのがありました。

ポイントは、下記の通りです。

では、ポイントに沿って具体的に落とし込んでいきましょう。

CHAR関数で改行コードの値を取得する

上記の「改行コードがセルの何文字目にあるかを調べる」のポイントになります。

EXCEL内部で各文字は内部コードを持っています。CHAR関数を使って、コードを指定して対象となる文字列を取得しましょう。

ちなみに、改行コードの内部コードは「=CHAR(10)」と表します。

FIND関数で改行コードの文字の位置を取得する

上記に引き続き、「改行コードがセルの何文字目にあるかを調べる」のポイントになります。

先ほどのCHAR関数で改行コードの文字列は取得したので、FIND関数を使って改行コードが何文字目にあるのかを取得しましょう。

FIND関数で改行コードの場所を調べる数式は、「=FIND(CHAR(10),対象セル)」と表します。

LEFT関数で1行めの情報を取得する

続いて、「一つ目の値は、LEFT関数で左から改行コードの場所までを切り取る」のポイントについてです。

FIND関数で改行コードが何文字目にあるのかが分かれば後は、改行コードを境目にしてセル内の文字列を切り取りましょう。

1行めの情報は、LEFT関数で左から改行コードまでを取得します。LEFT関数の場合は、書き出し位置について意識しなくて大丈夫です。

上記を踏まえて、LEFT関数も踏まえた数式は「=LEFT(対象セル,FIND(CHAR(10),対象セル))」と表します。

RIGHT関数で2行めの情報を取得する

続いて、「二つ目の値は、RIGHT関数で右から改行コードの場所までを切り取る」のポイントです。

2つめは、RIGHT関数で右から取得します。

RIGHT関数の場合は、右から書き出し位置までを取得します。

LEN関数でセル内の文字数から、改行コードの文字数を引きます。

2つめの数式は「=RIGHT(対象セル,LEN(対象セル)-FIND(CHAR(10),対象セル))」と表します。

需要があるかは怪しいですが、昔のEXCELデータや他所からもらったEXCELデータを加工や整理をする時にでも使えるでしょうか(実体験ｗ)

参考になれば幸いです。