Split cell of text into 4 columns without breaking words.

Jammyjt

New Member
Joined
Jul 20, 2021
Messages
7
Office Version
  1. 365
Platform
  1. Windows
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&" "),""))

1626779852804.png


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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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))
 
Upvote 0
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
 
Upvote 0
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))
 
Upvote 0
Solution
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
 
Upvote 0
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.
 
Upvote 0
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 :)
 
Upvote 0
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!
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

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