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
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