Split a lengthy paragraph into maximum 50 character lines with whole words

  • I've found the following formula which extracts the first set of words to the limit of 50 characters


    =TRIM(LEFT(LEFT(A1,25),FIND("^^",SUBSTITUTE(LEFT(A1,25)," ","^^",LEN(LEFT(A1,25))-LEN(SUBSTITUTE(LEFT(A1,25)," ",""))))))


    I've managed to adapt this to get the next set of whole words


    =TRIM(MID(A1,(FIND("^^",SUBSTITUTE(LEFT(A1,50)," ","^^",LEN(LEFT(A1,50))-LEN(SUBSTITUTE(LEFT(A1,50)," ",""))))),50))


    My problem is I need formulas further sets of words limited to 50 characters after the 2nd set as I can't get a formula to pull the next set of words. In total I'm trying to extract about 10 lines worth of whole words in total so any help would be greatly appreciated.

  • Re: Split a lengthy paragraph into maximum 50 character lines with whole words


    Quote from pike;794258

    Hi zerachiel76,
    Have a look in the possible answer box at the bottom of this page.
    There is a answer for 40 work split but it will have to be a macro as the formulas will be to long


    Thanks for your reply, I've looked at the macros and I don't know how to implement them within my spreadsheet. I can program macros ok but I cant see how to activate the examples within the spreadsheet.