Insert hard return (chr 10) in specific place

[therobinnorman]

Merry Christmas (eve) to all those viewing my thread.

Here's the deal. I have a worksheet with text in cell A1. How do I alter the cell to only allow 69 characters per line, and then auto-enter a chr10? Everything needs to remain in cell A1, if at all possibly. I need it to not cut off any words, nor enter a hypen. Adjusting the cell length works in excel, but when I copy/paste into my other program the line breaks disappear. VBA or excel functions are fine.

 

[pgc01]

Hi

Not enough information. Can you clarify?

1 - How long will the text be, can it be more than 2 lines?
2 - Does the text already have some linefeeds (chr 10)?
3 - Do you want the text always to be cut at a space character? In that case do you want to replace the space character with a linefeed or to keep the space and add a linefeed?

... or did you have in mind some other set of rules to divide the text?

 

[dmt32]

Hi,
have a look here:Excel VBA to split text around whole words

and see if this helps.


Dave

 

[tonyyy]

You might try the following...

Sub test69()
Dim i As Long, j As Long
Dim r As Range
Set r = Range("A1")
j = Len(r)

For i = 69 To Len(r) Step 69
    If i Mod 69 = 0 And Mid(r, i, 1) = " " Then
        r.Value = Mid(r, 1, i) & Chr(10) & Mid(r, i + 1, j)
    Else
        Do Until Mid(r, i, 1) = " "
            i = i - 1
        Loop
        r.Value = Mid(r, 1, i) & Chr(10) & Mid(r, i + 1, j)
    End If
Next i
End Sub

Cheers,

tonyyy