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

[fallore]

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:

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.

<colgroup><col width="1394"></colgroup><tbody>
</tbody>

 

[dmt32]

Hi,
have look here:http://meadinkent.co.uk/xl-split-text.htm

and see if does what you want.

Dave.

 

[JLGWhiz]

In case the link does not do it, try this. Assuming all text is in column N.

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