Split cell of text into 4 columns without breaking words.

[Jammyjt]

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

 

[Peter_SSs]

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
	A	B	C	D	E
1
2	Sometimes the formula works fine but there are other times when it does not	Sometimes the formula	works fine but there are	other times when it does	not
3	One	One
4	If there is a very long sentence with lots of words then all the remaining words fall into column E	If there is a very long	sentence with lots of	words then all the	remaining words fall into column E
Split Text

Cell Formulas
Range	Formula
B2:B4	B2	=LET(seq,SEQUENCE(,26),LEFT(A2,AGGREGATE(14,6,seq/(MID(A2&" ",seq,1)=" "),1)-1))
C2:C4	C2	=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:D4	D2	=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:E4	E2	=MID(A2,LEN(B2&C2&D2)+4,LEN(A2))

 

[Jammyjt]

Hi Peter,

Thank you for the prompt reply and useful introductory links.

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]

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
	A	B	C	D	E
1
2	Sometimes the formula works fine but there are other times when it does not	Sometimes the formula	works fine but there are	other times when it does	not
3	One	One
4	If there is a very long sentence with lots of words then all the remaining words fall into column E	If there is a very long	sentence with lots of	words then all the	remaining words fall into column E
Split Text (2)

Cell Formulas
Range	Formula
B2:B4	B2	=LEFT(A2,AGGREGATE(14,6,SEQUENCE(,26)/(MID(A2&" ",SEQUENCE(,26),1)=" "),1)-1)
C2:C4	C2	=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:D4	D2	=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:E4	E2	=MID(A2,LEN(B2&C2&D2)+4,LEN(A2))

 

[Jammyjt]

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]

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]

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]

Cheers.

 

[millawitch]

I was using a workbook by Ashish Mathur, it has served me well. Until I noticed it cuts off before reaching 50 characters, so I had a go at updating the formulas for Microsoft 365.

* Can handle CHAR(160)
* Limitations: hyphenated words

Cell A2: text to split
Cell B7: X characters limit
Cell B2 (Up to first X characters):
=TEXTBEFORE(LEFT(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ",$B$7+1)," ",-1)

Cell C2 and beyond - can be dragged (Up to next X characters):
=IFERROR(TEXTBEFORE(LEFT(RIGHT(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ",LEN(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ")-SUM(LEN($B2:B2&" "))),$B$7+1)," ",-1),"")

Hope this helps!

 

[millawitch]

I was using a workbook by Ashish Mathur, it has served me well. Until I noticed it cuts off before reaching 50 characters, so I had a go at updating the formulas for Microsoft 365.

* Can handle CHAR(160)
* Limitations: hyphenated words

Cell A2: text to split
Cell B7: X characters limit
Cell B2 (Up to first X characters):
=TEXTBEFORE(LEFT(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ",$B$7+1)," ",-1)

Cell C2 and beyond - can be dragged (Up to next X characters):
=IFERROR(TEXTBEFORE(LEFT(RIGHT(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ",LEN(TRIM(CLEAN(SUBSTITUTE(SUBSTITUTE($A$2,CHAR(160)," "),CHAR(10)," ")))&" ")-SUM(LEN($B2:B2&" "))),$B$7+1)," ",-1),"")

Hope this helps!

~ Edit: ~

** Can also handle CHAR(10)
** Hyphenated words, if the second part is separated from the first by line feed/carriage return

Uses: My particular use is for breaking down large sentences into 50 characters segments for input into accounting system with a character count limit

P.S. I ran a few tests and, although I can't explain why, TRIM before CLEAN removed more non-printing characters from my sample texts. I tried researching for an explanation, but couldn't find anything, so I have used them in this order for good measure.

P.S. 2 Not sure if the cleanest/most concise/elegant formulas, but hey - they're working so far. I was particularly pleased with being able to drag the Up to next X characters
as the previous worksheet I was using (with older functions) had different formulas for each new column, making it harder to maintain if there was a need to expand the return columns.