Friday, January 14, 2011

Wrapping text within a cell in Excel using VBA

The most simplest way to make a cell word wrap is to right click and format the cell alignment to wrap text.  This makes the text begin on the next line within that cell when it reaches the right cell wall.  You increase the size of the cell and more text appears on the first line with less on the next.

There may, from time to time, be a case where you need to begin a new line regardless of the size of that cell.  VB needs to see a carriage return in order to accomplish this.  A carriage return is represented by Chr(10) and may be inserted into the cell directly as follows:

ActiveCell.FormulaR1C1 = "ABCD" & Chr(10) & "EFGH"

Of course, you may want to incorporate this several times based on a specific length.  Here is one way this might be accomplished:

Sub AddCarriageReturns()
' Macro created 1/13/2011 by Steve Wynne
Dim CarriageInsert
Dim FullString As String
Dim I As Integer
I = Len(ActiveCell)
Do Until I = 5
    I = I - 5
    CarriageInsert = Mid(ActiveCell, I, 5)
    FullString = CarriageInsert & Chr(10) & FullString
    ActiveCell = FullString
End Sub

Try to nest this within a for/next or loop statement and it can be applied to an entire range of cells!


Georg Pohl said...

it seems to become more protable to use builtin constants:

vbLf --> Chr(10)
vbCr --> Chr(13)
vbCrLf --> Chr(13) + Chr(10)

to become independant from OS write:
vbNewLine --> Chr(13) + Chr(10) or Chr(13) on Mac

saw1366 said...

Thanks for the feedback Georg!

saw1366 said...

For more about the various methods on carriage returns, see the discussion at