Split cell of text into 4 columns without breaking words.

Jammyjt

New Member
Joined
Jul 20, 2021
Messages
4
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

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,673
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
50,673
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

Jammyjt

New Member
Joined
Jul 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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
Joined
May 28, 2005
Messages
50,673
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 20, 2021
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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 :)
 

Forum statistics

Threads
1,141,774
Messages
5,708,458
Members
421,570
Latest member
BaileyJ

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