Macro to wrap text with hard returns?

zrog

New Member
Joined
Oct 22, 2009
Messages
4
It's a bit surprising, but I couldn't find any macro doing this.
I would like to wrap text inside cells with a hard return (like typing alt-Enter) after 40 characters (or less if the 40th falls in the middle of a word) using Excel 2003.

Starting cells (no hard returns)
Lorem ipsum dolor sit amet, consectetur adipiscing elit. Curabitur condimentum elementum ornare. Pellentesque habitant morbi tristique senectus

Cell after macro (hard returns in)
Lorem ipsum dolor sit amet, consectetur
adipiscing elit. Curabitur condimentum
elementum ornare. Pellentesque habitant
morbi tristique senectus

Do you have any suggestions? Thank you
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Try:

Code:
Sub Test()
    Const WrapAt As Integer = 40
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim i As Integer
    Dim Temp As String
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1")
    For Each Cell In Rng
        With Cell
            Temp = .Value
            Do
                i = i + WrapAt
                Do
                    If Mid(Temp, i, 1) = " " Then
                        Temp = Left(Temp, i - 1) & Chr(10) & Right(Temp, Len(Temp) - i)
                        Exit Do
                    Else
                        i = i - 1
                    End If
                Loop
            Loop While i < Len(Temp) - WrapAt
            .Value = Temp
        End With
    Next Cell
End Sub

Change the worksheet and range references to suit.
 
Upvote 0
Hi Andrew,

Thank you for the script! However, it seem to stop at If Mid(Temp, i, 1) = " " Then for invalid procedure or call.

Could it be due to something on my machine?
 
Upvote 0
I see...

The macro seems to struggle with strings shorter than 40 characters.
If it's shorter than 40 characters and has no spaces, it stops. If it's shorter but has spaces, it will add a return even if that's not needed.

Maybe both issues can be avoided by making the macro ignore strings shorter than 40 characters?

On a side note, if the range is bigger than one cell, the macro seems to stop after the first one.

I hope that's not a complicated issue to fix!
 
Upvote 0
Try:

Code:
Sub Test()
    Const WrapAt As Integer = 40
    Dim Sh As Worksheet
    Dim Rng As Range
    Dim Cell As Range
    Dim i As Integer
    Dim Temp As String
    Set Sh = Worksheets("Sheet1")
    Set Rng = Sh.Range("A1:A10")
    For Each Cell In Rng
        i = 0
        With Cell
            If Len(.Value) > WrapAt Then
                Temp = .Value
                Do
                    i = i + WrapAt
                    Do
                        If Mid(Temp, i, 1) = " " Then
                            Temp = Left(Temp, i - 1) & Chr(10) & Right(Temp, Len(Temp) - i)
                            Exit Do
                        Else
                            i = i - 1
                        End If
                    Loop
                Loop While i < Len(Temp) - WrapAt
                .Value = Temp
            End If
        End With
    Next Cell
End Sub

I don't understand "if the range is bigger than one cell, the macro seems to stop after the first one". The code loops around the range. If you want to use A1:A10 set the range like this:

Set Rng = Sh.Range("A1:A10")
 
Upvote 0

Forum statistics

Threads
1,224,521
Messages
6,179,278
Members
452,902
Latest member
Knuddeluff

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top