Hi Terry,

Am Mon, 17 Aug 2015 19:23:37 +0100 schrieb Terry Pinnell:

I'm sure this one too will prove embarrassingly simple. I've forgotten
how to automate the process of splitting a long text entry into
several lines. Best shown by illustration:

select the cell with the long text and run following macro:

Sub Wrap()
Dim varText() As Variant
Dim i As Long, n As Long, maxChr As Long
Dim myStr As String

myStr = Selection.Value
'Maximum of characters in one line
maxChr = 80

Do
ReDim Preserve varText(n)
If Len(myStr) <= maxChr Then
varText(n) = myStr
myStr = Replace(myStr, varText(n), "")
Else
For i = maxChr To maxChr - 10 Step -1
If InStr(i, myStr, " ") Then
varText(n) = Left(myStr, InStr(i, myStr, " "))
n = n + 1
Exit For
End If
Next
End If
myStr = Replace(myStr, varText(n - 1), "")
Loop While Len(myStr) 0

Selection.Resize(n + 1) = Application.Transpose(varText)
End Sub


Regards
Claus B.
--
Vista Ultimate / Windows7
Office 2007 Ultimate / 2010 Professional