Insert carriage returns at x characters

[mikeymay]

I have some cells that contain text of varying lengths.

I need to put these texts into a comment in a cell which I can perform dynamically.

When entering the comment test, the comment box is autosized but this puts all the text in 1 line of test.

Using the following text as an example - More shizzle than the other one but only works on cranes. This then is the 2nd row of info to check wordwrap.

I want to have a maximum of 25 characters per line. If the 26th character is a space then insert a carriage return at character 26 or find the last space in the first 25 characters and insert the CR here and then ignoring the 1st x characters (up to 25 characters) perform the same until all the texts has the appropriate CRs inserted.

The example text would then look as follows in the comment box -
More shizzle than the
other one but only works
on cranes. This then is
the 2nd row of info to
check wordwrap.

Open to both VBA or formula based solutions.

Thanks

 

[lrobbo314]

Book1
	E	F
100	a bc	abc
Sheet1

Cell Formulas
Range	Formula
E100	E100	=REPLACE(F100,2,0,CHAR(10))

 

[mikeymay]

Not sure exactly what your suggestion is doing Irobbo.

I have tried but all it does is insert a CR at character 2.

 

[lrobbo314]

It's an example. You'll need to nest some formulas and change the position of the insertion values to get the desired result.