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:

https://dl.dropboxusercontent.com/u/...l-WordWrap.jpg

--
Terry, East Grinstead, UK

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

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

Hi Terry,

Am Wed, 19 Aug 2015 17:26:03 +0100 schrieb Terry Pinnell:

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!

press Alt+F11 = Insert = Module and paste the code into the code
window. Then select the cell and run the macro.

What has to be revised? Do you have more than one cell with long text in
one column? I have a suggestion for that case.


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

Hi Terry,

Am Wed, 19 Aug 2015 17:26:03 +0100 schrieb Terry Pinnell:

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!

Look here
https://onedrive.live.com/redir?resi...=folder%2cxlsm
for "CutString"
and download the workbook because macros are disabled in OneDrive.


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

Claus Busch wrote:

Hi Terry,

Am Wed, 19 Aug 2015 17:26:03 +0100 schrieb Terry Pinnell:

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!

press Alt+F11 = Insert = Module and paste the code into the code
window. Then select the cell and run the macro.

What has to be revised? Do you have more than one cell with long text in
one column? I have a suggestion for that case.


Regards
Claus B.

Thanks very much, Claus. That works a treat. And your multiple line
version too.

Never seen an XLSM file before - very handy. This page, the first I
found with a google search, seemed to imply my ancient version of
Excel wouldn't work, but it did:
http://pcsupport.about.com/od/fileex...f/xlsmfile.htm appeared

What has to be revised? Well, just about everything!

My 80 or so macros were created over 20 years ago, and I haven't
really worked with macros for at least 15 years. I see my 'Excel 3
User Guide' was published in 1990.

I did initially check whether I already had a suitable macro and found
one named 'Wrap' as you see he
https://dl.dropboxusercontent.com/u/...ldMacros-1.jpg
But that simply applies Format Cells Alignment Wrap cell.

After following your instructions, I was unsure how to proceed beyond
this stage:
https://dl.dropboxusercontent.com/u/...l-Macros-2.jpg

Anyway, one way or another, it worked and solved the immediate task.
I'll avoid the strong temptation to interrupt my current project (I'm
making a family DVD) to re-study Excel macros. But as a result of your
help I will at least be able to successfully copy/paste if necessary
for the time being!

--
Terry, East Grinstead, UK

Hi Terry,

Am Fri, 21 Aug 2015 15:07:55 +0100 schrieb Terry Pinnell:

Thanks very much, Claus. That works a treat. And your multiple line
version too.

always glad to help. Appreciate your feedback.

Never seen an XLSM file before - very handy. This page, the first I
found with a google search, seemed to imply my ancient version of
Excel wouldn't work, but it did:
http://pcsupport.about.com/od/fileex...f/xlsmfile.htm appeared

I don't know which Excel version you use. Since 2007 a workbook with
macros has to be saved as .xlsm or .xlsb
If you use an older version macros run in .xls

After following your instructions, I was unsure how to proceed beyond
this stage:
https://dl.dropboxusercontent.com/u/...l-Macros-2.jpg

Anyway, one way or another, it worked and solved the immediate task.
I'll avoid the strong temptation to interrupt my current project (I'm
making a family DVD) to re-study Excel macros. But as a result of your
help I will at least be able to successfully copy/paste if necessary
for the time being!

If you need the macro only in this workbook select it in the Project
Explorer with right click and choose Insert = Module. The module gets a
running number. You can also paste the code in an existing module.
It will be saved when the workbook is saved.
If you put the code into the PERSONL.xlx the macro is available in all
workbooks.


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