Breaking up long lines of text

[richardthelionheart]

<colgroup><col width="64"></colgroup><tbody>
[TD="width: 64"]SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB. Safe beginner with a mix of speed and strength and set to maintain the perfect record. One to beat. MILEY NISMO (3) is the main danger. Speedy ***** who led throughout in 29.78 last week. ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.
[/TD]

</tbody>

When I copy and paste this text from the greyhound site, I get just one row of text with a number of sentences. (Not like it turned out above, have no idea what happened there !) Anyhow, imagining that all this text above is in one continuous horizontal line, what I'm trying to do is this. I want to ' break it up ' into sentences that are shown on consecutive rows. like this -

<colgroup><col style="width:48pt" width="64" span="10"> </colgroup><tbody>
[TD="width: 640, colspan: 10"]SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51
PB.
[/TD]

[TD="colspan: 9"]Safe beginner with a mix of speed and strength and set to maintain the perfect record. [/TD]

[TD="colspan: 2"]One to beat.[/TD]

[TD="colspan: 4"]MILEY NISMO (3) is the main danger.[/TD]

[TD="colspan: 6"]Speedy ***** who led throughout in 29.78 last week.[/TD]

[TD="colspan: 7"]ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.[/TD]

</tbody>

(Still having problems demonstrating) But there should be 6 rows here, which I have to just imagine now ! Anyhow I'm trying to ' read' the one line of text and ask excel to find the full stops/periods and then put each sentence on a separate line.

I have no idea how to do this or even if it's possible

Any help will be gratefully received.

 

[fhqwgads]

Alt Enter?

 

[sandy666]

use [code] your text here [/code]

and you cannot use full stop to split this line like you want because of: 29.51

if full stop:

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29[/td][/tr]

[tr=bgcolor:#FFFFFF][td]51 PB[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA] Safe beginner with a mix of speed and strength and set to maintain the perfect record[/td][/tr]

[tr=bgcolor:#FFFFFF][td] One to beat[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA] MILEY NISMO (3) is the main danger[/td][/tr]

[tr=bgcolor:#FFFFFF][td] Speedy ***** who led throughout in 29[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]78 last week[/td][/tr]

[tr=bgcolor:#FFFFFF][td] ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA][/td][/tr]
[/table]

 

[jtakw]

Hi,

Are you saying you want it broken up by sentence in a new row WITHIN the same cell, Or each sentence in a New Separate cell?

 

[sandy666]

too late to edit post#3

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#70AD47]Column1[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E2EFDA]SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29
51 PB
Safe beginner with a mix of speed and strength and set to maintain the perfect record
One to beat
MILEY NISMO (3) is the main danger
Speedy ***** who led throughout in 29
78 last week
ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims
[/td][/tr]
[/table]

 

[fhqwgads]

oh sorry i didnt read your post all the way through
if you want to put the text on separate lines within the same cell you can try select the cell with your one long line of text, press ctrl + H to open the find/replace window, in the "find what" put in ". " without quotations, and in "replace with" put "." and press ctrl +J to put a line break

hope that helped

 

[jtakw]

Not knowing which version you want as I asked in Post # 4, I'll post both formula solutions based on your sample.

First, if you want the Text string broken up by sentence WITHIN the same cell (Don't forget to widen the cell and "wrap text"):

Book1
	A	B
1	SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB. Safe beginner with a mix of speed and strength and set to maintain the perfect record. One to beat. MILEY NISMO (3) is the main danger. Speedy ***** who led throughout in 29.78 last week. ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.	SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB. Safe beginner with a mix of speed and strength and set to maintain the perfect record. One to beat. MILEY NISMO (3) is the main danger. Speedy ***** who led throughout in 29.78 last week. ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.
Sheet497

Cell Formulas
Range	Formula
B1	=SUBSTITUTE(A1,". ","."&CHAR(10))

I'll post the 2nd version in a separate post.

 

[jtakw]

2nd formula solution if you want the Text string broken up by sentence into individual rows:

Book1
	A
1	SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB. Safe beginner with a mix of speed and strength and set to maintain the perfect record. One to beat. MILEY NISMO (3) is the main danger. Speedy ***** who led throughout in 29.78 last week. ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.
2	SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB.
3	Safe beginner with a mix of speed and strength and set to maintain the perfect record.
4	One to beat.
5	MILEY NISMO (3) is the main danger.
6	Speedy ***** who led throughout in 29.78 last week.
7	ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.
Sheet497

Cell Formulas
Range	Formula
A2	=TRIM(MID(SUBSTITUTE(A$1,". ","."&REPT(" ",LEN(A$1))),(ROWS(A$2:A2)-1)*LEN(A$1)+1,LEN(A$1)))

Formula copied down.

 

[richardthelionheart]

2nd formula solution if you want the Text string broken up by sentence into individual rows:

	A
SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB. Safe beginner with a mix of speed and strength and set to maintain the perfect record. One to beat. MILEY NISMO (3) is the main danger. Speedy ***** who led throughout in 29.78 last week. ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.
SLICK BUDDY (7) is an exciting prospect and unbeaten in 3 starts this T/D with a blinding 29.51 PB.
Safe beginner with a mix of speed and strength and set to maintain the perfect record.
One to beat.
MILEY NISMO (3) is the main danger.
Speedy ***** who led throughout in 29.78 last week.
ALL AMERICAN BOY (8) and PRINCESS ZESTA (6) have place claims.

<colgroup><col style="width: 25pxpx"><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

</tbody>

Sheet497​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]A2[/TH]
[TD="align: left"]=TRIM(MID(SUBSTITUTE(A$1,". ","."&REPT(" ",LEN(A$1))),(ROWS(A$2:A2)-1)*LEN(A$1)+1,LEN(A$1)))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



Formula copied down.

Thank you for all the replies. jtakw has the answer I need (above) which seems to work the best. Thank you to all who took the time to solve the problem.

 

[jtakw]

You're welcome, glad we can help.