Search code examples
exceltextcellline-breaks

Auto line break in cell every X characters

I have a huge cell containing text which is without line breaks. Once I need to copy&paste it I use online auto line breaker tool which makes new break lines every X characters.

I'm looking for solution how to make this work in Excel.

Solution

  • This may not be practical, depending upon how often required and how many cells are to be split by line at a time, but most things possible with VBA are also possible without, even if impractical.

    Assuming your text is in B1 and the requisite character limit in A1. In C1:

    =MID($B1,1+$A1*(COLUMN()-3),$A1)

    copied across to D1. In C2 =C1, in D2: 

    =C2&CHAR(10)&D1

    then copy D1:D2 across until first blank shows up in Row1 (assumes you won't have a multitude of consecutive space 'characters').

    The result should be below the last non-blank cell in Row1.

    SO33432188 example