Splitting long text cells into multiple without breaking words and no delimiter?

fallore

New Member
Joined
Sep 30, 2016
Messages
3
Hey Guys,

I've done a fair bit of googling and either I'm missing a key word/concept to get results or the answer is just great at hiding. What I would like to do is take a column of messages that are between 60 and 300 characters long, and split them into multiple cells with a max character length of 60, and do it in a way where it splits them at spaces and not in the middle of the words. I don't know enough to know if that's a tall order. I've tried using Kutools, both to split per x characters and to add a delimiter per x characters, but it doesn't have a way to do it "intelligently" to not split words up (as far as I can tell.) I thought maybe in theory I could do some Left(cell,find(" ",cell) stuff with concatenate to put delimiters in next to spaces, but I'm really struggling to actually execute it. Here is a random message in N17 to give you an example of what I'm dealing with:


<colgroup><col width="1394"></colgroup><tbody>
[TD="width: 1394"]Ed, I'm glad you and your son like this! I appreciate your sense of humor and humanity. -- Gary

Ideally it would split it at at a space somewhere around 50-60 characters and put the excess into the cell(s) on the right of this one. Is this possible? I feel like I've been missing some very easy way to do it for a while now, and I'd really appreciate some help.
[/TD]

</tbody>
 

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.
In case the link does not do it, try this. Assuming all text is in column N.

Code:
Sub t()
Dim spl As Variant, r As Range, i As Long, txt As String
With ActiveSheet
    For Each r In Range("N2", .Cells(Rows.Count, 14).End(xlUp))
        spl = Split(r.Value, " ")
        For i = LBound(spl) To UBound(spl)
            txt = txt & " " & spl(i)
            If Len(txt) >= 60 And i < UBound(spl) Then
                .Cells(r.Row, Columns.Count).End(xlToLeft).Offset(, 1) = txt
                txt = ""
            ElseIf Len(txt) < 60 And i >= UBound(spl) Then
                .Cells(r.Row, Columns.Count).End(xlToLeft).Offset(, 1) = txt
                txt = ""
            End If
        Next
    Next
End With
End Sub
 
Last edited:
Upvote 0

Forum statistics

Threads
1,223,896
Messages
6,175,262
Members
452,627
Latest member
KitkatToby

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