Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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) -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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)? On Apr 27, 9:41*pm, Miguel wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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 ======================== |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
splitting text in cell - row and column operations
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 -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Text to column from end or splitting | Excel Discussion (Misc queries) | |||
Splitting Text from single cell in column across multiple Columns | Excel Worksheet Functions | |||
Splitting column with text & numbers | Excel Discussion (Misc queries) | |||
splitting text within parenthese into new column | Excel Worksheet Functions | |||
Splitting text in one column into two (or more) columns. | Excel Worksheet Functions |