VBAで1つのセルにある複数行データを分けるサンプル

複数のデータが改行（Alt + Enter）されて1つのセルに入力されている状態（セル内改行）から、それら複数のデータを複数行へ分離するサンプルコードです。いわゆる1セル1データに変換するサンプルコードをご紹介します。

人からもらったExcelファイルのデータの中に、たまに1つのセルに複数のデータが入力されているのを見ることがあります。せっかく頂いたデータですが、そのままでは集計するのが難しいですよね。

そこで、そのデータを整形することになるのですが、この作業が意外と大変なんですよね。しかも手作業や関数など組み合わせる方法だと、間違いや確認時間もそれなりに必要になりますよね。

そこで、今回はそのようなセル内改行されたある複数のデータを、複数行へ1つずつ格納できるVBAコードをご紹介しようと思います。

まずは本記事でご紹介するサンプルファイルの内容と、実行ビフォアアフターが分かる1分動画（音声無し）をご確認ください。

セル内改行データがあるサンプルシート

今回取り上げるサンプルシートです。項目は、社員ID, 名前, 担当地域と担当地区コードの4つです。

今回問題となっているのは、赤枠で囲ってある「担当地区コード」欄です。確認すると複数の文字列がセル内改行されて入力されていますよね。

最終的な集計をするためには、この状態から1セル1データの複数行のレコードに分割したいですよね。ここで、整形完成後のイメージをしやすいように、下図を用意しました。。

図はサンプルシートの最初のレコードにある木村社員の例になります。図を見て頂くと、「担当地区コード」の1つのセルに、4行入力されてますよね。

今回のVBAコードで実施するのは、図の上側にある整形前の状態を、下側のように各担当地区コードに分割します。そうなれば、あとの集計も楽になりそうですよね。

セル内改行データを1セル1データの複数行にするVBAサンプルコード

それでは、今回のVBAサンプルコードをご紹介しますね。いつもの通り、真似て書いてみてください。

では、書き写したらVBAを実行してみてくださいね。実行すると、下記の通りセル内改行されていたものが分割されて、複数行のレコードになります。

1セル1データに分解するVBAコードのアルゴリズム

1セル1データに分解するVBAコードのアルゴリズムについて、これ以降詳しくご紹介していきます。

5つのプロセスで1セル1データを実現するアルゴリズム

では、今回のVBAコードの詳しいお話しをしたいと思います。これまでよりも少しボリュームアップしていますが、これまでの知識で理解できる箇所が多く、新しいVBAコードは少ないのに気づくと思います。

既にお気づきかもしれませんが、今回のVBAコードのアルゴリズムを把握しやすいようにコードの中に〇番付きでプロセス順を書いてみました。そこで、まずは このプロセスについて、簡単にお話ししておきますね。

まずはじめに、セル内改行されたセルに、データがいくつ把握できたら良さそうですよね。そこで、「担当地区コード」欄の複数データが、セル内改行されている点に目をつけてみます。

①ではまずセルの中の改行されている数を求めています。そして、①で求めた改行の数とデータ数の関係からセルの中の行数を求めています（②）。

②でデータ数を求めたあと、そのデータ数と等しい行数分の整形前レコードを新しい行にコピペします（③）。

④では、「担当地区コード」欄からデータを分割します。そして、分割されたデータを③でコピペしたレコードの「担当地区コード」欄に転記します。

①～④をすべての整形前レコードに対し実施したあと、実行前の整形前レコードをすべて削除します（⑤）。ではいよいよVBAコードを詳しく見ていきますね。

1セル1データ化するVBAコード詳細

2行目： Dim intLfCt As Integer
プロセス①で改行コードの数を数えるカウンター用変数です。改行コードは、 「VBAコードで手書き資料の入力後チェックをする 」記事で「vbCrLf」（キャリッジリターン+ラインフィード）について触れました。

今回はそれとは異なるセル内での改行コードを示すVBAコード「vbLf」（ラインフィード）になりますが、これについての詳細はあとでお話ししますね。

3行目： Dim intRCnt As Integer
プロセス②で使う1セルに入力されている行数を保持するための変数です。そして、この変数は1セル内のデータ数とも等しいです。

4行目： Dim strCode As String
Mid関数とLen関数のコンビネーションで1文字ずつ順次取り出した「担当地区コード」を保持するための変数です。

6行目：Dim lngSCpy As Long
7行目：Dim lngECpy As Long
プロセス③で、整形前レコードのコピー先の開始行番号と終了行番号を保持する変数です。

11行目： Dim n As Long
プロセス④で、セル内の何個目の「担当地区コード」をコピーするかの情報を保持する変数です。

19～53行目：For ～ Next
すべての整形前データに対する繰り返し処理範囲になります。ここにプロセス①～④が入っていますね。

VBAコードで改行コードの数を数える（①）

22行目： intLfCt = 0
「担当地区コード」セル内の改行コード(LF）数を保持する変数「intLfCt」を初期化しています。

23～27行目： For i = 1 To Len(Cells(r, 4)) ～ Next
改行コードを数える繰り返し処理範囲です。Mid関数とLen関数の記事でご紹介した手法と同じ考え方になります。

24行目： If Mid(Cells(r, 4), i, 1) = vbLf Then
左辺は、Cells(r, 4)の「担当地区コード」データから、Mid関数で取り出した1文字になります。右辺の「vbLF」は、先に少し触れましたExcelのセル内改行された場合の改行コード(LF）をVBAコードで表したものです。

左辺と右辺を比較した結果が等しければ、Then以降を実行します。

では、ここでセル内の改行コード(LF)が取得されるイメージをつかんで頂くため、図を用意しましたのでご参考ください。

25行目： intLfCt = intLfCt + 1
24行目のIf文で真の場合に実行されるVBAコードです。そのIf文内のMid関数で取り出された「担当地区コード」の1文字分が、ラインフィード(LF)に等しい場合実行されます。実行内容は、変数「intLfCt」を1ずつカウントアップします。

VBAコードでセル内行数（=データ数）を求める（②）

30行目： intRCnt = (Len(Cells(r, 4)) - intLfCt) / 4
左辺「intRCnt」は、セル内の行数であり、セル内のデータ数でもあります。右辺でその値を求めています。

右辺については、下の図をご覧ください。

「Len(Cells(r, 4))」は、改行コードを含んだセル内文字列の長さです。ここから25行目で求めた改行コードの数「intLfCt」を差し引くと、残りは複数データだけからなる文字長さになります。

これを「担当地区コード」の規定の文字数4で除すと、1セル内のデータ数が求まります。

（19/12/20 追記）複数行セルの処理対象文字列が可変長の場合

（コメントで、非常によい質問を頂きましたので記事にも追記させて頂きました）

本サンプルの「担当地区コード」は、4文字の固定長の例でご紹介していますが、2～6文字などに可変する文字列を扱う場合は、VBAコード30行目を以下の通り変更してください。

intRCnt = intLfCt + 1

実はセル内の行数は、セル内の改行コード数に1を加えることで求めることができます。後続の処理は、まったく同じになります。

VBAコードで分割後のレコード行を作成する（③）

33行目： lngSCpy = Range("A" & Rows.Count).End(xlUp).Row + 1
34行目： lngECpy = lngSCpy + (intRCnt - 1)
分割後のレコードを作成するための開始行番号と最終行番号を求めています。下の図で説明しておりますのでご覧ください。

分割前は、1レコード（1行分）ですが、分割後のレコードは「担当地区コード」のデータ数分のレコード数（木村社員の場合は4レコード）になります。

また、次の佐藤さんの処理の時には、lngSCpyは10になり、各社員の処理の際には値が変わることに注意してくださいね。

37行目： Range(Cells(r, 1), Cells(r, 4)).Copy Range(Cells(lngSCpy, 1), Cells(lngECpy, 1))
整形前データを整形後データ作成範囲にコピーすることにより分割後レコード数分の行数を作成しています。これについても図で説明させていただきますね。

図では37行目を前半部と後半部で色分けしてみました。まず前半部分（青字）の「.Copy」ですが、直前のRangeオブジェクトのメソッドになります。

使い方は、「.Copy」の後に貼り付け先（今回の例では赤字で示された後半部分）を指定するだけです。

ここでは、整形前レコードをRangeオブジェクトで指定後、コピーしています。手作業ですと、整形前レコードを範囲指定（A2:D2)してから、右クリックメニューの中にある「コピー」をクリックした状態ですね。

後半部分（赤字）で、コピーした範囲の貼付け先を指定しています。手作業と同じく、A列の範囲（網掛け部）を指定してから、分割後の行数分を貼付けています。

VBAコードで分割したデータを個々の転記先に入力する(④)

40行目： n = 1: strCode = ""
変数nは、分割した複数のデータが何番目のものかを管理するためのものです。ここでは、初期化して1番目のデータから処理するという意味になります。

変数「strCode」は、ここで空にします。その後のMid関数とLen関数のコンビネーションにより、「担当地区コード」から1文字ずつ取り出した文字が繋がって代入されるイメージです。

41～52行目： For i = 1 To Len(Cells(r, 4)) ～ Next
23行目と同じ処理になります。23行目では、改行コード数を数えるための処理でしたが、ここでは「担当地区コード」のデータ部分を取り出す処理になります。

実は23行目の改行コードを数えるときに、一緒にデータ部分を取り出すこともできるのですが、配列を使わないと複雑になるため今回は敢えて同じ処理をもう1度実行しています。

42行目： If Mid(Cells(r, 4), i, 1) = vbLf Then
ここも24行目の処理と同じですね。「担当地区コード」から1文字取り出した文字が改行コードと同じ場合、Then以降を実行します。

43行目： Cells(lngSCpy + (n - 1), 4) = strCode
44行目： n = n + 1: strCode = ""
42行目の条件式で真の場合に実行されるVBAコードです。このコードが実行される頃には、既に変数「strCode」には1つのデータが入っていることになります。では、ロジックを図で紹介しますね。

45行目： Else
42行目で改行コード以外と判定された場合に実行されるVBAコードになります。

46行目： strCode = strCode & Mid(Cells(r, 4), i, 1)
改行コード以外の文字列は、ここで変数「strCode」へ順次代入されます。このロジックについては、Mid関数とLen関数の記事でやさしく解説しておりますので、ぜひ参考にしてください。

48～50行目：If i = Len(Cells(r, 4)) Then ～ End If 
45行目の条件（改行コード以外）で実行されるもう1つのVBAコードです。ここでは、繰り返し回数を保持する変数iと、右辺の「担当地区コード」のセル内容の長さが一致しているかの条件を判定しています。

ここが真（条件式が等しい）ということは、繰り返し処理の最後であるということですよね。つまり、分割された最後のデータが直前の46行目のVBAコードで確定したことになります。

49行目： Cells(lngSCpy + (n - 1), 4) = strCode
43行目とまったく同じVBAコードになります。48行目で最後のデータが確定した後、このVBAコードで最後のデータを転記先のセルに入力しています。

最後にすべての整形前データを削除する(⑤)

56行目： Range(Cells(2, 1), Cells(lngERow, 4)).EntireRow.Delete
最後のVBAコードです。元の整形前データをすべて削除しています。では削除直前のワークシートの状態を下の図で確認してみましょう。

そして、今回のVBAコードを実行した前後の状態を再確認して終わりにしたいと思います。

まとめ

1セルの中に複数のデータが入力されているデータを、分割するVBAコードについてくわしくお話ししてきました。今回のサンプルでは、整形前のレコードを新しい行へ、複数データの数だけコピーしました。

そして、個別に取り出したデータを1つ1つ、新しいレコードに入力した後、最後に整形前のレコードを削除しました。

今回は、セル内改行された複数データの区切りを改行コードにしてサンプルコードを作成しましたが、これを応用して「,」（カンマ）区切りやスペースで区切られた複数データにも応用できますので、必要に応じて今回のVBAサンプルコードをアレンジして利用してみてください。