On Wed, 27 Apr 2011 18:01:56 -0700 (PDT), Miguel wrote:

Good morning,

I am currently working with Excel 07 on windows XP Professional and i
ran into an issue a couple of days back on a spreadsheet that contains
mainly text responses inside of the cells.

I am trying to create a subroutine that recognizes the cells that
exceed an X number of characters ( which I understand that the
character length varies depending on the cell pixel size – we are
using a standard of 546 pixels height by 442 pixels width = about 2170
characters) and takes the overflow of such cell, adds a new row under
the cell and pastes that overflow into it (of course, the formatting
of this new cell still fits 2170 characters in a 546x442 pixels). This
process is done iteratively throughout the entire sheet.

So for example, the text in A5 contains 8000 characters. It will take
the first 2170 characters, leave them in A5, insert a row right
underneath if the space is not used (otherwise use A6) and paste such
overflow. Then take the next 2170 characters,, leave them in A6 and
paste the overflow in A7 (again, if A7 is empty, paste directly there,
otherwise create a row and paste on the new A7), etc etc etc.


Thank you in advance

This macro I wrote for another purpose may help. It can break the lines at any predetermined number of characters, but it will break the line at a <space (unless there are no spaces at all in the line, in which case it will overflow; but with text responses, and 2170 characters, it's unlikely that will be an issue).

The notes within the macro are important as there is a change to be made to replace the first row, as opposed to putting the first segment into the second row. (The former is good for debugging purposes). There are also some setup requirements as I use early binding.

To enter this Macro (Sub), <alt-F11 opens the Visual Basic Editor.
Ensure your project is highlighted in the Project Explorer window.
Then, from the top menu, select Insert/Module and
paste the code below into the window that opens.

To use this Macro (Sub), first select the cell to be processed (you can do multiple columns; but obviously only one row).

<alt-F8 opens the macro dialog box. Select the macro by name, and <RUN.

============================
Option Explicit
Sub WordWrap()
'requires reference to Microsoft VBScript Regular Expressions 5.5
'Wraps at W characters, but will allow overflow if a word is longer than W
Dim re As RegExp, mc As MatchCollection, M As Match
Dim str As String
Dim W As Long
Dim rSrc As Range, c As Range
Dim mBox As Long
Dim i As Long
'with offset as 1, split data will be below original data
'with offset = 0, split data will replace original data
Const lDestOffset As Long = 1

Set rSrc = Selection
If rSrc.Rows.Count < 1 Then
MsgBox ("You may only select" & vbLf & " Data in One (1) Row")
Exit Sub
End If
Set re = New RegExp
re.Global = True
W = InputBox("Maximum characters in a Line: ", , 79)
If W < 1 Then W = 79
For Each c In rSrc
str = c.Value
'remove all line feeds and nbsp
re.Pattern = "[\xA0\r\n]"
str = re.Replace(str, " ")
re.Pattern = "\S.{0," & W - 1 & "}(?=\s|$)|\S{" & W & ",}"
If re.Test(str) = True Then
Set mc = re.Execute(str)
'see if there is enough room
i = lDestOffset + 1
Do Until i mc.Count + lDestOffset
If Len(c(i, 1)) < 0 Then
mBox = MsgBox("Data in " & c(i, 1).Address & " will be erased if you contine", vbOKCancel)
If mBox = vbCancel Then Exit Sub
End If
i = i + 1
Loop

i = lDestOffset
For Each M In mc
c.Offset(i, 0).Value = M
i = i + 1
Next M
End If
Next c
Set re = Nothing
End Sub
========================