Insert a line break after every 42 characters

[Rajkiran557]

Hi All,


I need a formula to insert a line break after every 42 characters in a cell , example if there is 266 characters in a cell there should be 6(266/42)line breaks.


=REPLACE(REPLACE(A1,42,0,CHAR(10)),84,0,CHAR(10))


I tried this but i am not sure how to get the start num to change to 126,168 and so on


Any help is highly appreciated


Thanks

 

[Rick Rothstein]

Hi All,


I need a formula to insert a line break after every 42 characters in a cell , example if there is 266 characters in a cell there should be 6(266/42)line breaks.


=REPLACE(REPLACE(A1,42,0,CHAR(10)),84,0,CHAR(10))


I tried this but i am not sure how to get the start num to change to 126,168 and so on

I am not sure you will be able to do this with a formula, but there is a VBA coded method available, see my mini-blog article here...

<!-- title / author block -->Wrap Text On Spaces Up To A Maximum Number Of Characters Per Line

 

[FDibbins]

cross-posted here Insert a line break (CHAR(10)) after every 42 characters

 

[hiker95]

A message to forum cross posters

Please read this
Excelguru Help Site - A message to forum cross posters

 

[Rajkiran557]

Sorry I was not aware about that...

 

[Rajkiran557]

Thanks for the tip , it was very helpful..