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

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

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

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

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

 

Advertisement

結論

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

1行めの情報を取得する数式: =LEFT(対象セル,FIND(CHAR(10),対象セル))

2行めの情報を取得する数式: =RIGHT(対象セル,LEN(対象セル)-FIND(CHAR(10),対象セル))

考え方

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

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

・改行コードがセルの何文字目にあるかを調べる。

・一つ目の値は、LEFT関数で左から改行コードの場所までを切り取る

・二つ目の値は、RIGHT関数で右から改行コードの場所までを切り取る

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

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データを加工や整理をする時にでも使えるでしょうか(実体験w)

参考になれば幸いです。