Cut and paste paragraphs in new line

[Mosamba]

Guys I have a spreadsheet which is full of lines, each one populated by many paragraphs separated by full stops.
I need to cut each of these paragraphs and paste them into individual lines.

Appreciate very much your help.

Thank you

 

[Peter_SSs]

Description is pretty vague in terms of layout and just what the data is like. Also, you refer to paragraphs separated by full stops where normally it is sentences that are separated by full stops.

Anyway, see if this might get you started. If not, a small selection of sample data and expected results might help.

Starting with the data in column A in the screen shot below, the code produces the results shown in the other columns. I have assumed that each sentence is separated by a full stop and a space.

Sub SplitSentences()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    .Replace What:=". ", Replacement:=".#", LookAt:=xlPart
    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="#"
  End With
End Sub

Excel Workbook
	A	B	C	D	E	F
1
2	This cell is full. Of a number. Of short sentences.	This cell is full.	Of a number.	Of short sentences.
3	This one. Has less.	This one.	Has less.
4	This has only one sentence.	This has only one sentence.
5	Again. This cell is full. Of a number. Of short sentences.	Again.	This cell is full.	Of a number.	Of short sentences.
Split Sentences

 

[Mosamba]

Description is pretty vague in terms of layout and just what the data is like. Also, you refer to paragraphs separated by full stops where normally it is sentences that are separated by full stops.

Anyway, see if this might get you started. If not, a small selection of sample data and expected results might help.

Starting with the data in column A in the screen shot below, the code produces the results shown in the other columns. I have assumed that each sentence is separated by a full stop and a space.

Sub SplitSentences()
  With Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)
    .Value = .Offset(, -1).Value
    .Replace What:=". ", Replacement:=".#", LookAt:=xlPart
    .TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, Tab:=False, _
      Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="#"
  End With
End Sub

Split Sentences

*	A	B	C	D	E	F
*	*	*	*	*	*
This cell is full. Of a number. Of short sentences.	This cell is full.	Of a number.	Of short sentences.	*	*
This one. Has less.	This one.	Has less.	*	*	*
This *has only one sentence.	This *has only one sentence.	*	*	*	*
Again. This cell is full. Of a number. Of short sentences.	Again.	This cell is full.	Of a number.	Of short sentences.	*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:357px;"><col style="width:193px;"><col style="width:117px;"><col style="width:136px;"><col style="width:139px;"><col style="width:40px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Hello Peter,
You got it right regarding sentences and not paragraphs.
It worked perfectly.
Thanks very much again.
All the best!