Break a character string in a cell

When a long character string is entered in the cell,
If you do not break the line, width of the cell will be longer.
It is difficult to see it if the width is too long,
So make line breaks within the cell to reduce the width.

Line feed within a cell by manual input,
It can be done with "Alt" + "Enter".

here,
I will show you how to break a character string
every 5 characters with VBA.
We use the Chr function to add a newline to a string.
Line breaks are equivalent to Chr (10).
Add this between strings.

To recognize character strings every five characters,
We use Mid function and For statement.

Code here

Sub macro180602a()
'Break a character string
'Every 5 characters

    Dim i As Integer
    Dim c As Object
    Dim Str As String
    Dim Rng As Range
    Dim num As Integer
    num = 5 'word count
    Set Rng = ActiveSheet.Range("A1:A2") 'range
   
    For Each c In Rng
        Str = ""
        For i = 1 To Len(c.Text) Step num
            Str = Str & Mid(c, i, num) & Chr(10)
        Next i
        Cells(c.Row, c.Column) = Str
    Next c
   
End Sub

Execute the above code in the state of the image below

Result：

Regardless of half size and full width,
A line feed was made every five characters.

With the above code
The number of characters to be broken
is specified with the variable "num".
The range is specified by the variable "rng".
By changing these two variables
You can change the number of characters
and the range of cells.