エクセル セル内改行を別セル抽出

質問

画像のように、セル内で改行された文字を別のセルに分割して抽出したいのですが、どうしたらいいものでしょう。
一気には無理でも、できるだけ簡単に、早く、という感じで。
複数行、複数列が対象です。
区切り位置とかで試してみたのですが、一列一列しか処理できないことや、違う列に上書きされたりして、結構時間がかかるもので。
すいません、何卒よろしくお願いいたします！

回答No.4

nishi6 · ベストアンサー

添付図のように、Sheet1のA1からのデータをSheet2のA1から書き出します。改行は１セルに１回としています。

変換の手順は、
　（１）区切りコード(CHAR(10))で前後に分ける。
　（２）横に展開する。
　（３）Sheet2では、２列右に進むごとにSheet1への参照を１列増やす
座標変換になります。

算式はセルA1とセルB1の２つがセットです。（aokiiさんの式を使わせてもらいました）
セルA1：=IFERROR(LEFT(OFFSET(Sheet1!$A1,0,(COLUMN()-1)/2),FIND(CHAR(10),OFFSET(Sheet1!$A1,0,(COLUMN()-1)/2))),"")
セルB1：=IFERROR(MID(OFFSET(Sheet1!$A1,0,INT((COLUMN()-1)/2)),FIND(CHAR(10),OFFSET(Sheet1!$A1,0,INT((COLUMN()-1)/2))),1000),"")

長い算式になってしまいました。ご辛抱を。回答をコピーしてください。２つのセル単位で右方向に必要分コピーし、行方向に必要分増やしてください。未入力対応しています。

お礼

ありがとうございます！正直VBA自体もよく理解していなかったのですが、ちょっと数値を変えてみたらできました！すごいですね、助かりました。

回答No.3

imogasi

#2です。
VBAでやってみた。
質問者の場合に合わせて、下記コードを部分修正できるようならやってみたら。
Split関数利用が目新しいだけで、あとはVBA的には初等的平凡。
例データ　Sheet1　A1:B3　各セルには、どのセルも、２回のセル内改行とした
（見た目３行です）。
"11
aa
xxx"
"22
bb
yyy"
"33
cc
zzz"
"44
dd
uuu"
"55
ee
vvv"
"66
ff
www
"
標準モジュールに
Sub test01()
' 留意事項
'Sheet1の行数　任意
'Sheet1の列数　任意　ただし各行で同列数とする。
'Sheet1のセルの改行数　一定であること
Dim s As Variant
Set sh1 = Worksheets("Sheet1")
Set sh2 = Worksheets("Sheet3")
lr = sh1.Range("A100000").End(xlUp).Row
cr = Cells(2, 1000).End(xlToLeft).Column
'MsgBox lr & " " & cr
'-----
jj = 1 'Sheet3のA列へ
For i = 1 To lr
For J = 1 To cr
s = Split(sh1.Cells(i, J), Chr(10))
'MsgBox i & "行" & J & "列 " & "改行後行数" & UBound(s) + 1
For u = 0 To UBound(s) '0から始まる
'MsgBox i & "行" & jj & " 列　" & s(u)
sh2.Cells(i, jj) = s(u)
jj = jj + 1 'Sheet2で次列へ
Next u
Next J
jj = 1 'Sheet3のA列へ
Next i
End Sub
実行結果　Sheet3
11 aa xxx 22 bb yyy
33 cc zzz 44 dd uuu
55 ee vvv 66 ff www
「Sheet1の各セルの改行数は一定であること」を、「バラバラもあり得る」、とすると、結果を（列的に）シートにどう出してよいか迷う。

お礼

ありがとうございます！対応いただき助かりました。非常に参考になりました。次からVBAをもっと学習したいと思います！

回答No.2

imogasi

質問者のれべるでは、エクセル関数でということだろうか。
基データがセル内改行が２行なら下記で出来そうだが、それを超えると、式が複雑になりすぎて、やる気が出ない。
VBAでやるなら、コード行数は増えるにしても可能だろうと思う。
Sheet1のA,B列の例　２列３行分の例示　
シートセル範囲をコピーして貼り付けると、変な様子になるが、状態は判りますね。質問の通りで、１せる行分増やしただけ。
"11　＜－－個の箇所でセル内改行あり。以下同様。
aa"
"22
bb"
"33
cc"
"44
dd"
"55
ee"
"66
ff"
ーーー
Sheet２に行って
A1セル　=MID(Sheet1!A1,1,FIND(CHAR(10),Sheet1!A1)-1)
B1セル　=MID(Sheet1!A1,FIND(CHAR(10),Sheet1!A1)+1,LEN(Sheet1!A1)-FIND(CHAR(10),Sheet1!B1))
C1セル　=MID(Sheet1!B1,1,FIND(CHAR(10),Sheet1!B1)-1)
D1セル　=MID(Sheet1!B1,FIND(CHAR(10),Sheet1!B1)+1,LEN(Sheet1!B1)-FIND(CHAR(10),Sheet1!B1))
基データの行セル数だけ式複写。
結果　Sheet2のA1:D3
11 aa 22 bb
33 cc 44 dd
55 ee 66 ff

回答No.1

aokii

Sheet1のA1セルを分割する場合は以下の式をSheet1のC1セルとD1セルに、それぞれ、入れてみて下さい。
Sheet1のC1セルに入れる式
=LEFT(A1,FIND(CHAR(10),A1))
Sheet1のD1セルに入れる式
=MID(A1,FIND(CHAR(10),A1),1000)

お礼

ありがとうございます。私のレベルでも理解できました。次回作るときに参考にしたいと思います！