Breaking up long lines of text

richardthelionheart

Board Regular
Joined
Mar 25, 2016
Messages
95
Office Version
  1. 2016
Platform
  1. Windows

<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.
 
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]
 
Last edited:
Upvote 0
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?
 
Upvote 0
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]
 
Last edited:
Upvote 0
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
 
Upvote 0
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
AB
1SLICK 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
RangeFormula
B1=SUBSTITUTE(A1,". ","."&CHAR(10))


I'll post the 2nd version in a separate post.
 
Upvote 0
2nd formula solution if you want the Text string broken up by sentence into individual rows:


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

Forum statistics

Threads
1,226,771
Messages
6,192,926
Members
453,767
Latest member
922aloose

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top