# Split cell of text into 4 columns without breaking words.

#### Jammyjt

##### New Member
Hello all,

I have recently been trying to develop a function that can split a cell of text (varying in character length up to a maximum of 100). The first 3 columns must have a maximum of 25 characters whilst the last I am hoping to dump the remainder of the text (this allows me to see how many characters I have gone over so the original text can be edited accordingly to fit).

I have been using the following thread for help: Split a Column into 2, use a character limit that does not cut words in half.

I have been able to adjust the formula to get some kind of results but they vary. Sometimes I get the desired result and other times I don't. My functions are as seen below with a screenshot of some of the results:

B1:B3 - =IF(LEN(A1)<=25,A1,LEFT(A1,FIND("#",SUBSTITUTE(LEFT(A1,26)," ","#",LEN(LEFT(A1,26))-LEN(SUBSTITUTE(LEFT(A1,26)," ",""))))-1))

C1:C3 - =IF(LEN(A1)<=51,REPLACE(A1,1,LEN(B1)+1,""),LEFT(REPLACE(A1,1,LEN(B1)+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),26)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1)+1,""),26))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1)+1,""),26)," ",""))))-1))

D1:D3 - =IF(LEN(A1)<=76,REPLACE(A1,1,LEN(B1&C1&" ")+1,""),LEFT(REPLACE(A1,1,LEN(B1&C1&" ")+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1&C1)+1,""),26)," ","#",LEN(LEFT(REPLACE(A1,1,LEN(B1&C1)+1,""),26))-LEN(SUBSTITUTE(LEFT(REPLACE(A1,1,LEN(B1&C1)+1,""),26)," ",""))))-1))

E1:E3 - =TRIM(SUBSTITUTE(A1,TRIM(B1&" "&C1&" "&D1&" "),"")) Please note, the orange highlighted cell indicates that the cell is over the 25 character limit. In cell E3, this is acceptable as I am expecting the text to overflow here so I can edit it. However, on Row 2, I was expecting D2 to split so that there's only a maximum of 25 characters.

Unfortunately I am unable to use macros/VBA's due to work restrictions so trying to generate a solution with formulas. Any help would be greatly appreciated, thank you!

Kind Regards,

Jamie

### Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Do you have the LET function in your 365?
Is this what you want?

21 07 20.xlsm
ABCDE
1
2Sometimes the formula works fine but there are other times when it does notSometimes the formulaworks fine but there areother times when it doesnot
3OneOne
4If there is a very long sentence with lots of words then all the remaining words fall into column EIf there is a very longsentence with lots ofwords then all theremaining words fall into column E
Split Text
Cell Formulas
RangeFormula
B2:B4B2=LET(seq,SEQUENCE(,26),LEFT(A2,AGGREGATE(14,6,seq/(MID(A2&" ",seq,1)=" "),1)-1))
C2:C4C2=LET(txt,MID(A2,LEN(B2)+2,LEN(A2)),seq,SEQUENCE(,26),LEFT(txt,AGGREGATE(14,6,seq/(MID(txt&" ",seq,1)=" "),1)-1))
D2:D4D2=LET(txt,MID(A2,LEN(B2&C2)+3,LEN(A2)),seq,SEQUENCE(,26),LEFT(txt,AGGREGATE(14,6,seq/(MID(txt&" ",seq,1)=" "),1)-1))
E2:E4E2=MID(A2,LEN(B2&C2&D2)+4,LEN(A2))

#### Jammyjt

##### New Member
Hi Peter,

Unfortunately the 365 version I use is limited and the LET function isn't recognised. I am currently trying to understand if we can update the version to use that function. Thank you for suggesting that solution though.

I have been trying to work around the limitation of not having that function and using the formulas I have provided in my initial post. I am curious why it works for some lines and not others. Is there any other solution you can think of not utilising the LET function in the circumstance I'm unable to get an updated version?

Thanks,

Jamie

#### Peter_SSs

##### MrExcel MVP, Moderator
the LET function isn't recognised.
That does not matter. Any formula using the LET function can be re-written without the LET function. The formulas just tend to be a bit longer. 21 07 20.xlsm
ABCDE
1
2Sometimes the formula works fine but there are other times when it does notSometimes the formulaworks fine but there areother times when it doesnot
3OneOne
4If there is a very long sentence with lots of words then all the remaining words fall into column EIf there is a very longsentence with lots ofwords then all theremaining words fall into column E
Split Text (2)
Cell Formulas
RangeFormula
B2:B4B2=LEFT(A2,AGGREGATE(14,6,SEQUENCE(,26)/(MID(A2&" ",SEQUENCE(,26),1)=" "),1)-1)
C2:C4C2=LEFT(MID(A2,LEN(B2)+2,LEN(A2)),AGGREGATE(14,6,SEQUENCE(,26)/(MID(MID(A2,LEN(B2)+2,LEN(A2))&" ",SEQUENCE(,26),1)=" "),1)-1)
D2:D4D2=LEFT(MID(A2,LEN(B2&C2)+3,LEN(A2)),AGGREGATE(14,6,SEQUENCE(,26)/(MID(MID(A2,LEN(B2&C2)+3,LEN(A2))&" ",SEQUENCE(,26),1)=" "),1)-1)
E2:E4E2=MID(A2,LEN(B2&C2&D2)+4,LEN(A2))

• Jammyjt

#### Jammyjt

##### New Member

Hi Peter,

That works perfectly, thank you very much!

I'm just curious if you know why the initial code I posted works for some lines and not others?

Thanks,

Jamie

#### Peter_SSs

##### MrExcel MVP, Moderator
That works perfectly, thank you very much!
You're welcome. I'm just curious if you know why the initial code I posted works for some lines and not others?
I have not studied the formulas in great detail but I can see for example with the formula in your D2:
=IF(LEN(A2)<=76,REPLACE(A2,1,LEN(B2&C2&" ")+1,""),LEFT(REPLACE(A2,1,LEN(B2&C2&" ")+1,""),FIND("#",SUBSTITUTE(LEFT(REPLACE(A2,1,LEN(B2&C2)+1,""),26)," ","#",LEN(LEFT(REPLACE(A2,1,LEN(B2&C2)+1,""),26))-LEN(SUBSTITUTE(LEFT(REPLACE(A2,1,LEN(B2&C2)+1,""),26)," ",""))))-1))

I think with that first part you are assuming that columns B & C will have used their full 25 characters. However, because words are not being broken B2 is 21 characters and C2 is 24 characters. That has only used 45 (+ 2 spaces) = 47 characters. A2 is 75 characters so D2 formula says to replace the first 47 which leaves 28 characters in D2.

#### Jammyjt

##### New Member
That makes sense actually. I did try playing around with that 76 and noticed different results but nothing that did exactly what I needed. Thanks for taking the time to run through that though and explaining it, helps me a lot #### Peter_SSs

##### MrExcel MVP, Moderator
Cheers. Replies
5
Views
166
Replies
3
Views
184
Replies
4
Views
57
Replies
9
Views
190
Replies
3
Views
75

### Forum statistics

1,141,768
Messages
5,708,416
Members
421,567
Latest member
vicpinto1970 ### 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.

### Which adblocker are you using? 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

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
{"ft_lang_label":"__label__en","ft_lang_prob":0.89634925,"math_prob":0.9514586,"size":3832,"snap":"2021-31-2021-39","text_gpt3_token_len":1198,"char_repetition_ratio":0.16509926,"word_repetition_ratio":0.9878049,"special_character_ratio":0.35229644,"punctuation_ratio":0.20176406,"nsfw_num_words":0,"has_unicode_error":false,"math_prob_llama3":0.9695693,"pos_list":[0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30],"im_url_duplicate_count":[null,1,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null],"WARC_HEADER":"{\"WARC-Type\":\"response\",\"WARC-Date\":\"2021-09-27T12:28:52Z\",\"WARC-Record-ID\":\"<urn:uuid:a1422862-0690-4da1-a1ef-c285d03a7a24>\",\"Content-Length\":\"133005\",\"Content-Type\":\"application/http; msgtype=response\",\"WARC-Warcinfo-ID\":\"<urn:uuid:27499a22-42ba-44d2-8950-707a2f92a7d4>\",\"WARC-Concurrent-To\":\"<urn:uuid:bcb7da04-4c81-4c39-986f-c70be25831ac>\",\"WARC-IP-Address\":\"216.92.17.166\",\"WARC-Target-URI\":\"https://www.mrexcel.com/board/threads/split-cell-of-text-into-4-columns-without-breaking-words.1176899/#post-5724399\",\"WARC-Payload-Digest\":\"sha1:BE5WTARAO363KVTKRTWBP6UFEVIGZ6TF\",\"WARC-Block-Digest\":\"sha1:72FGUSDNLGCMBK37X5QU3WU7TEITRL5A\",\"WARC-Identified-Payload-Type\":\"text/html\",\"warc_filename\":\"/cc_download/warc_2021/CC-MAIN-2021-39/CC-MAIN-2021-39_segments_1631780058450.44_warc_CC-MAIN-20210927120736-20210927150736-00105.warc.gz\"}"}