splitting text in cell - row and column operations

[Miguel]

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

 

[GS]

Miguel explained :

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

You could use the Len() function to determine the number of characters,
and the Mid$() function to parse the contents into 2170 (or less)
character strings.

Just curious: Why 2170 characters? Is that the limit for copying in
XL12? (earlier versions are limited to 256)

 

[Miguel]

Thanks for the quick response Garry.
Well, the number of characters is determined by the size of the cell
in the spread sheet i am working with. I am working on XL07 and if the
cell is 546 pixels height by 442 pixels width = about 2170 characters

 

[Miguel]

Also, how would i go about taking the parsed overflow and pasting in
into the cell below (given that is empty or if it has text, insert a
new row right underneath the current one to paste it onto so no text
is loss)?

 

[GS]

Miguel has brought this to us :

Thanks for the quick response Garry.
Well, the number of characters is determined by the size of the cell
in the spread sheet i am working with. I am working on XL07 and if the
cell is 546 pixels height by 442 pixels width = about 2170 characters

Well, that may be true for the selected font and font size, but
changing either one of those throws everything out the window.

 

[GS]

It happens that Miguel formulated :

Also, how would i go about taking the parsed overflow and pasting in
into the cell below (given that is empty or if it has text, insert a
new row right underneath the current one to paste it onto so no text
is loss)?

There's a few ways to go about this...

1. You can iterate every cell in a column and test its contents
length.

2. You can set up a 'service' column that contains a formula that
returns the length, and iterate this for values '>2170'.

I'm getting a sense that you're not all that familiar with using VBA
and so have to ask if you need this done for you by someone who's more
skilled?

 

[GS]

Here's a procedure that does what you want, subject to the position of
spaces near the MaxLength of the text. IOW, it parses at 2170
characters (or whatever to specify) but checks for the position of the
last space in the string and trims it there.

Sub Parse_CellContents1(TestRange As Range, MaxLength As Long)
' Iterates TestRange for any cells with more than MaxLength characters.
' Parses found cells into subsequent cells immediately below;
' If cell below is not empty then a row is inserted.

Dim rng As Range
Dim sText As String, sTemp As String
Dim lLastRow As Long, lCurRow As Long, lOffset As Long, lPos As Long

With TestRange
lLastRow = Cells(.Rows.Count, .Column).End(xlUp).Row
End With
lOffset = 1
Do Until lCurRow = lLastRow
lCurRow = lCurRow + 1: Set rng = Cells(lCurRow, TestRange.Column)
If Len(rng.Value) > MaxLength Then
sText = rng.Text: sTemp = Left$(sText, MaxLength)
lPos = InStrRev(sTemp, " ") '//find the last space
rng.Value = Left$(sText, lPos) '//trim at the space
sText = Mid$(sText, lPos + 1)
Do
sTemp = Left$(sText, MaxLength)
If Len(sTemp) < MaxLength Then lPos = MaxLength _
Else lPos = InStrRev(sTemp, " ")
If Not rng.Offset(lOffset) = Empty Then '//insert a new row
With rng.Offset(lOffset)
.EntireRow.Insert
With .Offset(-1)
.Value = Left(sText, lPos): .WrapText = True
End With
End With
lLastRow = lLastRow + 1 '//add the new row to the row count
Else
With rng.Offset(lOffset)
.Value = Left$(sText, lPos): .WrapText = True
End With
End If
lOffset = lOffset + 1 '//if another row is needed
sText = Mid$(sText, lPos + 1)
Loop Until Len(sText) = 0
End If
lOffset = 1 '//reset for next pass
Loop
End Sub

Sub Test_ParseCellContents()
Parse_CellContents1 Range("A:A"), 2170 '//edit to suit
End Sub