Best way to get 200 paragraphs from Word into Excel

[JenniferMurphy]

I have a Word document with a couple hundred short paragraphs. Each paragraph is a verse with 4 lines. Lines 2-4 are separated from the previous line with a linefeed (not a carriage return). That's the arrow pointing down and to the left, not the paragraph mark. Like this:

Line 1(lf)
Line 2(lf)
Line 3(lf)
Line 4(cr)

I would like to import them into a spreadsheet with each verse in a cell so I can add columns with categorization information. I tried copying 3-4 verses to the clipboard and pasting then into a cell, but each line went into a different cell.

Is there an easy way to get each verse in a cell so I have 200 rows with a verse in the cell in column C?

Thanks

 

[JoeMo]

Don't know if this will help without seeing some sample data. Here I've assumed there are 4 verses each of which occupies 4 consecutive cells in col A, with no empty cells between verses. These are restructured in col C with a verse in each of four consecutive cells.

Book1.xlsm
	A	B	C
1	Line1		Line1 Line2 Line3 Line4
2	Line2		Line5 Line6 Line7 Line8
3	Line3		Line9 Line10 Line11 Line12
4	Line4		Line13 Line14 Line15 Line16
5	Line5
6	Line6
7	Line7
8	Line8
9	Line9
10	Line10
11	Line11
12	Line12
13	Line13
14	Line14
15	Line15
16	Line16
Sheet3

Sub OneVersePerCell()
'assumes each verse is in 4 sequential rows, no blanks between verses
Dim R As Range, ct As Long
Set R = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
Application.ScreenUpdating = False
For i = 1 To R.Rows.Count Step 4
    ct = ct + 1
    Range("C" & ct).Value = Join(Application.Transpose(Range("A" & i, "A" & i + 3)), Chr(10))
Next i
Application.ScreenUpdating = True
End Sub

 

[JenniferMurphy]

So you are suggesting that I go ahead and paste the text into Excel with each line going into a separate cell, then run your macro to join then together again?

 

[JoeMo]

So you are suggesting that I go ahead and paste the text into Excel with each line going into a separate cell, then run your macro to join then together again?

Yes. If that's suitable, then maybe someone can help with automating the import of the 200 verses to Excel.

 

[JenniferMurphy]

Hey, it works!

I did have to add a Dim statement for i.

Thanks

 

[JoeMo]

Hey, it works!

I did have to add a Dim statement for i.

Thanks

You are welcome - thanks for the reply.