Split sentence into 2 cells

sgm1911

New Member
Joined
Jul 10, 2017
Messages
10
I got a cell with the text:

THIS IS AN EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE GO TO NEXT ROW

How would I get excel to split the sentence above into 2 different cells A1 & A2, after the sentence exceeds 66 characters
So that-
A1: THIS IS AN EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE
A2: GO TO NEXT ROW

--------------------------------------------

Additionally, if the cell is 67 characters: THIS IS AN EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLEE
How would I split to to 2 different cells so the words dont get cut off half way?
A1: THIS IS AN EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE EXAMPLE
A2: EXAMPLEE
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

VBA Geek

MrExcel MVP
Joined
Dec 16, 2013
Messages
2,857
try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style="color: #333333;;">Dudeeeee Excel Rocks you can do so much with it lolllll whats up ya wanna go to the beach today</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style="background-color: #FCD5B4;;">Dudeeeee Excel Rocks you can do so much with it lolllll whats up</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style="background-color: #FCD5B4;;">ya wanna go to the beach today</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A4</th><td style="text-align:left">=IF(<font color="Blue">LEN(<font color="Red">A1</font>)<67,A1,LEFT(<font color="Red">A1,FIND(<font color="Green">"@",SUBSTITUTE(<font color="Purple">LEFT(<font color="Teal">A1,67</font>)," ","@",LEN(<font color="Teal">LEFT(<font color="#FF00FF">A1,67</font>)</font>)-LEN(<font color="Teal">SUBSTITUTE(<font color="#FF00FF">LEFT(<font color="Navy">A1,67</font>)," ",""</font>)</font>)</font>)</font>)-1</font>)</font>)</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">A5</th><td style="text-align:left">=MID(<font color="Blue">A1,LEN(<font color="Red">A4</font>)+2,255</font>)</td></tr></tbody></table></td></tr></table><br />
 

sgm1911

New Member
Joined
Jul 10, 2017
Messages
10
try:

ABCDEFGHIJK
1Dudeeeee Excel Rocks you can do so much with it lolllll whats up ya wanna go to the beach today
2
3
4Dudeeeee Excel Rocks you can do so much with it lolllll whats up
5ya wanna go to the beach today

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
A4=IF(LEN(A1)<67,A1,LEFT(A1,FIND("@",SUBSTITUTE(LEFT(A1,67)," ","@",LEN(LEFT(A1,67))-LEN(SUBSTITUTE(LEFT(A1,67)," ",""))))-1))
A5=MID(A1,LEN(A4)+2,255)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Thanks ! Works perfectly !
 

sgm1911

New Member
Joined
Jul 10, 2017
Messages
10
try:

ABCDEFGHIJK
1Dudeeeee Excel Rocks you can do so much with it lolllll whats up ya wanna go to the beach today
2
3
4Dudeeeee Excel Rocks you can do so much with it lolllll whats up
5ya wanna go to the beach today

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3

Worksheet Formulas
CellFormula
A4=IF(LEN(A1)<67,A1,LEFT(A1,FIND("@",SUBSTITUTE(LEFT(A1,67)," ","@",LEN(LEFT(A1,67))-LEN(SUBSTITUTE(LEFT(A1,67)," ",""))))-1))
A5=MID(A1,LEN(A4)+2,255)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

Thanks ! I have a problem now, because sometimes the sentence goes into 3 lines.
So how would I have the same formulas, for cells A1 & A2 & A3, after the sentence exceeds 66 characters for A1 and A2?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,935
Messages
5,598,950
Members
414,268
Latest member
bluebandersnatch

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
Top