Extract specific number of characters from an alphanumeric string without breaking any word


When downloading data from an ERP into Excel, a Remarks column has entries which can be as lengthy as 300 characters per cell.  Before uploading this data into another database, entries in the Remarks column need to be split into multiple cells of upto 50 characters per cell.  Furthermore, when splitting data into multiple cells, it should be ensured that no word gets split/broken.  So if the 50th character is alphabet k of the word Like, then that cell should hold words till the word which appears just before Like so that the length does not exceed 50 characters.

You may refer to my solution in the attached workbook.

Leave a Comment

Your email address will not be published.


  • Worked as described – was able to chop up a string into columns of 40 chars without splitting words – Nice 🙂

  • How in the world did you make that formula!!! it is amazing. I had a list of 1400 items with cells up to 500 chars to distribute to 100 chars maximum and this worked perfectly… I just edited the formula to change the cells ref and voila…. Thank you so much…. I can’t believe that formula haha…. I wanna learn how to make formulas like that!