Macro to wrap text with hard returns?

Z

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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

A
  • Solved
Fixing the macro for text splitting (rows being skipped)
Replies
6
Views
448
Georgiboy
Georgiboy
S
  • Solved
Remove all class names in HTML-tags
Replies
5
Views
825
strooman
S
M
  • Solved
Fill Color Based on Condition
Replies
7
Views
386
mumps
mumps
IIII
  • Question Question
[VBA]: Delete empty rows from multiple tables and then print the range to pdf
Replies
3
Views
614
OaklandJim
O
B
  • Solved
Pull names that left messages
Replies
3
Views
260
Fluff
Fluff

Forum statistics

Threads
1,223,957
Messages
6,175,623
Members
452,661
Latest member
Nonhle

Share this page

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