Splitting String at Blank Empty Row

[ESACAWIP]

hi, hope all is good

I am given the following, all in a single cell:

aaa
bbb
ccc

ddd
eee
fff

zzz
eee
eee


can someone help with a macro where if I select the entire column, it would split each cell by the blank row into consecutive columns?
aaa ddd zzz
bbb eee eee
ccc fff eee



Much appreciated!

 

[alansidman]

Hard to understand exactly what it is you are looking for here as cannot determine cell orientation. Please provide sample data and mocked up solution in XL2BB

 

[ESACAWIP]

Hard to understand exactly what it is you are looking for here as cannot determine cell orientation. Please provide sample data and mocked up solution in XL2BB

Sorry i am not sure how that works but here is a screenshot of an example. I have large amounts of columns where data is presented / broken into seperate paragraphs. I would like to split this by each blank row (not line). Is this possible?

 

[jtakw]

Hi,

Is it possible to upload that sample using XL2BB?
So we don't have to Manually Type and recreate your data for testing.

Check my signature for instructions.

 

[jtakw]

Here, I typed in the 1st sample, see if this works for you.
Sorry, I can only offer a formula solution.
Formula copied down and across as far as you need.

Book3.xlsx
	A	B	C	D	E
2	45334 Turk Avenue apt 2031 New York, NY 43254 Car: Corvette White Sports Car Height: 5 9 eye: brown	45334 Turk Avenue apt 2031 New York, NY 43254	Car: Corvette White Sports Car	Height: 5 9 eye: brown
Sheet907

Cell Formulas
Range	Formula
B2:E2	B2	=TRIM(MID(SUBSTITUTE($A2,CHAR(10)&CHAR(10),REPT(" ",LEN($A2))),COLUMNS($B2:B2)*LEN($A2)-LEN($A2)+1,LEN($A2)))

 

[ESACAWIP]

Macro to extraact comments in column A.xlsm
	A	B	C	D
1	Data	Output	Output	Output
2	45334 Turk Avenue Apt 2031 New York, NY 43254 Car: BMW 3 Series Back Sedan Height 5 3 Eye:Black	45334 Turk Avenue Apt 2031 New York, NY 43254	Car: BMW 3 Series Back Sedan	Height 5 3 Eye:Black
3	543544 Mike Street New York, NY 543565 Car: Toyota White Coupe Height: 6 Feet Eye: Green	543544 Mike Street New York, NY 543565	Car: Toyota White Coupe	Height: 6 Feet Eye: Green
Sheet3

 

[ESACAWIP]

Thank you jtakw! If anyone can help with a VBA code that would be really great since I have some cells with 20+ "paragraphs" but for now, this is more than enough for me to get my work rolling.

 

[jtakw]

You're welcome, thanks for the feedback.

Hopefully, someone will come along with a VBA code to do the same.

 

[Rick Rothstein]

Give this a try...

Sub SplitOnDoubleLineFeeds()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = 2 To Cells(Rows.Count, "A").End(xlUp).Row
    Cells(R, "B").Resize(, 3) = Split(Cells(R, "A").Value, vbLf & vbLf)
  Next
  Application.ScreenUpdating = True
End Sub

 

[jtakw]

Hi Rick,

I'm not good at VBA at All, so pardon me for asking...
Does this bit Cells(R, "B").Resize(, 3) allows for expanding up to 3 Columns?

Reasons I ask, 1, I don't know
2, OP says he has Strings up to 20 separate substings.