Claus Busch wrote:

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.

Thanks Claus, appreciate your help. Not as simple as I'd expected.

For many years I've used my own macro program, Macro Express Pro, for
automating stuff. Haven't touched VBA for a decade or so. Therefore
working out exactly how to paste that VBA macro will need a bit of
revision!

--
Terry, East Grinstead, UK